PL/SQL procedure dynamic execution

[Nicolas Dumeige]

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;
/

[Nicolas Dumeige]

Nevermind, I found it, quite simple :

EXECUTE IMMEDIATE 'BEGIN '||instruction||'; END; ' ;

[Yogeeraj_1]

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

[Yogeeraj_1]

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

[Nicolas Dumeige]

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

[Yogeeraj_1 · Accepted solution]

hi nicolas

attached a generic example.

hope this helps!

Regards
yogeeraj

[Nicolas Dumeige]

Neat stuff, thanks for sharing it.