CODEWITHSUNDEEP
oracle-databaseplsql
John O
John O

Reputation: 5423

Is it possible to preserve the values of variables in a declare section of an anonymous PL-SQL block ran from an execute immediate statement?

If I have PL-SQL code of the following form:

DECLARE
  v_glob VARCHAR2(4000) := q'<
  DECLARE xyz VARCHAR2(7); BEGIN DBMS_OUTPUT.PUT_LINE('test45654645'); END;
  >';
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_glob);
  EXECUTE IMMEDIATE (v_glob);
  -- What was the value of xyz here?
END;

Is there any way to cheat scope and retain those values? For my purposes, it would be permissible to add extra code into v_glob if it could be done in a regular way.

The Oracle documentation certainly makes it seem as if this isn't possible, and as I understand it, it's not supposed to be. Is there a clever workaround that I'm not seeing?

Upvotes: 0

Views: 359

Answers (1)

Egor Skriptunoff
Egor Skriptunoff

Reputation: 23747

DECLARE
  v_glob VARCHAR2(4000) := q'<
  DECLARE 
    xyz VARCHAR2(7) := 'hidden!'; 
  BEGIN 
    DBMS_OUTPUT.PUT_LINE('test45654645'); 
    :output := xyz;
  END;
  >';
  v_output varchar2(7);
BEGIN
  DBMS_OUTPUT.PUT_LINE(v_glob);
  EXECUTE IMMEDIATE (v_glob) using out v_output;
  -- What was the value of xyz here?
  DBMS_OUTPUT.PUT_LINE(v_output);
END;

Upvotes: 1

Related Questions