Back to Basics: Execute Immediate and SELECT statement

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)
AS
v_object_Type user_objects.object_type%TYPE;
BEGIN
BEGIN
EXECUTE IMMEDIATE
‘SELECT object_Type ‘
|| ‘ FROM user_objects ‘
|| ‘ WHERE object_name = : obj_name ‘
INTO v_object_Type
USING p_object_Name;
dbms_output.put_line(‘Object Type is ‘ || v_object_Type);
EXCEPTION
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);
END;
END test_proc;
/
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.

Source: http://decipherinfosys.wordpress.com/2008/09/11/back-to-basics-execute-immediate-and-select-statement/