LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jodycrutchfield

How can I execute a package in oracle with input data and display multiple rows of data that return in a ref cursor?

I can use this sql to submit data and get data back from sqlplus.

declare
type cur is ref cursor;
c cur;
type_name varchar2(200);
name varchar2(200);
oid number(20);
child_oid number(20);
child_name varchar2(400);
begin

nc_dev_rdb.PKG_TW_DATA_INTEGRITY_PRJ.GET_CHILD('91 25138930913248107', c);

loop
fetch c into name, oid, child_oid, child_name, type_name;
exit when c%notfound;
dbms_output.put_line('Name: '||name||'; Object_ID: '||oid||'; Child: '||child_oid||'; Child Name: '||child_name||'; Type: '||type_name);
end loop;
close c;
end;

Thank you,

Jody

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: timonk

Jody,

Unfortunately, LAE does not have the ability to return cursors.

You CAN execute sql procedures and packages using the DB Execute nodes and the sqlNonSelect brainscript command, however.

Regards
Timon Koufopoulos
MDA Support.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

Hi Jody,

I think you will have to put the data into a table temporarily so that you can then execute the sqlSelect() function on that table. You can put multiple statements into a DB Execute node and they each execute in their own transaction.

You should be able to adapt this sample code to what you need.

First create a table, this step could also be done in a DB Execute node, but I'm assuming it already exists and is empty. Here is the code to create the empty table:
CREATE TABLE tmp AS SELECT * FROM dual WHERE 1=0;

Then create a DB Execute node with one output. Populate the database connection parameters and this code works as the script:

sqlscript = "
begin
FOR loop_counter IN 0..9
LOOP
INSERT INTO tmp VALUES (To_Char(loop_counter));
END LOOP;
END;
"

sqlretrieve = "
select * from tmp
"

sqlcleanup = "
delete from tmp
"

sqlNonSelect(sqlscript)
sqlSelect(1,sqlretrieve)
sqlNonSelect(sqlcleanup)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: jodycrutchfield

Thanks Guys,

I will try the temp table approach.

Jody