Systems Engineering and RDBMS

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)
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. In next blog, we will demonstrate how can we use multiple rows using BULK COLLECT with dynamic sql.

One Response to “Back to Basics: Execute Immediate and SELECT statement”

  1. […] bookmarks tagged immediate Back to Basics: Execute Immediate and SELECT state… saved by 7 others     Josephjonasrules7117 bookmarked on 09/16/08 | […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: