September 11, 2010

Use of Constants

Posted in Oracle, PL/SQL tagged , , , , , 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.

September 6, 2010

Performance Improvement with Associative Arrays in PL/SQL, Oracle

Posted in Oracle, PL/SQL, SQL tagged , , , , , , 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

  1. Create a replica table (emp_new) for the emp table
  2. Insert more data into the table (emp_new) so for the testing.
  3. Create a pl/sql block
  4. In the pl/sql block, loop thru the employees in the emp_new table
  5. for each emp, try to get the dept name :
  6. This part we will do in two ways
  7. 1: directly calling (get_dept_info) function
  8. 2: using associative arrays
  9. 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.

August 13, 2010

All Columns Prompted : A smarter way to call detail requests from multiple requests in Oracle BIEE

Posted in OBI EE tagged , , , , at 10:54 am by itsourteamwork


I would like to share of my experience in calling a report from multiple reports.

Let me explain in terms of Paint subject area.

Case 1:

We have a detailed report, which can be called from a master report.
The master report is made of Region column, Year and Dollars.


The detailed report is attached to the dollars column so that the transaction details for the corresponding region and year can be displayed.



When the user clicks on the Dollars column then the detailed report will be called


This will work fine as long as the user clicks on the dollars column.
What if
1. The user drills down to markets first then clicks on the dollars column to get the transaction for the region, market and year?

2. The user drills down to month by licking on the year column and then clicks on the dollars column to get the transactons for a region, year and month?



3. The user drills down to any level of the hierachy and clicks on the dollars column to get the transaction level details of the dims?


In any of the above situtations, the detail report will show the transaction related to the parent region and year column. In order to display the transactions for any level in the markets dim or time dim, all the column of the markets dim and time dim should prompted in the detailed report.

In the same way, if the same detailed report needs to be called from another report constructed using a top level column from products and year column then the detailed report should be ready to accept any level of dim value from product dim or year dim.

In general a detailed report can be called from any master report and the master report can be constructed by using any column at any level from any any dim. So, the detailed report should be ready to display the transaction for any dim value at any level.

For this reason, we can add all the columns from the all the dims as prompted filters to the detailed reports.

Csae 2:

The master report is added to a dashboard page, which is having year prompt and brand column from product dim prompt.
So the master report should be prompted for year and the brand column.
In future if another prompt is added to the same dashboard page then, the master report needs to be modified by added that column as prompted filter.

In other words, the master report shold be ready to accept any dim value from the prompts.

In this case also, we can add all the columns from the dims as prompted filters to the master reports

In both the cases we have added all the columns from the all the dims as prompted filters to both master and detailed reports.

Now, what if, one of the dim is change, like a new column is added or a column is removed?
If the column is added then its ok for the reprots, only thing the reports are not ready to accept the value. But if a column is removed then, we have to open each report and remove the column from the filter; other wise the reports will throw error.
Again, as per the case 1 and case 2, the added column needs to be added as prompted filter in the all reports; means, all the reports needs to be modified one by one.

Changing all the reports is not practically possible.

What we did is, we have created some saved filters, one for each dim. These filters will contain all the columns of the dim as prompted as shown below


A saved filter for all the columns of Markets Dim

A saved filter for all the columns of Products Dim

A saved filter for all the columns of Periods Dim


We have created another saved filter called “All Prompted Objects” which is a combination of all the filter which were created for each dim as shown below


Now we have added this “All prompted Objects” to all the reports (master and detailed).

So if a column is added to the dim (say to Products dim), then we will add the column to the “Products objects filter” and the “All prompted objects” will use that. In the same way, if a column is removed, then the column will be removed from the “prompted objects filter” and the “All prompted objects” filter will not use it.

One of the new joinee in our team asked us, why we have to create the filters first at the dim level and bundel them into one “All prompted obects”, why all the collumns can’t be added directly to the “All prompted object” filter?

Well, the reason is, there are few reports which should not accept the values from one dim (say time dim). For that report we have to create another “All prompted object without time” and add all the columns as prompted. Like we have another report which should not accept values from Region Dim. So have to create another “All prompted objects without Region” filter.

Now, if a column is added or removed from a dim, then we need to modify three “All prompted objects” filter. Instead of that, if we create a save filter for each dim and create “All prompted obects” on top of them, the we have to modify only one saved filter.


Now we have added this “All prompted Objects” to all the reports (master and detailed).

So if a column is added to the dim (say to Products dim), then we will add the column to the “Products objects filter” and the “All prompted objects” will use that. In the same way, if a column is removed, then the column will be removed from the “prompted objects filter” and the “All prompted objects” filter will not use it.

One of the new joinee in our team asked us, why we have to create the filters first at the dim level and bundel them into one “All prompted obects”, why all the collumns can’t be added directly to the “All prompted object” filter?

Well, the reason is, there are few reports which should not accept the values from one dim (say time dim). For that report we have to create another “All prompted object without time” and add all the columns as prompted. Like we have another report which should not accept values from Region Dim. So have to create another “All prompted objects without Region” filter.

Now, if a column is added or removed from a dim, then we need to modify three “All prompted objects” filter. Instead of that, if we create a save filter for each dim and create “All prompted obects” on top of them, the we have to modify only one saved filter.

January 11, 2010

Exception Handling

Posted in Oracle, PL/SQL tagged , , , , 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:


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;

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:


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;

–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:


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;

–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:


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;

–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.

December 29, 2009

Anchor Data types (Using %ROWTYPE) in Oracle

Posted in Oracle, PL/SQL tagged , , 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;

 

free counters

December 1, 2009

Anchor data types (Using %TYPE) in Oracle

Posted in Oracle, PL/SQL tagged , , , 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.