| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Can DBMS_SQL be used to get the RETURN from a PL/SQL FUNCTION?
A copy of this was sent to tmcguiga_at_my-dejanews.com
(if that email address didn't require changing)
On Thu, 29 Oct 1998 16:39:32 GMT, you 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.
>
>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'?
>
Well, one way to do this would be:
declare
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
for x in ( select package_name
from user_arguments
where object_name in ( 'PKBVCI', 'PKSVCI' )
and position = 0
and package_name is not null
group by package_name
having count(*) = 2 )
loop
for y in ( select x.package_name || '.pkbvci' fname from dual union all
select x.package_name || '.pksvci' fname from dual )
loop
dbms_output.put_line( 'Going to execute ' || y.fname );
dbms_sql.parse(exec_cursor,
'begin dbms_output.put_line( ' || y.fname || ');
end;', dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_output.put_line( 'Executed... ' || y.fname );
end loop;
when others then
if dbms_sql.is_open(exec_cursor) then
dbms_sql.close_cursor(exec_cursor);
end if;
raise;
The query gets all packages that contain both a pkbvci and pksvci FUNCTION (and position = 0 gets only functions, position is the position in the argument list and 0 is used for function return values)...
It then loops over the names of the functions you want to execute and used dbms_sql to execute them. I used dbms_output.put_line() in the block I am running to get whatever they return 'printed' out on the screen if you are in sqlplus or svrmgrl (don't forget to enter "set serveroutput on" before running.
As an example:
SQL> set serveroutput on
SQL> create or replace package foo_pkg
2 as
3
3 function pkbvci return number;
4
4 function pksvci return number;
5
5 end;
6 /
Package created.
SQL>
SQL> create or replace package body foo_pkg
2 as
3
3 function pkbvci return number
4 is
5 begin
6 return 12345;
7 end;
8
8
8 function pksvci return number
9 is
10 begin
11 return 67890;
12 end;
13
13 end;
14 /
Package body created.
SQL> @a
SQL> declare
2 exec_cursor integer default dbms_sql.open_cursor; 3 rows_processed number default 0; 4 begin 5 for x in ( select package_name 6 from user_arguments 7 where object_name in ( 'PKBVCI', 'PKSVCI' ) 8 and position = 0 9 and package_name is not null 10 group by package_name 11 having count(*) = 2 ) 12 loop 13 for y in ( select x.package_name || '.pkbvci' fname from dual union all 14 select x.package_name || '.pksvci' fname from dual ) 15 loop 16 dbms_output.put_line( 'Going to execute ' || y.fname ); 17 dbms_sql.parse(exec_cursor, 18 'begin dbms_output.put_line( ' || y.fname || '); 19 end;', dbms_sql.native ); 20 rows_processed := dbms_sql.execute(exec_cursor); 21 dbms_output.put_line( 'Executed... ' || y.fname ); 22 end loop; 23 end loop; 24 dbms_sql.close_cursor( exec_cursor ); 25 exception 26 when others then 27 if dbms_sql.is_open(exec_cursor) then 28 dbms_sql.close_cursor(exec_cursor); 29 end if; 30 raise;
PL/SQL procedure successfully completed.
So that shows that i executed both functions and got to see there output as well....
>I may be overlooking a much better way of doing this. Any advice concerning
>invoking FUNCTIONs determined at runtime would be appreciated.
>
>Thanks,
> Tiernan.
>
>-----------== Posted via Deja News, The Discussion Network ==----------
>http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA
--
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Oct 29 1998 - 12:23:25 CST
 |
 |