I want to execute an sql statement dynamically in my procedure. As far as I could see, the binding is done according to the order of the usage.
Is there a way in which I can say something like
:a --> par_a_
2 Answers
There is a differnce between execute immediate 'plsql code' and execute immediate 'sql', In dynamic pl/sql oracle will use real bindes and you may specify it once in correct order and it will replace all binds if there are repeted. with sql you should spesify all binds instead of repeting it.
declare
l_sql varchar2(4000) := 'select :a from dual union all select :a from dual';
l_pl_sql varchar2(4000) := 'begin dbms_output.put_line(:a); dbms_output.put_line(:a); end;';
type t_tab_str is table of varchar2(4000);
l_res t_tab_str ;
begin
execute immediate l_sql bulk collect into l_res using '1','2';
for i in 1.. l_res.last loop
dbms_output.put_line(l_res(i));
end loop;
execute immediate l_pl_sql using '1';
end;
you may use dbms_sql and it function bind
declare
l_sql varchar2(4000) := 'select :a from dual union all select :a from dual';
type t_tab_str is table of varchar2(4000);
l_res t_tab_str ;
l_sql_id number;
l_ret number;
type curtype is ref cursor;
l_cursor curtype ;
begin
dbms_sql.parse(l_sql_id ,l_sql,dbms_sql.native);
dbms_sql.bind_variable(l_sql_id,'a','1');
l_ret := dbms_sql.execute(l_sql_id);
l_cursor := dbms_sql.to_refcursor(l_sql_id);
fetch l_cursor bulk collect into l_res;
for i in 1.. l_res.last loop
dbms_output.put_line(l_res(i));
end loop;
end;
Comments
Seems to me what you are after is USING keyword.
Below is example from oracle documentation.
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
/* Specify bind arguments in USING clause.
Specify mode for first parameter.
Modes of other parameters are correct by default. */
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/dynamic.htm
I want to execute an sql statement dynamicallyWhat kind of SQL statement?