Home » SQL & PL/SQL » SQL & PL/SQL » Dynamic sql cursor into pipe row (oracle 9i,windowsXP)
Dynamic sql cursor into pipe row [message #390873] Tue, 10 March 2009 02:35 Go to next message
yashora
Messages: 39
Registered: August 2006
Member
Hi,

I have one typical question with plsql objects pipe lined function using dynamic sql.


CREATE TYPE emp_row_type AS OBJECT (
  empno              VARCHAR2(30),
  ename              VARCHAR2(18),
  deptno             NUMBER  
);
/

CREATE TYPE emp_table_type AS TABLE OF emp_row_type;
/

CREATE OR REPLACE FUNCTION get_all_objects
(
f_deptno number
)
RETURN object_table_type PIPELINED AS
type cur is ref cusor;
empcur cur;
str varchar2(4000);
BEGIN
open empcur for ' select empno,ename from emp where deptno'||f_deptno;

--i want to pass the above cursor into the object....

PIPE ROW(emp_row_type(empcur.empno,empcur.ename,emp.deptno));
return;
end;



How to pass the dynamic sql cursor values into pipe row function?

Regards
Yashora
Re: Dynamic sql cursor into pipe row [message #390875 is a reply to message #390873] Tue, 10 March 2009 02:42 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pipelined functions are strongly typed, so must be your cursor and so you can't use any sql.

Regards
Michel
Previous Topic: ora 00907
Next Topic: how to count comma seprated values
Goto Forum:
  


Current Time: Fri Dec 09 09:53:09 CST 2016

Total time taken to generate the page: 0.24348 seconds