CODEWITHSUNDEEP
oracle-databaseplsqldynamic-sql
Mohammad Taleshi
Mohammad Taleshi

Reputation: 41

execute immediate a pl/sql that having execute immediate

I have a query string that have execute immediate.

how can I Execute Immediate this PL/SQL?

query string = 'Execute immediate select ....';

Want do this: Execute immediate 'query string';

That became this: Execute immediate 'Execute immediate select ....;';

Do you know How can I do this?

Upvotes: 0

Views: 2040

Answers (1)

Matthew McPeak
Matthew McPeak

Reputation: 17924

Without commenting on whether it is normal or wise: yes, I believe you can do it. That is, I've never seen anything written about EXECUTE IMMEDIATE that suggested it is not re-entrant. Plus, it works if you try it.

Here is a simple, typical EXECUTE IMMEDIATE call:

DECLARE
  l_count NUMBER;
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :l_count FROM DBA_OBJECTS WHERE ROWNUM <= 100' INTO l_count;
  DBMS_OUTPUT.PUT_LINE ('l_count = ' || l_count);
END;

Here is basically the same thing, but with EXECUTE IMMEDIATE calls nested to two levels:

DECLARE
  l_outer_count NUMBER;
BEGIN
EXECUTE IMMEDIATE q'!
BEGIN
  EXECUTE IMMEDIATE 'SELECT COUNT(*) INTO :x FROM DBA_OBJECTS WHERE ROWNUM <= 100' INTO :l_outer_count;
END;
!'
USING IN OUT l_outer_count;
DBMS_OUTPUT.PUT_LINE('l_outer_count = ' || l_outer_count);
END;

I have never encountered a need to do this.

Upvotes: 5

Related Questions