asked on

Help Calling Oracle Stored Proc

I am a Sql Server guru..but please help on the Oracle side..

Using Golden version 6 to call a stored procedure....and just cannot get the syntax down to call it.
Here is the stored proc definition

I need to know how to call this from within golden..

and then im wiring it into a .net datasource control...I will make that part a separate post if I still have issues.

Procedure GetOrgNames (tOrgCursor In Out OrgRefCursor)
is
begin
	open tOrgCursor for
	Select 0 ID,'<Select Organization>' Text from Dual
	union
	select OrgID ID, OrgName Text from organization order by Text;
end GetOrgNames;

Open in new window

sumit2906

What is golden 6, are you referring to golden gate? If you want to use the above proc, you can use like this:

CREATE OR REPLACE PROCEDURE IS

TYPE RecType IS RECORD (ID INT,
OrgName varchar2(30));
TYPE array_t IS TABLE OF RecType
INDEX BY BINARY_INTEGER;
rec_array array_t;
tOrgCursor OrgCursor;
BEGIN
GetOrgNames (tOrgCursor);
FETCH tOrgCursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i).ID);
dbms_output.put_line(rec_array(i).OrgName);

END LOOP;
END pass_ref_cur;
/

Robb Hill

ASKER

im confused in your answer.......that is the proc I put in code above.....
I just need to know how you would call that ....
it looks like you wrote me a new proc..hehe....

Sorry if I am confused here...I am very MS SQL ....but in MS sql if I made a proc...

there would be some command...like exec...blah blah()

how would you do that with the proc I put in code before...I cant seem to call in Golden

The program is Golden 6 by Benthis Software.....sub called "The Golden Retriever"

SOLUTION