Generic programming

Generic programming

Post by replace this with » Wed, 01 Oct 1997 04:00:00



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                      

-------------------------------------------------------------------------------

 
 
 

Generic programming

Post by Peter Schneid » Thu, 02 Oct 1997 04:00:00


On Tue, 30 Sep 1997 21:09:22 GMT, martinj(replace this with


>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):

[SNIPPED]

>-------------------------------------------------------------------------------
>--- Martin Jesterhoudt                      

>-------------------------------------------------------------------------------

Hi Martin,

have a look at Oracle standard package DBMS_SQL. You can construct an
anonymous PL/SQL block as a VARCHAR2 variable, use bind variables in
this block, have DBMS_SQL execute it and then retrieve the values of
your bind variables.

If you need any further help, feel free to send me an e-mail.

Hope this helps,
Peter

Peter Schneider


 
 
 

Generic programming

Post by Thomas Ky » Thu, 02 Oct 1997 04:00:00




>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                      

>-------------------------------------------------------------------------------

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

Oracle Government
Bethesda MD

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

----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

 
 
 

Generic programming

Post by Tina Tra » Sat, 04 Oct 1997 04:00:00


Use dynamic SQL provided by the dbms_sql package.



Quote:> 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                      


----------------------------------------------------------------------------
---

- Show quoted text -

 
 
 

1. Programming "Generic" DTS using VB6

2. CommandTypeEnum

3. Program access to generic databases

4. Help !!! With Text Box

5. telephone generic pick program

6. Trouble compiling postgresql in hp-unix

7. Program access to generic databases

8. HELP how to access table between 2 sql6.5 server

9. Need generic advice on debugging stored procedure

10. SP Question - Insert into table using generic insert SP

11. Free generic SQL quering tool

12. Generic network error with a BULK INSERT statement

13. Not SQL:Generic App/srvc API