Re: How to call a ORACLE stored procedure which parameters are PL/SQL table and %ROWTYPE

A copy of this was sent to ellen111_at_my-deja.com (if that email address didn't require changing) On Fri, 28 May 1999 07:01:12 GMT, you wrote:

>Hi,
>
>I compiled a stored procedure in ORACLE DATABASE. which has two
>OUT parameter, one is PL/SQL table, and another is table%ROWTYPE.
>when I call it by PL/SQL block, the %ROWTYPE is OK, but the error
>message said the PL/SQL table is wrong match data type( I declared
>a same PL/SQL table in the PL/SQL block). and also, I want to

Lets see your example. How did you declare the TABLE type in the stored procedure and the anonymous block. The correct way is:

create or replace package TYPES
as

    type myArray is table of <type> index by binary_integer; end;
/

create or replace procedure my_procedure(x OUT types.myArray, y OUT emp%rowtype) as
begin
 ...
end;
/

declare

    a TYPES.MYARRAY; -- < IMPORTANT: use the SAME type, not a type that looks

• < the same to you and me, THE SAME TYPE b emp%rowtype; begin my_procedure( a, b ); end; /

You are probably using a TABLE TYPE that looks the same to you and me -- but is NOT the same exact type. Above, we put the type into a package spec so the procedure AND the anonymous block can both 'see' it and use it.

>call this procedure in a proc* program, but when I compile the .pc
>program, the error is : the procedure name must be declared.
>do you know how to declare a procedure in a proc file? I execute it
>using embed PL/SQL block in the proc file.
>

You cannot bind a 3gl STRUCT to a PL/SQL record. So, you will not be able to call that procedure with the table%rowtype parameter from pro*c.

In order to call any procedures/functions from pro*c you must:

• use SQLCHECK=semantics on the precompile line
• provide a USER=user/password on the precompile line
• code: exec sql execute begin my_procedure( :x, :y, :z ); end; end-exec;

in the .pc file.

>Thank you!
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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 Fri May 28 1999 - 07:06:01 CDT