Re: [Q] Generic programming

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/10/01
Message-ID: <34319d9e.30191172@newshost>#1/1

On Tue, 30 Sep 1997 21:09:22 GMT, martinj(replace this with @)xs4all.nl (Martin Jesterhoudt) wrote:

>Hello,
>
>PROBLEM:
>I want to call a procedure by using a variable as procedurename, but
>don't know if this is possible. Can someone give me a hint? Thanks in
>advance...
>
>EXAMPLES:
>(which doesn't work):
>
>declare
> cursor c_procs is
> select procedure_name
> from project_procedures;
>begin
> for r_procs in c_procs loop
> EXEC r_procs.procedure_name -- <<<<< THIS LINE NEEDS MODIFICATION
> end loop;
>end;
>
>For obvious reasons, I don't want to use something like this:
>
>declare
> ...
>begin
>
> for r_procs in c_procs loop
>
> if r_procs.procedure_name = 'STARTUP' then
> STARTUP;
> elsif r_procs.procedure_name = 'NEXTPROC' then
> NEXTPROC;
> else
> EXITPROC;
> end if;
>
> end loop;
>
>end;
>-------------------------------------------------------------------------------
>--- Martin Jesterhoudt
>--- martinj(replace this by @)worldonline.nl
>-------------------------------------------------------------------------------

If you install the following function/procedure, you'll be able to:

declare
  cursor c_procs is

  select	procedure_name
  from	project_procedures;

begin
  for r_procs in c_procs loop
    execute_immediate_proc( 'begin ' || r_procs.procedure_name || ' end;' );   end loop;
end;

Remember, roles are never enabled in stored procedures, so, make sure the procedures you try to run are either

• owned by you, the owner of the procedure
• you, the owner of the above block, have been granted execute directly on the procedure (and not via a role)

create or replace                                                               
function execute_immediate( stmt  in varchar2 )                                 
return number                                                                   
as                                                                              
    exec_cursor     integer default dbms_sql.open_cursor;                       
    rows_processed  number  default 0;                                          
begin                                                                           
    dbms_sql.parse(exec_cursor, stmt, dbms_sql.native );                        
    rows_processed := dbms_sql.execute(exec_cursor);                            
    dbms_sql.close_cursor( exec_cursor );                                       
    return rows_processed;                                                      
exception                                                                       
    when others then                                                            
      if dbms_sql.is_open(exec_cursor) then                                     
        dbms_sql.close_cursor(exec_cursor);                                     
      end if;                                                                   
      raise;                                                                    
end;                                                                            
/                                                                               

create or replace
procedure execute_immediate_proc( stmt in varchar2 ) is
  l_dummy number;
begin
  l_dummy := execute_immediate( stmt );
end;
/

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Oct 01 1997 - 00:00:00 CDT