calling oracle SP which returns ref.cursor using STP
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
OK. So you have a SP like this:
Create a type that contains the same columns as the cursor, and a pipelined function to fetch the cursor.
Now use a SQL to extract the data in a normal OCI stage:
Code: Select all
CREATE PROCEDURE my_proc(p1 IN NUMBER, rc OUT sys_refcursor) ...
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;
Code: Select all
SELECT *
FROM CAST(TABLE(my_func(1234)) AS my_typ_ary)
Ross Leishman
-
- Charter Member
- Posts: 42
- Joined: Wed Aug 18, 2004 2:49 pm
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Why not post a request on the Enhancements Wish List forum? Here or DeveloperNet or both.
Last edited by ray.wurlod on Sat Apr 22, 2006 1:58 am, edited 1 time in total.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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,
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,
REGARDS,
JAGS
JAGS
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.
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.