Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

PL/SQL procedure dynamic execution

SOLVED
Go to solution
Nicolas Dumeige
Esteemed Contributor

PL/SQL procedure dynamic execution

Hello,

That's not stricly necessary in my case, but I was wondering how is it possible to execute a PL/SQL procedure given that this call will be dynamically built.

See the example below and the last 3 comments ...

Thank for you idea,

Nicolas

CREATE OR REPLACE FUNCTION rcp_sld_appli (app IN VARCHAR2 DEFAULT 'VIDE')
RETURN number
IS
ladate DATE;
appli VARCHAR2(3);
mois VARCHAR2(2);
proc VARCHAR2(25);
instruction VARCHAR2(100);
BEGIN
appli := upper(app);
DBMS_OUTPUT.ENABLE(100000);
/* Chosse wich procedure to call */
IF ( appli = 'CDE' ) THEN
proc := 'KAGRRST.P_NEW_MONTH_CDE';
ELSIF ( appli = 'GL' ) THEN
proc := 'KAGRRST.P_NEW_MONTH_GL';
END IF;

/* Choose the time period */
SELECT to_char(sysdate+15,'MM') INTO mois FROM DUAL;

instruction := proc||'('''||to_char(mois)||''')';

-- DBMS_SQL.EXECUTE instruction;
-- EXECUTE IMMEDIATE 'call instruction';
-- PLVdyn.PLSQL(instruction);
END;
/
All different, all Unix
6 REPLIES
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL procedure dynamic execution

Nevermind, I found it, quite simple :

EXECUTE IMMEDIATE 'BEGIN '||instruction||'; END; ' ;
All different, all Unix
Yogeeraj_1
Honored Contributor

Re: PL/SQL procedure dynamic execution

hi nicolas,

just a remark.

dbms_sql can
a) parse once
b) execute over and over and over

execute immediate cannot do that. it parse/executes each time.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: PL/SQL procedure dynamic execution

hi again,

in general, execute immediate is good for ONE TIME execution of a dynamic sql statement.

dbms_sql is good for REPEATED executions.

regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL procedure dynamic execution

Yogeeraj,

Thanks for you comments.
The code is executed just once.

Furthermore, can you call a PL/SQL procedure using DBMS_SQL.EXECUTE ?
Can you provide an example ?

Cheers

Nicolas
All different, all Unix
Yogeeraj_1
Honored Contributor
Solution

Re: PL/SQL procedure dynamic execution

hi nicolas

attached a generic example.

hope this helps!

Regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Nicolas Dumeige
Esteemed Contributor

Re: PL/SQL procedure dynamic execution

Neat stuff, thanks for sharing it.

All different, all Unix