| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Need PL/SQL help. Passing PL/SQL tables as parameters.
Well, it sounds like the culprit is pkg.getdata, not the test procedure. It sounds like an infinite look somewhere in the pkg...
Anyway, Oracle7.2 and 7.3 can return cursors to a 3gl application (or 4gls that are setup to handle it). For example:
REM the following creates a function that can return a cursor REM we print it out in sql*plus to show it working there...
create or replace package types
as
type cursorType is ref cursor;
end;
/
l
show errors
create or replace function getAddresses return types.cursorType as
l_cursor types.cursorType;
begin
open l_cursor for select ename, job, hiredate from emp;
return l_cursor;
end;
/
variable n REFCURSOR
exec :n := getAddresses
print n
/* Pro*c to fetch from the above code */
static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
typedef char asciiz;
EXEC SQL TYPE asciiz IS STRING(40);
SQL_CURSOR my_cursor;
asciiz ename[40];
asciiz job[40];
asciiz hiredate[40];
EXEC SQL ALLOCATE :my_cursor;
EXEC SQL EXECUTE BEGIN
:my_cursor := getAddresses;
END; END-EXEC;
for( ;; )
{
EXEC SQL WHENEVER NOTFOUND DO BREAK;
EXEC SQL FETCH :my_cursor INTO :ename, :job, :hiredate;
printf( "%s, %s, %s\n", ename, job, hiredate );
}
On 15 Feb 1997 21:36:36 GMT, spchin_at_aol.com (SPCHIN) wrote:
>I am using Oracle 7.3 and have written a procedure that is part of a >package and returns PL/SQL tables to the calling environment. For some >reason, upon calling the procedure, my program just hangs for hours on end >unless I control-break out of it. Even the dbms_output.put statements >within my program do not print out. > >I am at a loss as to what is wrong. I have written packages before but >have never had to use PL/SQL tables as OUT parameters. Can anyone provide >me with any info on this? Is it the passing of tables as parameters >that's causing the problem? Below is a sample of a short proc I wrote to >call my package which returns the PL/SQL tables. > >create or replace procedure testpkg >is > >Done integer; >Ret integer; >A PKG.PLSQL_Table1; >B PKG.PLSQL_Table2; >C PKG.PLSQL_Table3; > >begin >PKG.GetData(10,Done,Ret,A,B,C); >end; >/ > >In case anyone is wondering as to what I'm trying to accomplish, my >company is converting from Sybase to Oracle. A Sybase stored procedure >can return rows whereas an Oracle procedure can't. Thus, I am trying to >mimic the process with the use of PL/SQL tables. Any help or advice will >be greatly appreciated. Thanks.
Thomas Kyte
Oracle Government
tkyte_at_us.oracle.com
 |
 |