Systems Engineering and RDBMS

Archive for August 12th, 2008

Visual Studio 2008 and .Net SP1

Posted by decipherinfosys on August 12, 2008

The first service pack for Visual Studio 2008 and .Net Framework 3.5 has been released by MSFT. You can download the Visual Studio 2008 service pack here and the updated .Net 3.5 Framework over here.  Microsoft had described the service pack release as a release focussed on improving rapid application development (and deployment), advancing appl. development through new feature sets and making data driven programming simpler.  We will blog more in the days to come on the new features that have been added.

Posted in Technology, Visual Studio | Leave a Comment »

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)

Posted in Oracle | Leave a Comment »