How to build dynamic SQL with using

Question

I am trying to make dynamic sql like this 'select col1,col2 from '|| my_table ||' it works fine but I want to write like this sql_stmt:='select col1,col2 from :myTable'; execute immediate sql_stmt using my_table; but I have error I have same error when I want to do something like this tooo v_filter := my_proc(); sql_stmt:='select col1,col2 from my_table where :filter' execute immediate sql_stmt using v_filter; is it impossible to build dynamic sql like this with using ? If it is impossible what is another way to avoid sql injections ?

Answer 1

When you want to use table names in dynamic SQL, yes - you'll have to concatenate them. In order to avoid SQL injection, use DBMS_ASSERT.SQL_OBJECT_NAME.

Here's an example:

SQL> create or replace procedure p_test (par_table in varchar2) is
 2 l_table varchar2(30);
 3 l_str varchar2(200);
 4 l_cnt number;
 5 begin
 6 l_table := dbms_assert.sql_object_name(par_table);
 7
 8 l_str := 'select count(*) from ' || par_table;
 9 execute immediate (l_str) into l_cnt;
 10 dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 11 end;
 12 /
Procedure created.
SQL>
SQL> exec p_test('dept');
Table contains 4 rows
PL/SQL procedure successfully completed.
SQL> exec p_test('delete from emp');
BEGIN p_test('delete from emp'); END;
*
ERROR at line 1:
ORA-44002: invalid object name
ORA-06512: at "SYS.DBMS_ASSERT", line 316
ORA-06512: at "SCOTT.P_TEST", line 6
ORA-06512: at line 1
SQL>

[EDIT: WHERE clause]

This works:

SQL> create or replace procedure p_test (par_table in varchar2,
 2 par_filter in varchar2) is
 3 l_table varchar2(30);
 4 l_str varchar2(200);
 5 l_cnt number;
 6 begin
 7 l_table := dbms_assert.sql_object_name(par_table);
 8
 9 l_str := 'select count(*) from ' || par_table ||
 10 ' where deptno = :filter';
 11 execute immediate (l_str) into l_cnt using par_filter;
 12 dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 13 end;
 14 /
Procedure created.
SQL> exec p_test('emp', '10');
Table contains 3 rows
PL/SQL procedure successfully completed.
SQL>

WHERE clause, modified so that it contains only the WHERE keyword, while the rest is to be used as a parameter:

SQL> create or replace procedure p_test (par_table in varchar2,
 2 par_filter in varchar2) is
 3 l_table varchar2(30);
 4 l_str varchar2(200);
 5 l_cnt number;
 6 begin
 7 l_table := dbms_assert.sql_object_name(par_table);
 8
 9 l_str := 'select count(*) from ' || par_table ||
 10 ' where :filter';
 11 execute immediate (l_str) into l_cnt using par_filter;
 12 dbms_output.put_line('Table contains ' || l_cnt || ' rows');
 13 end;
 14 /
Procedure created.
SQL> exec p_test('emp', 'deptno = 10');
BEGIN p_test('emp', 'deptno = 10'); END;
*
ERROR at line 1:
ORA-00920: invalid relational operator
ORA-06512: at "SCOTT.P_TEST", line 11
ORA-06512: at line 1
SQL>

It won't work; is that what you're asking?

Some more reading about dynamic SQL on Oracle, as well as here, on Stack overflow (How can I create a dynamic WHERE clause.

• user8368945: It works fine for table name and how about where clause where I want to concatenate statement with filter ?