Using dynamic sql inside Oracle stored procedure

dynamic-sqloracle

Assuming I have the following procedure

CREATE PROCEDURE foo (table1_id IN TABLE1.table1_id%type, 
                     table1_val IN TABLE1.table1_value%type) 
AS 
  SQL_UPDATE VARCHAR2(500) := 'UPDATE TABLE1 SET table1_value =:1 WHERE table1_id = :2';
BEGIN
  --.....
  --1 : 
    EXECUTE IMMEDIATE SQL_UPDATE USING foo.table1_val, foo.table1_id;
  --2 : 
    UPDATE TABLE1 SET table1_value = foo.table1_val WHERE table1_id = foo.table1_id;

END;

Beside the style/readability, is it any performance penalty for using dynamic query (1) compared to (2) in such cases (I mean when it's absolutely avoidable) ?

Thank you.

Best Answer

The only reason why I might do that is if I needed to address an object that might not exist at compile time -- for example if I had code to create new external tables as required.

As this implies, the dynamic SQL statement is not parsed when the PL/SQL compiles, so you have no idea whether it is correct or not, and dependencies are not stored in the database.

Related Solutions

Sql-server – Select permission inside stored procedure

Ok, on the basis of the above comment and as per my suspicion - it seems as though you are trying to execute dynamic SQL within your stored procedure.

What you need to remember is that when you do this it does not get executed within the context of the stored procedure - it gets executed within a new session. Because of this, the fact that the statement is being called within a stored procedure is a moot point, and you will need to grant explicit permission on the objects that your dynamic SQL is using.

If you don't want to do this I would refactor your stored procedure to not use dynamic SQL.

The below link from Microsoft should help you with your problem:

PRB: Security Context of Dynamic SQL Statements Inside a Stored Procedure (Wayback Machine archive)

This behavior occurs because a dynamic execution query (sp_executesql or EXECUTE) executes in a separate context from the main stored procedure; it executes in the security context of the user that executes the stored procedure and not in the security context of the owner of the stored procedure.

This is also discussed in the (more current) Microsoft Docs article:

Writing Secure Dynamic SQL in SQL Server

Executing dynamically created SQL statements in your procedural code breaks the ownership chain, causing SQL Server to check the permissions of the caller against the objects being accessed by the dynamic SQL.

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

The other thing you could do is create a view that unions the like tables together, and include a defined column with the table name, then just do a select against that view with the tablename in the where clause.

CREATE VIEW tablesviews AS
SELECT columns, 'TABLE1' as tablename
FROM Table1
UNION ALL
SELECT columns, 'TABLE2' as tablename
FROM Table2
...


SELECT {some columns}
FROM  tableviews
WHERE Tablename=@table and [other where clauses]

Best Answer

Related Solutions

Sql-server – Select permission inside stored procedure

Sql-server – How to use same stored procedure for different tables and avoid using dynamic sql

Related Question