calling oracle SP which returns ref.cursor using STP

[lstsaur]

Hi,
STP stage does not support type of ref cusor. This feature is on my wish list since last year. And I am still waiting....waiting....and ......

[rleishman]

Look in the Oracle PL/SQL doco for pipelined functions. You could create an interface that essentially converts the REF CURSOR into a View, then select from the VIEW in a normal OCI stage.

[jagadhish]

Hi,

Thanks for your information. If STP doesn't support Ref. cursor, is it possible to call that procedure using routine and get the result set?
Any help is greatly appreciated....


Thanks and regards,
Jags.

[chulett]

Does this really need to be in a SP? Why not convert it to a 'normal' OCI query?

[jagadhish]

Hi,

The SP is already existing one, we cant change it. Can I use shell script to call that procedure process the result set...

please give your inputs.

[rleishman]

OK. So you have a SP like this:

Code: Select all

CREATE PROCEDURE my_proc(p1 IN NUMBER, rc OUT sys_refcursor) ...

Create a type that contains the same columns as the cursor, and a pipelined function to fetch the cursor.

Code: Select all

CREATE TYPE my_typ AS OBJECT (
  col1 VARCHAR2(20)
, col2 NUMBER
);

CREATE TYPE my_typ_ary AS TABLE OF my_typ;

Code: Select all

CREATE FUNCTION my_func (p1 IN NUMBER) 
RETURN my_typ_ary PIPELINED AS
  rc SYS_REFCURSOR;
  t my_typ;
BEGIN
  my_proc(p1, rc);
  t := my_typ();

  LOOP
    FETCH rc INTO t.col1, t.col2;
    EXIT WHEN rc%NOTFOUND;

    PIPE ROW(t);
  END LOOP;
END;

Now use a SQL to extract the data in a normal OCI stage:

Code: Select all

SELECT *
FROM CAST(TABLE(my_func(1234)) AS my_typ_ary)

[chulett]

The SP is already existing one, we cant change it.

Just for the record, I didn't suggest you change it - I suggested you pitch it.

[ratikmishra1]

I use ODBC stage to read ref cursors returned from oracle stored procedures.

You should have only one output parameter for the ref cursor.
Define the columns that you expect to get from the cursor.

and you are good to go.

[lstsaur]

Hi,
That's why I said in my previous post that handling type of ref cursor feature should be built within STP stage. I hope that Ascential is reading this thread. Of course, there are some other ways to get Oralce's type of ref cursor to work, but just NOT from thier proud STP stage?

[ray.wurlod]

Why not post a request on the Enhancements Wish List forum? Here or DeveloperNet or both.

[jagadhish]

Hi rleishman

Im trying out pipelined function. but while creating the function,

CREATE FUNCTION my_func (p1 IN NUMBER)
RETURN my_typ_ary PIPELINED AS
rc SYS_REFCURSOR;
t my_typ;
BEGIN
my_proc(p1, rc);
t := my_typ();

LOOP
FETCH rc INTO t.col1, t.col2;
EXIT WHEN rc%NOTFOUND;

PIPE ROW(t);
END LOOP;
END;



for the statement t := my_typ(); I'm getting following error message,

PL/SQL: Statement ignored
PLS-00306: wrong number or types of arguments in call to
'my_typ'


If I remove that statement t := my_typ(); funtion executes without any error.. could u please tell me what is couse for this ....

thanks,

[rleishman]

If it works, go for it. I thought it would need a constructor. I still haven't unravelled all of the wonders of Oracle's object-relation features.

[DSguru2B]

Why dont you have the SP created in the Oracle instead of creating it at runtime. And then you can call the SP just simply using the STP stage.
In that stage you dont need to paste in the whole SP instead you just call.
Let the stage know the input and output params.

[DSguru2B]

Or you can even call it from the OCI stage from an after SQL.

[rleishman]

I wasn't advocating putting any DDL in the DataStage job to be created at runtime. The function and type DDL statements are executed once only during setup. The only runtime SQL is the SELECT .. FROM TABLE(func(..)).

I think the OP gets this and is on the right track.