September 11, 2010

Use of Constants

Posted in Oracle, PL/SQL tagged Best Practice, Oracle, Oracle 11g, PL/SQL, Programming, SQL at 1:35 am by itsourteamwork

A constant is something whose value never changes. For ex, literal 5 is a constant, because the value 5 can’t be changed. Similarly, the character literal “BOOK” is also a constant.

A constant variable is a normal variable but this variable’s value can’t be changed.

Why to use a constant when the literal can be directly used in a program?

Well, lets take an example:

Suppose, there is a program, which calculates commission for the sales reps depending on the no of orders that they booked. If the total no of orders are greater than 10 then the sales rep will get 5 percent commission on the total order that he/she booked.

CREATE OR REPLACE PROCEDURE pr_calc_comm
(p_sales_rep_id ORDER_MST.sales_rep_id%TYPE) IS

v_order_count          PLS_INTEGER;
v_order_value          NUMBER;
v_comm_amt             NUMBER;
v_stmt                 VARCHAR2(32000);
BEGIN
v_stmt := ‘Select order_mst WHERE sales_rep_id =’ || p_sales_rep_id|| ‘ at 100′;

–Get the total no of orders and the order value
SELECT COUNT(orders_id) , SUM(order_value)
INTO v_order_count , v_order_value
FROM order_mst
WHERE sales_rep_id = p_sales_rep_id;

/* Check whether the order count is greater than 10.
If yes, then calculate the commission as 5% of the order value */
v_stmt :=’Calculating comm at 200′;

IF v_order_count > 10 THEN

v_comm_amt := v_order_value * 5 / 100;
v_stmt :=’Insert into comm_tab at 300’;

INSERT INTO comm_tab(sales_rep_id,comm_amt)
VALUES (p_sales_rep_id, v_comm_amt);

COMMIT;

END IF;

EXCEPTION
WHEN OTHERS THEN
Raise_application_error(‘Error’ ||SQLERRM || ‘ at ‘|| v_stmt);

END;

Above program will work fine.

What if, if the commission percentage is changed to 7 percent? Well, you can open the code and change the percentage to 7. Similarly, if the order count is changed to 15 from 10, then also, the program can be modified. You can replace 10 by 15.

What if, if the percentage 5 is used in more than one place in a big program, say a program of 1000 line? How can you find the value in that big program?

Now lets re-write the same program by using constants

CREATE OR REPLACE PROCEDURE pr_calc_comm
(p_sales_rep_id ORDER_MST.sales_rep_id%TYPE) IS

v_order_count                                  PLS_INTEGER;
v_order_value                                  NUMBER;
v_comm_amt                                   NUMBER;
v_stmt                                                VARCHAR2(32000);

–Declare constant variables
c_min_order_count CONSTANT PLS_INTEGER := 10;
c_comm_pct CONSTANT NUMBER(5,2) := 5;

BEGIN

v_stmt :=’Selet on order_mst WHERE sales_rep_id =’ || p_sales_rep_id|| ‘ at 100′;
–Get the total no of orders and the order value

SELECT COUNT(orders_id) , SUM(order_value)
INTO v_order_count , v_order_value
FROM order_mst
WHERE sales_rep_id = p_sales_rep_id;

v_stmt :=’Calculating comm at 200’;

/* Check whether the order count is greater than 10.
If yes, then calculate the commission as 5% of the order value */

IF v_order_count > c_min_order_count THEN

v_comm_amt := v_order_value * c_comm_pct / 100;
v_stmt :=’Insert into comm_tab at 300′;

INSERT INTO comm_tab(sales_rep_id,comm_amt)
VALUES (p_sales_rep_id, v_comm_amt);

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN
Raise_application_error(‘Error’ ||SQLERRM || ‘ at ‘|| v_stmt);

END;

We have added two constant variables and used them in the program. Now, if the values need to be changed, then simply we need to change the value in the declaration section.

Now, what if, if the same variable needs to be used in more than one procedure/function/package body? Individual constant variables can be declared in the respective procedure/function/package bodies. If the value of the constant variable needs to be changed then we have to change in all the places where ever the constant variables were declared.

So, if the same constant variables need to be used in more than one procedure, then create a package, which contains the constant variables, and use them in the individual programs.

Another use of Constants

Suppose, in a Banking application, there can be diff types of accounts. Savings account, Current Account, and Salary account etc. Lets take a piece of code from a program

IF v_account_type = ‘S’ THEN –Check for savings acc type
–DO something
ELSIF v_account_type =’C’ THEN –Check for current acc type
–DO something
ELSIF v_account_type =’L’ THEN — Check for Salary acc type
— DO something
ELSE
–display some error informing the user that Invalid acc type
— or do nothing
END IF;

Instead of using the special lateral values like S, C, L, constants can be declared as

c_acc_type_savings CONSTANT CHAR(1) := ‘S’;

c_acc_type_current CONSTANT CHAR(1) := ‘C’;

c_acc_type_salary CONSTANT CHAR(1) := ‘L’;

and the above IF statement can be re-written as

IF v_account_type = c_acc_type_savings THEN –Check for savings acc type
–DO something

ELSIF v_account_type = c_acc_type_current THEN –Check for current acc type
–DO something
ELSIF v_account_type = c_acc_type_salary THEN — Check for Salary acc type
— DO something
ELSE
–display some error informing the user that Invalid account type
— or do nothing
END IF;

Also, if a variable needs to be compared with a value called ‘Savings’ like

IF v_acc_type =’Savings’ THEN
—

END IF;

and, the value ‘Savings’ needs to be checked in many places, then there is a chance for making typo errors. Instead if a constant is used then the chance making typo errors can be eliminated

The best practice is to use constant variable instead of constant values.

Always declare constants at the appropriate scope rather than use special literal values in code. Do not use ‘magic’ literals in code.

The benefits of this methodology include the following:

Standardization: If a value has a significant meaning for multiple objects within a package or throughout a schema, or even at a broader scope, it can be defined and maintained easily and consistently across the entire spectrum.
Compile-time checking of all sentinal values. Since the actual value of the constant is maintained in one place only, there is no danger of multiple uses of the same value being misspelled, miss-cased (upper/lower) or otherwise inconsistent with one a nother, resulting in data or logic errors.

Ease of maintenance. If a value needs to change, the change is made in one place and all uses of it are automatically synchronized.
Last, but not the least, keep all the constants in a common packages and use them wherever required.

Permalink Leave a Comment

September 6, 2010

Performance Improvement with Associative Arrays in PL/SQL, Oracle

Posted in Oracle, PL/SQL, SQL tagged Best Practice, Oracle, Oracle 11g, Performance Imporvement, PL/SQL, Programming, SQL at 10:41 am by itsourteamwork

Lets see, how the performance can be improved with the use of Associative Arrays.

We are going using Associative Arrays for caching the data to reduce the function calls or select statements.

Suppose, there is a Function (get_dept_info) which returns the dept name for a given dept no from the dept table.

In a main pl/sql block, from the emp table, all the employees will be retrieved one by one in a loop, for each emp retrieved, the above function (get_dept_info) will be called to get the dept name (we can get the dept name by joining the emp table directly with dept table also). For explaining the concept only we are using this example.

The algorithm that we are going to follow is

Create a replica table (emp_new) for the emp table

Insert more data into the table (emp_new) so for the testing.

Create a pl/sql block

In the pl/sql block, loop thru the employees in the emp_new table

for each emp, try to get the dept name :

This part we will do in two ways

1: directly calling (get_dept_info) function

2: using associative arrays

then do any processing as per the requirement. In our case we will not do anything.

Now lets how to do this:

–Connect to the user Scott

SQL> SHOW USER;
USER is “SCOTT”

–Set the TIMI ON to see the time taken to execute the commands

SQL> SET TIMI ON;

–Create a table, exact replica of emp table

SQL> CREATE TABLE emp_new AS SELECT * FROM emp;

Table created.

Elapsed: 00:00:00.10

–Change the column width of empno to store any bigger no.

SQL> ALTER TABLE emp_new MODIFY empno NUMBER;

Table altered.

Elapsed: 00:00:00.03

–A pl/sql block to insert more rows into the emp_new table

SQL> BEGIN
2    FOR I IN 1..15 LOOP
3      INSERT INTO emp_new SELECT * FROM emp_new;
4    END LOOP;
5    UPDATE emp_new SET empno = empno+ROWNUM;
6    COMMIT;
7 END;
8
9 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:22.28
SQL>
SQL> SELECT COUNT(*) FROM emp_new;

COUNT(*)
———-
491520

Elapsed: 00:00:01.23

–Create the function to return the dept name for a given dept no

SQL> CREATE OR REPLACE    FUNCTION get_dept_info ( p_dept_no dept.deptno%TYPE) RETURN VARCHAR2 IS
2        v_dname dept.dname%TYPE;
3      BEGIN
4        SELECT dname INTO v_dname FROM dept WHERE deptno = p_dept_no;
5        RETURN v_dname;
6      EXCEPTION
7        WHEN OTHERS THEN
8          Raise_application_error (-20100,’Error ‘|| sqlerrm ||’ for deptno = ‘ || p_dept_no);
9      END get_dept_info ;
10
11 /

Function created.

Elapsed: 00:00:00.06

–Now, execute the following code to get the dept name for the dept no

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2      v_str                            VARCHAR2(32000):=’In Declaration Secion’;
3      v_dname                          dept.dname%TYPE ;
4
5 BEGIN
6    v_str :=’Before For loop at 100′;
7    FOR emp_data IN (SELECT * FROM emp_new WHERE deptno IS NOT NULL) LOOP –loop thru the employees
8      v_str :=’Calling get_dept_info (‘ ||emp_data.deptno ||’) at 200′;
9      v_dname := get_dept_info (emp_data.deptno); –function call to get the dname
10      –DBMS_OUTPUT.PUT_LINE(‘For dno ‘|| emp_data.deptno ||’ dname is ‘|| v_dname);
11      NULL;
12    END LOOP;
13
14
15 EXCEPTION
16    WHEN OTHERS THEN
17       Raise_application_error(-20010,’Error ‘||SQLERRM ||’ at ‘||v_str);
18 END;
19
20 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:14.57

–Change the code to get the dept name using associative arrays

–Create an associative array index by VARCHAR2;

–FOR each dept no retrieved from the emp table, first check in the associative arrary, if there exists a dept name

–If exists then return

–If not then, NO_DATA_FOUND exception will be raised

–In the exception handler, call the function (get_dept_info) to get the dept nam

–store the dept name in the associative array for the next use

–In this way, the no of calls to the function will be reduced(the max no of calls will be the no of distinct values in the emp table)

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2      v_str                            VARCHAR2(32000):=’In Declaration Secion’;
3      v_dname                          dept.dname%TYPE ;
4      TYPE dname_type IS TABLE OF dept.dname%TYPE INDEX BY VARCHAR2(10); –Array type declaration
5      v_dname_tab dname_type; –Variable for the array
6 BEGIN
7    v_str :=’Before For loop at 100′;
8    FOR emp_data IN (SELECT * FROM emp_new WHERE deptno IS NOT NULL) LOOP
9      BEGIN
10        v_dname := v_dname_tab(emp_data.deptno); — Check associative array
11      EXCEPTION
12        WHEN NO_DATA_FOUND THEN –Means the dept no is not there in the array
13          v_str :=’Calling get_dept_info (‘ ||emp_data.deptno ||’) at 200′;
14          v_dname := get_dept_info (emp_data.deptno);   –call the function to get the dept name
15          v_dname_tab(emp_data.deptno ) := v_dname ; –Store in the associative array for the future use
16      END;
17
18      –DBMS_OUTPUT.PUT_LINE(‘For dno ‘|| emp_data.deptno ||’ dname is ‘|| v_dname);
19      NULL;
20    END LOOP;
21
22
23 EXCEPTION
24    WHEN OTHERS THEN
25       Raise_application_error(-20010,’Error ‘||SQLERRM ||’ at ‘||v_str);
26 END;
27
28 /

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.76

The time taken for first block to execute is 14 secs. The time taken for the second block to execute is 2 secs. See the diff.

Permalink Leave a Comment

January 11, 2010

Exception Handling

Posted in Oracle, PL/SQL tagged Best Practice, Oracle, PL/SQL, Programming, SQL at 12:26 am by itsourteamwork

In some of the project which I was supporting, I faced lot of problems with the errors and the way the error messages were displayed. Most of the programs didn’t had an exception handler, even if some programs had exception handler, the error message was not useful. So, I thought of sharing my experience and how exception handler can help the programmers in debugging the application.

Version 1:

CREATE OR REPLACE PROCEDURE pr_emp
( p_column_x                  employees.column_x%TYPE
, p_column_y                  employees.column_y%TYPE
, p_column_z                  employees.column_z%TYPE) IS

v_column_a                           employees.column_a%TYPE;
v_column_b                           employees.column_b%TYPE
v_column_c                           employees.column_c%TYPE;
v_column_x                           departments.column_x%TYPE;
c_commission    CONSTANT NUMBER(2):=10;
BEGIN

SELECT column_a
INTO v_column_a
FROM employees
WHERE column_x = p_column_x
AND column_y = p_column_y;

–Some more statements will go here…
…
…
…
INSERT INTO table_a;…

UPDATE table_b SET …
WHERE condition;

IF SQL%ROWCOUNT = 0 THEN

SELECT column_c
INTO v_column_c
FROM employees
WHERE column_z = p_column_z;
INSERT INTO table_a …
END IF;

END pr_emp;

When the procedure is executed, if the program raises an exception, then the program will be terminated with un-handled exception. If the procedure is called from another program then, the calling program also terminated. Suppose, NO_DATA_FOUND exception is raised, then the procedure execution will be stopped and the following message will be displayed:

ERROR at line 1:
ORA-01403: no data found
ORA-06512: at “SCOTT.PR_EMP “, line 7
ORA-06512: at line 1

Same program with exception will allow the calling program to be executed normally (if the exception handler does not have RAISE_APPLICATION_ERROR in it. We will not use RAISE_APPLICATION_ERROR here; instead we will use DBMS_OUTPUT.PUT_LINE for displaying the error.

Version 2:

SELECT column_a
INTO v_column_a
FROM employees
WHERE column_x = p_column_x
AND column_y = p_column_y;

–Some more statements will go here…
…
…
…
INSERT INTO table_a;…

UPDATE table_b SET …
WHERE condition;

IF SQL%ROWCOUNT = 0 THEN

SELECT column_c
INTO v_column_c
FROM employees
WHERE column_z = p_column_z;
INSERT INTO table_a …
END IF;

EXCEPTION
WHEN OTHERS THEN
–The exception can be logged into a message table
— or can be displayed by using DBMS_OUTPUT.PUT_LINE
–Here we will use DBMS_OUTPUT package

DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM);

END pr_emp;

This will display the error message that occurred is the procedure. Suppose, the program raise a NO_DATA_FOUND exception, then program execution will be stopped and the following message will be displayed:

ORA-01403: no data found

By looking at the message we can say that the first message, which was displayed, is better than this message as the first message at least gives us the line no where which caused the exception.

Version 1 will not allow the execution of the calling program and the second one will allow. But the error message is not informative.

Let us change the code to get the line no also

Version 3:

–Declare a variable to hold the statement no
v_line_no PLS_INTEGER;
BEGIN
v_line_no := 10;
SELECT column_a
INTO v_column_a
FROM employees
WHERE column_x = p_column_x
AND column_y = p_column_y;

–Some more statements will go here…
…
…
…
v_line_no := 100;
INSERT INTO table_a;…

v_line_no := 110;
UPDATE table_b SET …
WHERE condition;

IF SQL%ROWCOUNT = 0 THEN
v_line_no := 120;

SELECT column_c
INTO v_column_c
FROM employees
WHERE column_z = p_column_z;

v_line_no := 130;
INSERT INTO table_a …

END IF;

EXCEPTION
WHEN OTHERS THEN
–The exception can be logged into a message table
— or can be displayed by using DBMS_OUTPUT.PUT_LINE
–Here we will use DBMS_OUTPUT package

DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||’ at line no ‘|| v_line_no);

END pr_emp;

Now, if the program is executed, and the select statement raised the first NO_DATA_FOUND exception, then the following message will be displayed

ORA-01403: no data found at line no 10

If the procedure is called directly by passing the parameters then, as a developer of the program you know that, the values that were passe
d to the program is not returning any data from the employees table.

For others, they have to open the source and see the select statement to know the table name that is not returning the data.

Let’s change the procedure again.

Version 4:

–Along with the statement no, we will try to get the table/statement also.
–We use a character variable instead of numeric variable
v_stmt VARCHAR2(32000);

–Declare a variable to hold the statement no
–v_line_no PLS_INTEGER;
BEGIN
v_stmt := ‘Select stmt on employees table at 10’;
SELECT column_a
INTO v_column_a
FROM employees
WHERE column_x = p_column_x
AND column_y = p_column_y;

–Some more statements will go here…
…
…
…
v_stmt := ‘Insert into table_a at 100’;
INSERT INTO table_a;…

v_stmt := ‘Update stmt on table_a at 110’;
UPDATE table_b SET …
WHERE condition;

IF SQL%ROWCOUNT = 0 THEN
v_stmt := ‘Select stmt on employees table at 120’;

SELECT column_c
INTO v_column_c
FROM employees
WHERE column_z = p_column_z;

v_stmt := ‘Insert into table_a at 130’;
INSERT INTO table_a …

END IF;

EXCEPTION
WHEN OTHERS THEN
–The exception can be logged into a message table
— or can be displayed by using DBMS_OUTPUT.PUT_LINE
–Here we will use DBMS_OUTPUT package

DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||’ at stmt = ‘|| v_stmt);

END pr_emp;

When this procedure is executed with the same parameter, then the error will be

ORA-01403: no data found at Select stmt on employees table at 10

This implies that, the select statement on employees table is not returning values. Again, as a developer you know that the table may not have any data for the values passed as parameters. Even other developers, who are going to execute can understand the error.

In the above code, it’s better to have the line no along with the statement. This is because; if the line no is not used in the v_stmt and the same NO_DATA_FOUND exception is raised, then how can you find out which select statement on employees raised the exception. We have two select statements on employee tables viz, at line 10 and at line 120. So, have the line no v_stmt will help provide some more information.

Now, lets consider another scenario. Suppose, this procedure is called from another program and the same NO_DATA_FOUND exception is raised. Because the procedure is called from another program, we don’t know what are the values passed to the procedure. How debug now?

Lets re-write the code

Version 5:

–Along with the statement no, we will try to get the table/statement also.
–We use a character variable instead of numeric variable
v_stmt VARCHAR2(32000);

–Declare a variable to hold the statement no
–v_line_no PLS_INTEGER;
BEGIN

v_stmt := ‘Select stmt on employees where column_x = ‘
|| p_column_x
|| ‘ AND column_y = ‘
|| p_column_y
|| ‘ at 10’;

SELECT column_a
INTO v_column_a
FROM employees
WHERE column_x = p_column_x
AND column_y = p_column_y;

–Some more statements will go here…
…
…
…
v_stmt := ‘Insert into table_a at 100’;
INSERT INTO table_a;…

v_stmt := ‘Update stmt on table_b where x = ‘|| v_column_a ||’ at 110′;
UPDATE table_b SET …
WHERE condition;

IF SQL%ROWCOUNT = 0 THEN
v_stmt := ‘Select stmt on employees where column_z = ‘|| p_column_z||’ at 120′;

SELECT column_c
INTO v_column_c
FROM employees
WHERE column_z = p_column_z;

v_stmt := ‘Insert into table_a at 130’;
INSERT INTO table_a …

END IF;

EXCEPTION
WHEN OTHERS THEN
–The exception can be logged into a message table
— or can be displayed by using DBMS_OUTPUT.PUT_LINE
–Here we will use DBMS_OUTPUT package

DBMS_OUTPUT.PUT_LINE(‘Error occurred in the procedure and the error is ‘||SQLERRM ||’ at stmt = ‘|| v_stmt);

END pr_emp;

For the same NO_DATA_FOUND exception, the following message will be displayed

ORA-01403: no data found at Select stmt on employees where column_x = 100 AND column_y = 150 at 10

Assuming that, p_column_x = 100 and p_column_y = 150 while calling procedure

So, you can find out the where condition which caused the select statement to fail. This type of messaging will help in finding out any other exception that was raised then by any statement

Last, but not the least, why the numbers were given in multiples of 10s. This is because, suppose statements were numbered like 1, 2, 3 etc and the program needs to be modified and more statements needs to be added in between the line 2 and 3 then how to number the statements. On the other hand, if the statements were numbered like 10, 20, 30 etc, then the added statements between 20 and 30 can be numbered as 12, 14 etc.

Those who worked in BASIC language can relate this.

All the statements need not to be numbered. Use v_stmt only if an exception is expected and needs to be handled.

Permalink Leave a Comment

December 29, 2009

Anchor Data types (Using %ROWTYPE) in Oracle

Posted in Oracle, PL/SQL tagged Best Practice, Oracle, PL/SQL at 3:21 am by itsourteamwork

I have discussed about the usage of %TYPE in my previous post. Here lets see how %ROWTYPE can be used.

The employees table structure in HR schema is

NAME	NULL?	TYPE
EMPLOYEE_ID		NUMBER(6)
FIRST_NAME		VARCHAR2(20)
LAST_NAME	NOT NULL	VARCHAR2(25)
EMAIL	NOT NULL	VARCHAR2(25)
PHONE_NO		VARCHAR2(20)
HIRE_DATE	NOT NULL	DATE
JOB_ID	NOT NULL	VARCHAR2(20)
SALARY		NUMBER(8,2)
COMMISSION_PCT		NUMBER(2,2)
MANAGER_ID		NUMBER(6)

Suppose two rows from the employees table needs to be retrieved and compared for some business requirement. The program will look like

CREATE OR REPLACE PROCEDURE pr_comp_emp

( p_employee_id_1 employees.employee_id%TYPE
, p_employee_id_2 employees.employee_id%TYPE) IS

–Variables to stores the FIRST record values
v_employee_id_1             employees.employee_id%TYPE;
v_first_name_1                employees.first_name%TYPE;
v_last_name_1                 employees.last_name%TYPE;
v_email_1                        employees.email%TYPE;
v_phone_number_1         employees.phone_number%TYPE;
v_hire_date_1                  employees.hire_date%TYPE;
v_job_id_1                       employees.job_id%TYPE;
v_salary_1                       employees.salary%TYPE;
v_commission_pct_1      employees.commission_pct%TYPE;
v_manager_id_1              employees.manager_id%TYPE;
v_department_id_1          employees.department_id%TYPE;

–Variables to stores the SECOND record values

v_employee_id_2             employees.employee_id%TYPE;
v_first_name_2                employees.first_name%TYPE;
v_last_name_2                employees.last_name%TYPE;
v_email_2                        employees.email%TYPE;
v_phone_number_2         employees.phone_number%TYPE;
v_hire_date_2                  employees.hire_date%TYPE;
v_job_id_2                       employees.job_id%TYPE;
v_salary_2                       employees.salary%TYPE;
v_commission_pct_2       employees.commission_pct%TYPE;
v_manager_id_2              employees.manager_id%TYPE;
v_department_id_2          employees.department_id%TYPE;

BEGIN

–Fetch the first record
SELECT *
      INTO v_employee_id_1
             , v_first_name_1
             , v_last_name_1
             , v_email_1
             , v_phone_number_1
             , v_hire_date_1
             , v_job_id_1
             , v_salary_1
             , v_commission_pct_1
             , v_manager_id_1
             , v_department_id_1
    FROM employees
   WHERE employee_id = p_employee_id_1;

–Fetch the Second record
SELECT *
      INTO v_employee_id_2
             , v_first_name_2
             , v_last_name_2
             , v_email_2
             , v_phone_number_2
             , v_hire_date_2
             , v_job_id_2
             , v_salary_2
             , v_commission_pct_2
             , v_manager_id_2
             , v_department_id_2
    FROM employees
   WHERE employee_id = p_employee_id_1;

–More statements will go here

EXCEPTION
WHEN OTHERS THEN
–Handle the exception
DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);

END;

The problems with the select statement are:

What if the no of columns in employees table is increased or decreased?
The same type of variables needs to be declared twice.

Lets re-write the same program again

CREATE OR REPLACE PROCEDURE pr_comp_emp
( p_employee_id_1 employees.employee_id%TYPE
, p_employee_id_2 employees.employee_id%TYPE) IS

–Instead of individual variables, lets declare a record type
v_emp_rec_1 employees%ROWTYPE;

v_emp_rec_2 employees%ROWTYPE;

BEGIN
–Fetch the first record SELECT * INTO v_emp_rec_1
FROM employees

WHERE employee_id = p_employee_id_1;

–Fetch the second record
SELECT * INTO v_emp_rec_2

FROM employees
WHERE employee_id = p_employee_id_2;

–More statements will go here…

EXCEPTION
    WHEN OTHERS THEN
        –Handle the exception
        DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);

END;

The above program eliminated the issues that were discussed earlier.
v_emp_rec_1 will have the same no of columns and the data type of the columns. So changing the table will effect the record variable also.#160;

Question may arise here, why should I declare a record type variables if few columns only required to be selected? When to use the record type variable? Well, its depends on developer to developer. I prefer to use a record type variable if more than 5 columns needs to be selected from a table.

Lets have look at the following program which has a select statement retrieving data from more than one table.

CREATE OR REPLACE PROCEDURE pr_comp_emp
( p_employee_id_1 employees.employee_id%TYPE
, p_employee_id_2 employees.employee_id%TYPE) IS

–Variables to store FIRST record

v_employee_id_1              employees.employee_id%TYPE;
v_first_name_1                 employees.first_name%TYPE;
v_last_name_1                  employees.last_name%TYPE;
v_salary_1                         employees.salary%TYPE;
v_department_id_1           departments.department_id%TYPE;
v_department_name_1      departments.department_name%TYPE;

–Variables to store SECOND record
v_employee_id_2             employees.employee_id%TYPE;
v_first_name_2                employees.first_name%TYPE;
v_last_name_2                employees.last_name%TYPE;
v_salary_2                       employees.salary%TYPE;
v_department_id_2          departments.department_id%TYPE;
    v_department_name_2 departments.department_name%TYPE;
BEGIN

SELECT employee_id
          , first_name
          , last_name
          , salary
          , d.department_id
          , d.department_name
   INTO v_employee_id_1
          , v_first_name_1
          , v_last_name_1
          , v_salary_1
          , v_department_id_1
          , v_department_name_1
FROM employees e, departments d
WHERE e.department_id = d.department_id
   AND employee_id = p_employee_id_1;

SELECT employee_id
          , first_name
          , last_name
          , salary
          , d.department_id
          , d.department_name
   INTO v_employee_id_2
          , v_first_name_2
          , v_last_name_2
          , v_salary_2
          , v_department_id_2
          , v_department_name_2
FROM employees e, departments d
WHERE e.department_id = d.department_id
   AND employee_id = p_employee_id_2;

–More statements will go here…

EXCEPTION
    WHEN OTHERS THEN
      –Handle the exception
      DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);
END;

In this case how to declare a record type?

Well, a cursor with the same select statement can be declared and a variable of the cursor type can be declare as shown below

CREATE OR REPLACE PROCEDURE pr_comp_emp
( p_employee_id_1 employees.employee_id%TYPE
, p_employee_id_2 employees.employee_id%TYPE) IS

–Declare a cursor with the same select statement that is in use

CURSOR emp_det_cur IS
    SELECT employee_id
             , first_name
             , last_name
             , salary
             , d.department_id
             , d.department_name
      FROM employees e, departments d
    WHERE e.department_id = d.department_id ;

–Variables to store FIRST record
v_emp_det_1   emp_det_cur%ROWTYPE;

–Variables to store SECOND record
v_emp_det_2   emp_det_cur%ROWTYPE;

BEGIN

SELECT employee_id
            , first_name
            , last_name
            , salary
            , d.department_id
            , d.department_name
   INTO v_emp_det_1
    FROM employees e, departments d
WHERE e.department_id = d.department_id
      AND employee_id = p_employee_id_1;

SELECT employee_id
            , first_name
            , last_name
            , salary
            , d.department_id
            , d.department_name
   INTO v_emp_det_2
    FROM employees e, departments d
WHERE e.department_id = d.department_id
      AND employee_id = p_employee_id_2;

–More statements will go here…

EXCEPTION
    WHEN OTHERS THEN
      –Handle the exception
      DBMS_OUTPUT.PUT_LINE(‘Error’||SQLERRM);
END;

So, by using a dummy cursor as shown, record types of the cursor type can be declared. If the same record type of needs to be declared in my programs, then its better to declare the cursor in a package body and use the type in the variable declaration.

Below is the common package containing the cursor.

CREATE OR REPLACE
PACKAGE pkg_common AS

CURSOR emp_det_cur IS
      SELECT employee_id
               , first_name
               , last_name
               , salary
               , d.department_id
               , d.department_name
       FROM employees e, departments d
    WHERE e.department_id = d.department_id ;

END pkg_common;

Now lets re-write the declaration section again

CREATE OR REPLACE

PROCEDURE pr_comp_emp
( p_employee_id_1 employees.employee_id%TYPE
, p_employee_id_2 employees.employee_id%TYPE) IS

–Variables to store FIRST record
   v_emp_det_1   pkg_common.emp_det_cur%ROWTYPE;

–Variables to store SECOND record

   v_emp_det_2   pkg_common.emp_det_cur%ROWTYPE;

BEGIN
–Code will go here

NULL;

END;

Permalink Leave a Comment

December 25, 2009

New Features in Oracle 11g : Caching and Pooling

Posted in Oracle, PL/SQL, SQL tagged Oracle, Oracle New Features, PL/SQL, SQL at 1:45 am by itsourteamwork

Another good article on Caching and Pooling.

I was looking for the caching feature in 2005 as one of my select statement used to call a function more then 5 times with the same parameters. Any how “Better late than never”

Permalink Leave a Comment

December 15, 2009

New Features in Oracle 11g : Compound Triggers, Changing Trigger’s firing Order

Posted in Oracle, PL/SQL tagged Oracle, Oracle New Features, PL/SQL at 6:29 am by itsourteamwork

Another new feature of Oracle 11g, this time on PL/SQL. This feature is really good. We can have compound triggers on a table, we can set the order in which the triggers should fire and many more.

Click Here

Permalink Leave a Comment

December 1, 2009

Anchor data types (Using %TYPE) in Oracle

Posted in Oracle, PL/SQL tagged Best Practice, Oracle, PL/SQL, Programming at 3:18 am by itsourteamwork

I have seen some projects where the columns were declared as

v_prod_description VARCHAR2(100)

which stores the product description up to 50 characters. And the value fetched into v_prod_description as

SELECT prod_description

INTO v_prod_description

FROM product_mst

WHERE prod_code = v_prod_code;

This works fine as long as the width of the underlying column prod_description in table product_mst is 100 chars. Suppose, the business wants to change the product description to 256 characters, then the column width can be altered to 256 characters.

What happens if the same program is executed and the value of the product description is more than 100 characters?

The program will raise an exception and depending on the handler the program execution may stop or continue. But the important thing is that, the product description will not be available in the variable v_prod_description. In order to get the description, the variable width needs to be modified. If the variable is used in many places and in many programs then, as a developer all the programs need to be modified which is very difficult and time consuming.

On the other hand, if the variable is declared as

v_prod_description product_mst.prod_description%TYPE;

then, the variable will change its data type automatically whenever the data type of prod_description column in the product_mst is changed.

By following this practice, changing the data types of variables in the programs can be avoided when the underlying column’s data type is changed.

In the next post, will explain how can we use %ROWTYPE.

Permalink 1 Comment

	Marius Frøisland on OBIEE 11g : New Feature : Arch…
	zdinesh03 on OBIEE 11g : New Feature : Arch…
	Chandrasekhar Mandal on Bharatgas Web Site (http://www…
	vinod on Dhee 3: Winner Raghu Master an…
	Basant singh on Bharatgas Web Site (http://www…

Itsourteamwork : More 'WE' Less 'I'

September 11, 2010

Use of Constants

September 6, 2010

Performance Improvement with Associative Arrays in PL/SQL, Oracle

January 11, 2010

Exception Handling

December 29, 2009

Anchor Data types (Using %ROWTYPE) in Oracle

December 25, 2009

New Features in Oracle 11g : Caching and Pooling

December 15, 2009

New Features in Oracle 11g : Compound Triggers, Changing Trigger’s firing Order

December 1, 2009

Anchor data types (Using %TYPE) in Oracle

Search

Blog Stats

Hits Counter

Archives

Categories

Top Posts

Recent Posts

Recent Comments

Disclaimer

Blogroll

Pages

Subscribe to Blog via Email

Blogroll

Top Rated

Meta

Poll