Dynamic SQL syntax using EXECUTE IMMEDIATE

Question

Dynamic SQL update statement as below:

EXECUTE IMMEDIATE 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;

l_prefix is the parameter hold the prefix of table name, the value assigned is T_ i.CUSTOMER_REF_ID and i.CUSTOMER_ID are the fields fetched from cursor.

The dynamic statement encounter error ORA-00904: invalid identifier when data fetched.

When rewrite it in static way, the query is working fine:

UPDATE T_CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = i.CUSTOMER_REF_ID WHERE CUSTOMER_ID = i.CUSTOMER_ID;

I know it must be something wrong on the concatenation of dynamic SQL, just couldn't pinpoint any as the compilation is fine.

put a dbms_output.put_line in place of execute immediate, with parentheses and see the output query, does it match with the static update statement? — Kaushik Nayak
– Kaushik Nayak, Commented Apr 17, 2018 at 16:09

Sentinel · Accepted Answer · 2018-04-17 16:31:15Z

4

WARNING: Dynamic SQL like this is susceptible to SQL Injection attacks. Wherever possible rewrite your dynamic SQL to use bind variables instead.

Instead of constructing your dynamic SQL like this:

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = '||i.CUSTOMER_REF_ID||' WHERE CUSTOMER_ID = '||i.CUSTOMER_ID;
EXECUTE IMMEDIATE L_SQL;

Use this:

L_SQL := 'UPDATE '||l_prefix||'CRS_CUSTOMERS SET CUSTOMER_SOURCE_REF_ID = :REF_ID WHERE CUSTOMER_ID = :CUST_ID';
EXECUTE IMMEDIATE L_SQL USING i.CUSTOMER_REF_ID, i.CUSTOMER_ID;

This is still subject to SQL injection at the l_prefix, but if you control that value programatically it may be OK. Also splitting the construction of the SQL and execution of the SQL into two steps allows you to more easily replace the EXECUTE IMMEDIATE with DBMS_OUTPUT.PUT_LINE(SQL); to check your query for syntax errors. You can also want to DBMS_OUTPUT.PUT_LINE your parameters i.CUSTOMER_REF_ID and i.CUSTOMER_ID to check their values.

answered Apr 17, 2018 at 16:31

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user2102665 Over a year ago

if i have more than one EXECUTE IMMEDIATE assign to L_SQL why that's only the first statement get executed? Should i assign to different variable?

user2102665 Over a year ago

I got it..it require a different set bind variables to reference each EXECUTE IMMEDIATE statement

Collectives™ on Stack Overflow

Dynamic SQL syntax using EXECUTE IMMEDIATE

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related