Re: Can DBMS_SQL be used to get the RETURN from a PL/SQL FUNCTION?

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Thu, 29 Oct 1998 18:46:24 GMT
Message-ID: <3639afb7.18380279@dcsun4.us.oracle.com>

On Thu, 29 Oct 1998 16:39:32 GMT, tmcguiga_at_my-dejanews.com wrote:

>I am considering writing PACKAGE in PL/SQL to aid in version control.
>
>Each PACKAGE in the current schema is examined to see if it has a FUNCTION
>called PKBVCI and a FUNCTION called PKSVCI. This is done via a CURSOR --
>
>CURSOR VCIPackages IS
> SELECT package_name
> FROM user_arguments
> WHERE object_name = 'PKBVCI'
> INTERSECT
> SELECT package_name
> FROM user_arguments
> WHERE object_name = 'PKSVCI';
>

>ASIDE: Should I be tightening up the WHERE clauses to ensure PKBVCI and PKSVCI
>are actually FUNCTIONs and not, for example, PROCEDUREs. If so, how? Note that
>the specification of both FUNCTIONs state that they take no arguments and
>RETURN VARCHAR2.
In the user_arguments table, the definition for a function will have at least one row and the position will equal 0 (zero).

Given the package

  1 create or replace
  2 package myPackage as
  3 procedure proc_no_parm;
  4 procedure proc_one_parm( parm number );   5 function func_no_parm return number;   6 function func_one_parm( parm number ) return number;   7* end;
SQL> / SQL> l
  1 select PACKAGE_NAME, OBJECT_NAME, ARGUMENT_NAME, POSITION   2 from user_arguments
  3 where PACKAGE_NAME = 'MYPACKAGE'
  4* order by 2   

PACKAGE_NAME OBJECT_NAME ARGUMENT_NAME POSITION ------------ --------------- ------------- --------

MYPACKAGE    FUNC_NO_PARM                         0
MYPACKAGE    FUNC_ONE_PARM                        0
MYPACKAGE    FUNC_ONE_PARM   PARM                 1
MYPACKAGE    PROC_NO_PARM                         1
MYPACKAGE    PROC_ONE_PARM   PARM                 1

You see that functions always have a position 0 which is their return type.

>
>Next, having realised the PACKAGEs which support version control, both the
>PKBVCI and PKSVCI FUNCTIONs will need to be invoked. But can I do this?
>
>Since the PACKAGE names were established at runtime I think I need dynamic SQL
>but have no idea how to write the SQL statement.
>
>Do I try 'SELECT ' || <PackageName> ||'.PKBVCI FROM DUAL' or some other
>'trick'?

You need to use dynamic sql and a global package variable.

eg.

package global_stuff is
  return_code varchar2(32767);
end global_stuff;

procedure example is
  l_cur number;
begin
  l_cur := dbms_sql.open_cursor;
  for c1 in (

      SELECT package_name
        FROM user_arguments
       WHERE object_name = 'PKBVCI'
   INTERSECT
      SELECT package_name
        FROM user_arguments
       WHERE object_name = 'PKSVCI' )

  loop
    dbms_sql.parse( l_cur,

                    'begin global_stuff.return_code := ' ||
                       c1.package_name || '.PKBVCI; end;',
                    dbms_sql.native );

    l_status := dbms_sql.execute( l_cur );

    --
    --  Now the variable global_stuff.return_code = the return of the 
    --  function PKBVCI
    --
    dbms_sql.parse( l_cur,
                    'begin global_stuff.return_code := ' ||
                       c1.package_name || '.PKSVCI; end;',
                    dbms_sql.native );

    l_status := dbms_sql.execute( l_cur );

    --
    --  Now the variable global_stuff.return_code = the return of the 
    --  function PKSVCI
    --

  end loop;
  dbms_sql.close_cursor( l_cur );
end example;

>
>I may be overlooking a much better way of doing this. Any advice concerning
>invoking FUNCTIONs determined at runtime would be appreciated.
>
>Thanks,
> Tiernan.

Hope this helps.

chris.

>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Received on Thu Oct 29 1998 - 12:46:24 CST