Oracle PL/SQL, Cursors and Packages 
Author Message
 Oracle PL/SQL, Cursors and Packages

Hi,

Does anyone have an example on how to create a PL/SQL procedure that
returns more than 1 row as a output parameter?

I basically want to :-

SELECT * INTO outrec FROM TABLEA;

where outrec is an OUT parameter type.

Anyhelp would be greatly appreciated.

Also, can anyonetell me if PL/SQL packages are accessible through VB4?

many thanks,

Gary.



Fri, 29 Oct 1999 03:00:00 GMT  
 Oracle PL/SQL, Cursors and Packages

Hi,

Last time I looked into it, Oracle is not capable of returning result sets
from stored procedures.  You could use parameter arrays if you like, but
you would need a separate array for each field in your result set, and you
would have to load the array manually in PL/SQL code.  IMHO, you are better
off using a traditional select statement to fetch your results.

As far as calling PL/SQL packages from VB4, the answer is yes.  I have done
it with a tool called Oracle Objects for OLE.  Basically you end up
building an anonymous PL/SQL block like

BEGIN

:SomeResultParameter := SomePackage.SomeFunction(SomeArg);

END;

storing this in a string and passing it to the server directly.  You can
obtain a trial version of this tool from Oracle's web site to see if it
does what you want.  As far as other tools, the ODBC driver that I had did
not support packages, only plain vanilla stored procedures.  The documented
workaround (??) was to create a stored procedure stub that delegates the
call to the correct package.  For me, this was a ridiculous suggestion so I
stuck with Oracle's tool.

Hope this helps!

Chris



Quote:
> Hi,

> Does anyone have an example on how to create a PL/SQL procedure that
> returns more than 1 row as a output parameter?

> I basically want to :-

> SELECT * INTO outrec FROM TABLEA;

> where outrec is an OUT parameter type.

> Anyhelp would be greatly appreciated.

> Also, can anyonetell me if PL/SQL packages are accessible through VB4?

> many thanks,

> Gary.



Tue, 02 Nov 1999 03:00:00 GMT  
 Oracle PL/SQL, Cursors and Packages


Quote:

>Does anyone have an example on how to create a PL/SQL procedure that
>returns more than 1 row as a output parameter?
>ie, SELECT * INTO outrec FROM TABLEA;
>where outrec is an OUT parameter type.

As far as I know, there is no support for doing this in Oracle SQL.  I do
know that using the OCI Layer, it is possible to execute a stored procedure
and bind an array to an out variable -- which essentially gives you the
functionality you want.  However, whether you would be able to find a
DLL/OCX
that would allow this is another story.

Quote:
>Also, can anyonetell me if PL/SQL packages are accessible through VB4?

Just as stored procedures and stored functions are accessible through an
anonymous PL/SQL block or SELECT statement, so are stored procedures and
functions in packages.  For example:

myOracleDB.Execute("BEGIN MyPackage.DoSomething(Parameter); END;")



Thu, 11 Nov 1999 03:00:00 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Calling stored PL/SQL package in Oracle DB using ODBC

2. VB 5 Help Calling PL/SQL packages

3. Oracle PL/SQL Debugger here!

4. RDO 2.0 and calling Oracle PL/SQL Stored Procedures

5. VB 4 , PL/SQL and oracle

6. Visual basic and oracle PL/SQL

7. Arrays --- Retrieving from Oracle w/PL/SQL

8. US-CA-Orange County-Visual Basic, PL/SQL, Oracle-recruiter

9. Crystal 6.0 and Oracle PL/SQL

10. ADO and Oracle PL/SQL stored procedures

11. Call Stored Procedure using PL/SQL,VB4 and ORACLE 7.3

 

 
Powered by phpBB® Forum Software