Page 1 of 1
Posted: Tue Apr 18, 2006 3:16 pm
by 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 ......
Posted: Wed Apr 19, 2006 1:59 am
by 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.
Posted: Wed Apr 19, 2006 6:05 am
by 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.
Posted: Wed Apr 19, 2006 6:18 am
by chulett
Does this really need to be in a SP? Why not convert it to a 'normal' OCI query?
Posted: Wed Apr 19, 2006 7:05 am
by 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.
Posted: Wed Apr 19, 2006 5:32 pm
by 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)
Posted: Wed Apr 19, 2006 8:19 pm
by chulett
jagadhish wrote: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.
Posted: Thu Apr 20, 2006 11:30 am
by 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.
Posted: Thu Apr 20, 2006 2:18 pm
by 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?
Posted: Thu Apr 20, 2006 4:47 pm
by ray.wurlod
Why not post a request on the Enhancements Wish List forum? Here or
DeveloperNet or both.
Posted: Fri Apr 21, 2006 9:51 pm
by 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,
Posted: Sun Apr 23, 2006 4:39 pm
by 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.
Posted: Sun Apr 23, 2006 10:01 pm
by 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.
Posted: Sun Apr 23, 2006 10:02 pm
by DSguru2B
Or you can even call it from the OCI stage from an after SQL.
Posted: Mon Apr 24, 2006 1:45 am
by 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.