Home » SQL & PL/SQL » SQL & PL/SQL » how to send data into ref cursor (oracle 11g)
how to send data into ref cursor [message #389099] Fri, 27 February 2009 04:59 Go to next message
rajesh4851
Messages: 89
Registered: January 2007
Member
Hi,
I have a problem with my procedure in .NET.

I created one procedure with OUT parameter table type. This is used in front end(c#.net). because of some problems in .NET with table type record, they want out put parameter as refcursor.
Now i want to send my output data (stored plsql table) via ref cursor to the front end.

example:

create or replace package pck_emp
is
type dept_table is table of varchar2(200) index by binary integer;
TYPE ref_cursor IS REF CURSOR;

procedure test_emp(i_emp_id NUMBER, o_emp_dept dept_table);
end pck_emp;


Here my o/p parameter is o_emp_dept which is of table type. But the front end will not support this, so i have to pass refcursor instead of table type.

So i have changed the procedure like this:

procedure test_emp(i_emp_id NUMBER, o_emp_dept ref_cursor );
Inside of this procedure i have declared one table type variable and data is stored in to this table type variable.

now i want to fetch the data from this table type record to this ref_cursor variable. (i.e o_emp_dept)
how is it possible?
is there any other way to do this ? please give me some idea.

Regards,
Raj
Re: how to send data into ref cursor [message #389119 is a reply to message #389099] Fri, 27 February 2009 05:51 Go to previous messageGo to next message
psrinuind
Messages: 4
Registered: September 2008
Junior Member
Create one dummy table for your application "REPORTINTERIMDATA"


Eample code:

procedure getEventRecordInfo(eventRecsCur IN OUT tempRecordsCurType)
IS
BEGIN

insertToReportInterimData(); -- Apply your logic

OPEN eventRecsCur FOR
SELECT COL1
COL2
COL3
COL4
COL5
FROM REPORTINTERIMDATA;
DELETE FROM REPORTINTERIMDATA;
END getEventRecordInfo;
Re: how to send data into ref cursor [message #389128 is a reply to message #389099] Fri, 27 February 2009 06:10 Go to previous messageGo to next message
cookiemonster
Messages: 12404
Registered: September 2008
Location: Rainy Manchester
Senior Member
Deleting the data before it's been fetched isn't a good idea.

The best solution would be to not use a PL/SQL table at all. Just write a SELECT statement that gets the same data as would be put into the PL/SQL table and base the ref cursor on that select statement.

Failing that have a look at the table and cast functions (though I think you need to transform your PL/SQL table into a varray for that to work)
Re: how to send data into ref cursor [message #390464 is a reply to message #389128] Fri, 06 March 2009 07:58 Go to previous message
rajesh4851
Messages: 89
Registered: January 2007
Member
Thanks for your reply.
I found some solution in the below link:

http://www.oracle-base.com/articles/8i/ComplexRecordsets.php

In this link they have used 2 ways. one is using global temporary tables and another one is plsql tables.

In the second way (plsql table) they have used OBJECTs and filling that data into that object variable and finally they are opening the refcursor from that object by CASTING the object.
like this:
  -- Open REF CURSOR for outout.
  OPEN p_recordset FOR
    SELECT empno,
           ename,
           sal,
           complex
    FROM   Table(Cast(v_tab As My_Tab_Type))
    ORDER BY ename;


Can i use RECORD type instead of OBJECT? Is it possible?

Previous Topic: Updating Data
Next Topic: trigger with rollback
Goto Forum:
  


Current Time: Mon Dec 05 21:29:25 CST 2016

Total time taken to generate the page: 0.20974 seconds