September 11, 2010
Use of Constants
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?
|
CREATE OR REPLACE PROCEDURE pr_calc_comm v_order_count PLS_INTEGER; –Get the total no of orders and the order value /* Check whether the order count is greater than 10. IF v_order_count > 10 THEN v_comm_amt := v_order_value * 5 / 100; INSERT INTO comm_tab(sales_rep_id,comm_amt) COMMIT; END IF; EXCEPTION END; |
|
CREATE OR REPLACE PROCEDURE pr_calc_comm v_order_count PLS_INTEGER; –Declare constant variables v_stmt :=’Selet on order_mst WHERE sales_rep_id =’ || p_sales_rep_id|| ‘ at 100′; SELECT COUNT(orders_id) , SUM(order_value) v_stmt :=’Calculating comm at 200’; /* Check whether the order count is greater than 10. IF v_order_count > c_min_order_count THEN v_comm_amt := v_order_value * c_comm_pct / 100; INSERT INTO comm_tab(sales_rep_id,comm_amt) COMMIT; END IF; EXCEPTION WHEN OTHERS THEN END; |
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;
c_acc_type_savings CONSTANT CHAR(1) := ‘S’;c_acc_type_current CONSTANT CHAR(1) := ‘C’;c_acc_type_salary CONSTANT CHAR(1) := ‘L’;
IF v_account_type = c_acc_type_savings THEN –Check for savings acc type
–DO somethingELSIF 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;
- 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
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(*)
———-
491520Elapsed: 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 3, 2010
Not In Vs Minus in SELECT statement of Oracle
What is the diff between NOT IN and MINUS. As per the SET theory, both should yield the same results.
Lets take an example : From the scott schema by using the Emp and Dept table, if the following queries were fired to get the list of DEPARTMENTS that do not have any EMPLOYEE, then,
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
and
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
both queries will return no rows.
————————————————————–
Now, lets look at the following data:
DEPARTMENT table got few more rows
| DEPTNO | DNAME | LOC |
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
| 50 | QUALITY | CHICAGO |
| 60 | IT | CHICAGO |
| 70 | MARKETING | NEY YORK |
EMPLOYEE table
For the first row, employee no 9999, there is no department assigned. Deptno is NULL.
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO | STATUS |
| 9999 | James | James | 7782 | 23-Jan-82 | 1300 | (null) | (null) | Y |
| 7369 | SMITH | CLERK | 7902 | 17-Dec-80 | 800 | (null) | 20 | Y |
| 7499 | ALLEN | SALESMAN | 7698 | 20-Feb-81 | 1600 | 300 | 30 | Y |
| 7521 | WARD | SALESMAN | 7698 | 22-Feb-81 | 1250 | 500 | 40 | Y |
| 7566 | JONES | MANAGER | 7839 | 2-Apr-81 | 2975 | (null) | 20 | Y |
| 7654 | MARTIN | SALESMAN | 7698 | 28-Sep-81 | 1250 | 1400 | 50 | N |
| 7698 | BLAKE | MANAGER | 7839 | 1-May-81 | 2850 | (null) | 30 | Y |
| 7782 | CLARK | MANAGER | 7839 | 9-Jun-81 | 2450 | (null) | 10 | N |
| 7788 | SCOTT | ANALYST | 7566 | 19-Apr-87 | 3000 | (null) | 20 | Y |
| 7839 | KING | PRESIDENT | (null) | 17-Nov-81 | 5000 | (null) | 10 | N |
| 7844 | TURNER | SALESMAN | 7698 | 8-Sep-81 | 1500 | 0 | 30 | Y |
| 7876 | ADAMS | CLERK | 7788 | 23-May-87 | 1100 | (null) | 20 | N |
| 7900 | JAMES | CLERK | 7698 | 3-Dec-81 | 950 | (null) | 30 | Y |
| 7902 | FORD | ANALYST | 7566 | 3-Dec-81 | 3000 | (null) | 20 | N |
| 7934 | MILLER | CLERK | 7782 | 23-Jan-82 | 1300 | (null) | 60 | Y |
If the below queries were fired, to get the list of departments that does not have any employee, one row (department no 70) should be returned.
SELECT deptno FROM dept WHERE deptno NOT IN (SELECT deptno FROM emp);
and
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;
But surprisingly, first query will return no rows and the second will return the department no 70 because this dept does not have any employee.
Why? Its because, in the first query, each deptno from dept table will be compared with all the deptnos along with the NULL in the emp table. Remember, comparison of a value with NULL will be FALSE. So the deptno 70 comparison with NULL will be false and deptno 70 will not be returned.
January 11, 2010
Exception Handling
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:
|
v_column_a employees.column_a%TYPE; SELECT column_a –Some more statements will go here… UPDATE table_b SET … IF SQL%ROWCOUNT = 0 THEN SELECT column_c 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:
|
v_column_a employees.column_a%TYPE; SELECT column_a –Some more statements will go here… UPDATE table_b SET … IF SQL%ROWCOUNT = 0 THEN SELECT column_c
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:
|
v_column_a employees.column_a%TYPE; –Declare a variable to hold the statement no –Some more statements will go here… v_line_no := 110; IF SQL%ROWCOUNT = 0 THEN SELECT column_c v_line_no := 130; END IF;
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:
|
v_column_a employees.column_a%TYPE; –Along with the statement no, we will try to get the table/statement also. –Declare a variable to hold the statement no –Some more statements will go here… v_stmt := ‘Update stmt on table_a at 110’; IF SQL%ROWCOUNT = 0 THEN SELECT column_c v_stmt := ‘Insert into table_a at 130’; END IF;
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:
|
v_column_a employees.column_a%TYPE; –Along with the statement no, we will try to get the table/statement also. –Declare a variable to hold the statement no v_stmt := ‘Select stmt on employees where column_x = ‘ SELECT column_a –Some more statements will go here… v_stmt := ‘Update stmt on table_b where x = ‘|| v_column_a ||’ at 110′; IF SQL%ROWCOUNT = 0 THEN SELECT column_c v_stmt := ‘Insert into table_a at 130’; END IF;
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 25, 2009
New Features in Oracle 11g : Caching and Pooling
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”
December 4, 2009
New Features in Oracle 11g : SQL Operations: Pivot and Unpivot
Recently I came across the Pivot and Unpivot functions in Oracle 11g. Here is the link
Itsourteamwork : More 'WE' Less 'I' 