Re: execute immediate with User Defined Types

From: Billy <vslabs_at_onwe.co.za>
Date: 6 Jun 2005 06:17:30 -0700
Message-ID: <1118063850.582643.189560@g14g2000cwa.googlegroups.com>

Bullseye wrote:
>
> Does anyone know a work-a-round to allow User defined types to be
> passed To & From PL/SQL blocks using Execute Immediate?
> (Or Another way to do the same.)
>
> TYPE InfoBlock IS RECORD ( ..... );
>
> Status Number;
> IBlock InfoBlock;
>
> execute immediate 'BEGIN ' || 'UserFunc(:1,:2)' || '; END;'
> using IN OUT Status, IN OUT IBlock;
>

Shooting from the hip, but your problem is likely scope. If you define a record type in a PL/SQL code block, that type is only visible in that block - this is called scope.

When executing code that references that record type from anywhere else, that code will fail as the scope of that code does not include that record type definition.

Thus treat PL/SQL as you would other formal procedural languages - create a type library (aka Pascal unit, C header, whatever) and use it to define (once!) a type or a constant for the PL/SQL program units.

That all said, EXECUTE IMMEDIATE does not seem to like non-SQL record types (from my quick try). Which is fine as SQL object types work even better and allows a lot more flexibility especially if you o-o your PL/SQL code:

SQL> create or replace type TFooRecord is object( n number );   2 /

Type created.

SQL>
SQL> create or replace procedure yet_another_proc( c1 IN OUT TFooRecord ) is
  2 begin
  3 c1.n := c1.n + 1;
  4 end;
  5 /

Procedure created.

SQL>
SQL> set serveroutput on
SQL> declare
  2          f       TFooRecord;
  3
  4          procedure W( cline varchar2 ) is
  5          begin
  6                  DBMS_OUTPUT.put_line( cline );
  7          end;
  8  begin
  9          f := TFooRecord( 100 );
 10
 11          W( 'f is set to ['||f.n||']' );
 12          W( 'doing a dynamic call to yet_another_proc' );
 13
 14          EXECUTE IMMEDIATE 'begin yet_another_proc( :BINDVAR );
end;'
 15                  USING IN OUT f;
 16
 17          W( 'dynamic call done' );
 18          W( 'f is now ['||f.n||']' );

 19 end;
 20 /
f is set to [100]
doing a dynamic call to yet_another_proc dynamic call done
f is now [101]

PL/SQL procedure successfully completed.

SQL>

--
Billy

Received on Mon Jun 06 2005 - 08:17:30 CDT