Re: Dyn Method 4, Pro*C, Stored Procedures, Host Arrays

A copy of this was sent to Ken Tough <ken_at_objectech.co.uk> (if that email address didn't require changing) On Tue, 3 Aug 1999 13:59:36 +0100, you wrote:

>
>All the online doc we have mentions in lots of places:
>
>Warning: In dynamic SQL Method 4, you cannot bind a host array to
> a PL/SQL procedure with a parameter of type "table."
>
>So does that mean it is not possible to pass/return an array
>of data using SQL Method 4? Are there other parameter types
>which can be declared in PL/SQL (other than 'table') which -can-
>be bound to host arrays?
>

it means you cannot execute stored procedures that take PL/SQL table types as parameters unless they are IN parameters and have a default (you cannot bind to them in dynamic sql).

You would have to use OCI to bind table types.

To return large amounts of data from a stored procedure to a pro*c application - have you considered ref cursors?

Another workaround others have used is to pass easily parsable strings back in 32k chunks.

OCI is pretty easy to use to do this -- you can leave your entire app in Pro*C except for this one part (executing a procedure with plsql tables). let me know if you need a small example.

>Is there any difference between Oracle 7.3.x and Oracle 8.0.5 in
>this regard?
>
>I'd prefer to stay with Pro*C for some reason, than move to OCI
>at this point.
>
>Thanks for any help you can offer.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Aug 03 1999 - 09:56:34 CDT