How to convert reference cursor to oracle pipelined table data results


Problem: Converting from reference cursor to oracle pipelined results

Solution: Package body declaration is shown here, it is assumed that the specification is already taken care to declare the pipelined record datastructure, if not please look into my other posts on how to declare the specification part of it at

https://javatoj2ee.wordpress.com/2012/08/30/oracle-pipelined-table-functions/

function get_customer_data

(firstName IN VARCHAR2 DEFAULT NULL, lastName in VARCHAR2 DEFAULT NULL)

return customerpipelineddata PIPELINED AS

output_record                 customerpipelineddata;

v_customerdetails           ref_cursor_type;

–select statement goes here

open v_customerdetails for (select statement query)

–Below is the part needed to have reference cursor data into pipelined data

loop
fetch v_customerdetails_cursor bulk collect into output_record limit batchsize;
for i in 1 .. output_record.count() loop
pipe row (output_record(i));
end loop;
exit when output_record.count < batchsize;
end loop;

end get_customer_data;

here customerpipelineddata  is the actual pipelined data record, a set of records of a particular data structure like

TYPE customerpipelinedrecord  IS RECORD (id varchar2(100),firstname CUSTOMER.FIRST_NAME%type);

TYPE customerpipelineddata  IS TABLE OF customerpipelinedrecord;

Hope this helps….Please do not forget to like or post your comments 🙂

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: