Re: PLSQL: execute immediate call procedure

On Jun 27, 9:20 am, sybrandb <sybra...@xxxxxxxxx> wrote:
On Jun 27, 5:56 am, "Ana C. Dent" <anaced...@xxxxxxxxxxx> wrote:





"Keith Holmes" <o...@xxxxxxxxxxxxxxxxx> wrote innews:4681394b$0$8729$ed2619ec@xxxxxxxxxxxxxxxxxxxxxxxxxx:

"sybrandb" <sybra...@xxxxxxxxx> wrote in message
news:1182872425.438835.230160@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On Jun 26, 5:22 pm, "Keith Holmes" <o...@xxxxxxxxxxxxxxxxx> wrote:
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
o...@xxxxxxxxxxxxxxxxx

A quick scan through the docs would have learned you
a) CALL is supported in triggers and to call *external* procedures
only
b) apparently you are trying to apply for a contribution to Tom Kyte's
special webcast 'Worst practices in Oracle' as this (cr)app will be
utterly unscalable. Basically you seem to be trying to reengineer PL/
SQL to work like SQL*Plus.
This is not going to work, EVER.

--
Sybrand Bakker
Senior Oracle DBA

Thank you for your helpful comments!

You could usefully have suggested a better approach.

Keith Holmes

BEGIN V_PROCEDURE; END;

is how to invoke the PL/SQL procedure V_PROCEDURE from SQL*Plus

to do the same from within PL/SQL you only need to:

V_PROCEDURE;

from within an existing code block.- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -

You didn't read his post. He wants EVERYTHING to be FULLY dynamic.
Basically he is reinventing sql*plus by using PL/SQL.

--
Sybrand Bakker
Senior Oracle DBA- Hide quoted text -

- Show quoted text -
Sybrand,
I think Keith is on the very beginning of his pl/sql programming path.
He needs guidance, not sarcasm.

Keith,
Before you start any programming attempts, please read:
http://download-east.oracle.com/docs/cd/B19306_01/appdev.102/b14261/toc.htm
You will find many examples as well.
Thomas



.

Relevant Pages

  • Re: Forms 9.0.4.2
    ... Sybrand Bakker, Senior Oracle DBA ... Sounds like too much hard work (mind you, like you say, I am lazy). ... Let's focus on the original problem;-) ...
    (comp.databases.oracle.misc)
  • Re: Duplicate Database with RMAN
    ... Storage manager? ... Sybrand Bakker ... Senior Oracle DBA ... I did read the docs and I perform rman duplicates ...
    (comp.databases.oracle.server)
  • Re: Cloning Oracle DB (non-RAC) Box with Acronis or Ghost
    ... and use RMAN duplicate database. ... Sybrand Bakker ... Senior Oracle DBA ... Not sure what you mean by 'layered products' as that is a typical ...
    (comp.databases.oracle.server)
  • Re: IF - exists -SQL syntax?
    ... declare dummy varchar2 ... Sybrand Bakker ... Senior Oracle DBA ...
    (comp.databases.oracle.misc)
  • Re: Question on Startup
    ... withhelding them from this forum doesn't help resolving the issue. ... Sybrand Bakker, Senior Oracle DBA ...
    (comp.databases.oracle.server)