Back to Basics: Execute Immediate and SELECT statement
Posted by decipherinfosys on September 11, 2008
Execute Immediate statement prepares and executes the dynamic statement. For single row queries we can get the data using ‘INTO’ clause and store it in the variable. We can also use bind variable using ‘USING’ clause. Placeholder for the bind variables are defined as text, preceded by colon ( : ). In this blog post, we will show you how can we use Execute Immediate statement to retrieve the data. Following is the small PL/SQL stored procedure that demonstrates the use of Execute Immediate. Connect to the database via SQL*Plus using proper credentials and create the following procedure.
CREATE OR REPLACE PROCEDURE test_proc(p_object_Name IN VARCHAR2)
‘SELECT object_Type ‘
|| ‘ FROM user_objects ‘
|| ‘ WHERE object_name = : obj_name ‘
dbms_output.put_line(‘Object Type is ‘ || v_object_Type);
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20950, ‘object does not exist: ‘ || p_object_name);
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20951, ‘Error retrieving oject name: ‘ || SQLERRM);
Once the procedure is successfully created, let us execute it to see the results.
SQL> exec test_proc(‘TEST_PROC’);
Object Type is PROCEDURE
PL/SQL procedure successfully completed.
Here we have used bind variable to improve the performance. It is used in ‘USING’ clause. During execution : obj_name placeholder value will be replaced with the value from p_object_name.
Couple of things we have to remember when dealing with dynamic sqls are
• ; is not placed at the end of the SELECT statement, but it is placed at the end of the Execute Immediate statement.
• Similar thing is also with INTO clause. INTO is outside of quoted SELECT statement.
As demonstrated above, Execute Immediate is the way to deal with dynamic sqls. In next blog, we will demonstrate how can we use multiple rows using BULK COLLECT with dynamic sql.