PLSQL: execute immediate call procedure

From: Keith Holmes <orach_at_keithholmes.me.uk>
Date: Tue, 26 Jun 2007 16:22:10 +0100
Message-ID: <46812f2b$0$8744$ed2619ec@ptn-nntp-reader02.plus.net>

I'm trying to call a procedure from within a procedure. All works fine using syntax of the type:

   execute immediate 'begin '||v_procname||'(:1); end;' using v_text;

but would it be more efficient to use syntax of the form:

   execute immediate call ......; (except I can't get the syntax correct for doing the same thing.

Can someone advise me please?

The code I am using is:

create or replace procedure get_text_p1
(
 v_select in varchar2, /* SQL select string */  v_procname in varchar2 /* procedure to be carried out - NOTE this must be in UPPER

                                case if CALL used*/

)
is

--
/*******************************************************************************************
Purpose: To retrieve a string on the basis of a SELECT statement (v_select) 
and
         carry out process (v_procname)

Uses Objects:

Input example: begin get_text_p1('select bore_name from sobi', 
'DO_SOMETHING_P1'); end;



*******************************Steps********************************************************

** Step 1.

** Step 2.

** Step 3.

** Step 4.

** Step 5.

*

*  Amendment History

*

*  Version No.    Changed/        Date          Change

*                 Reviewed by

*  notEntered     Keith Holmes    26 Jun 2006   First Version

********************************************************************************************/

--
v_return_cur sys_refcursor;  /* ref cursor     */
v_text clob;                 /* text retrieved */
--
begin
  --
  dbms_output.enable (100000);
  --
  open v_return_cur for v_select;
    loop
   fetch v_return_cur into v_text;
   exit when v_return_cur%notfound;
   --
   execute immediate 'begin '||v_procname||'(:1); end;' using v_text;
   dbms_output.put_line (v_text);
   --
    end loop;
  close v_return_cur;
exception
  when others then
    dbms_output.put_line ('get_text_p1 '||sqlerrm);
end get_text_p1;
/

create or replace procedure do_something_p1
(
 v_text in varchar2
)
is
--
begin
  insert into proc_control_log (error) values (v_text);
exception
  when others then
    dbms_output.put_line ('do_something_p1 '||sqlerrm);
end do_something_p1;

Many thanks

Keith Holmes
orach_at_keithholmes.me.uk

Received on Tue Jun 26 2007 - 10:22:10 CDT