Systems Engineering and RDBMS

Back to the Basics: Using variable in a SELECT statement

Posted by decipherinfosys on August 12, 2008

Last week at a client site, one of the junior team members had asked whether we can use a variable in the SELECT statement or not. Requirement was to insert value in the new table from an existing table but certain columns in the new table will be populated with the variable. We will show a simple example to demonstrate this. First let us create a  table. Connect to SQL*Plus using proper credentials and create the following table.

CREATE TABLE TEST
(
ID                  NUMBER(9),
OBJ_TYPE         VARCHAR(5),
OBJ_NAME        VARCHAR(30),
CREATE_DATE     DATE
);

In the following anonymous PL/SQL block, we will use variable in INSERT INTO SELECT statement. We are aware that we can use pseudo columns like rownum, sysdate etc. in the SELECT statement. Similarly we can use variable declared in the PL/SQL block or any program unit in SELECT statement.  Execute following anonymous PL/SQL block from SQL*Plus session.

SET SERVEROUTPUT ON

DECLARE
v_objType VARCHAR2(10) := ‘TABLE’;
v_curDate DATE := SYSDATE;

BEGIN

INSERT INTO TEST(id,obj_Type,obj_name,create_Date)
SELECT object_id,v_objType,object_name,v_curdate
FROM user_objects
WHERE object_Type = ‘TABLE’
AND ROWNUM <= 10;

dbms_output.put_line(‘Rows Inserted = ‘ || SQL%ROWCOUNT);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;
/

Output indicates that variable usage in SELECT statement work correctly.

Rows Inserted = 10

PL/SQL procedure successfully completed.

Now let us see what happens, when we declare variable with the same name as of the column name from the table. We are using user_objects table in select statement and object_type is one of the column of user_objects table. In our PL/SQL block, we will use object_type as a local variable. Please execute following modified anonymous block and check the final results. No way, we are recommending that you should use declare variable similar to column name in the table and it is absolutely not the desirable coding practice.

To identify different data set, this time we are selecting ‘VIEW’ objects rather than the ‘TABLE’ objects.

DECLARE
object_Type VARCHAR2(19) := ‘TABLE’;
v_curDate DATE := SYSDATE;

BEGIN

INSERT INTO TEST(id,obj_Type,obj_name,create_Date)
SELECT object_id,object_type,object_name,v_curdate
FROM user_objects
WHERE object_Type = ‘VIEW’
AND ROWNUM <= 10;

dbms_output.put_line(‘Rows Inserted = ‘ || SQL%ROWCOUNT);
COMMIT;

EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
ROLLBACK;
END;
/

PL/SQL will also complete this time without any errors but let us check the result set. Result indicates that value from the table took precedence over the variable declared with the same name.

This type of declaration has also other consequences. In where clause, if we try to get the ‘PROCEDURE’ instead of ‘VIEW’ it will give following error. This is one more reason to avoid such declaration.

ORA-12899: value too large for column “CNFGPKMS”.”TEST”.”OBJ_TYPE” (actual: 9, maximum: 5)

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: