gs79
asked on
execute immediate plsql block
I am dynamically generating following below plsql block into a variable.
begin
var(var.count()+1) := 'MY_PKG.PROC(1,null,123)';
var(var.count()+1):= 'MY_PKG.PROC(2,null,223)';
myschema.parallel_job_pkg.
myschema.parallel_job_pkg.
end;
When the above plsql block is assigned to a variable lets say v_plsql and execute immediate is issued on the variable, it says the variable 'var' is not defined. But I have defined this variable in the declaration section of the procedure which is issuing the execute immediate statement. So how to execute dynamically generated plsql block such as above
Please let me know if you need any further information
begin
var(var.count()+1) := 'MY_PKG.PROC(1,null,123)';
var(var.count()+1):= 'MY_PKG.PROC(2,null,223)';
myschema.parallel_job_pkg.
myschema.parallel_job_pkg.
end;
When the above plsql block is assigned to a variable lets say v_plsql and execute immediate is issued on the variable, it says the variable 'var' is not defined. But I have defined this variable in the declaration section of the procedure which is issuing the execute immediate statement. So how to execute dynamically generated plsql block such as above
Please let me know if you need any further information
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I added the declaration part to the anon block it works!
Thanks
Thanks
ASKER
As I said in my OP, the variables are also declared in the calling procedure. Do you mean I need to have a declare section in the anonymous block I am constructing
Here is how the procedure and EXECUTE IMMEDIATE is called
procedure xyz as
var table_type;
pstat varchar2(1000);
v_plsql_block varchar2(32000);
..
..
..
begin
..
..
..
v_plsql_block := 'begin; <all the above statements in OP>; end;
execute immediate v_plsql_block;
end;
/
suppose if replace the execute immediate with the dynamically generated block inside the procedure xyz, it works fine.