2

I am new to PL SQL Procedures , we have this line of code inside the Execution section of a Stored Procedure .

I am having a query here , please tell me whats the use of using EXECUTE IMMEDIATE for a DML Statement Here ? and in what cases we should use EXECUTE IMMEDIATE?

v_update_query2 := 'INSERT INTO '||p_country||'.DETAILS ( ID, STATUS, DEST_SYSTEM, OUT_TIME ) VALUES ('''
    ||v_txn_id ||''','||'''T081'''||','||'''CLEARING'''||', SYSDATE)';



EXECUTE IMMEDIATE v_update_query1 ;
Sathyajith Bhat
  • 21,321
  • 22
  • 95
  • 134

1 Answers1

4

EXECUTE IMMEDIATE is the only way to have variable references to schema objects - eg table names, column names etc.

It allows you to build up any string and then execute that string as an SQL statement.
Without it, procedure variables can only be used for values in sql, eg select * from table where column = my_variable

In your example, the table name is being provided by the p_country variable - that's a schema element, so you need EXECUTE IMMEDIATE.

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • Bohemian is entirely correct, howver, you should also research dynamic SQL in the documentation and replace your literals with bind variables for both performance and security from SQL Injection. – Ollie Jul 25 '11 at 10:45
  • You can't replace the reference to schema objects within dynamic sql with bind variables, so `p_country` will still be a problem. In SQL Server I would use `quotename` on `p_country` to make it safe. I do not know the equivelent in Oracle land. – Shannon Severance Jul 25 '11 at 19:30