good guide to start learning PL/SQL.Useful for ADF developer too
Happy coding with techartifact….
Latest tip and information on Java and Oracle Fusion Middleware/Weblogic
good guide to start learning PL/SQL.Useful for ADF developer too
Happy coding with techartifact….
Exceptions
Oracle includes about 20 predefined exceptions (errors) – we can allow Oracle to raise these implicitly.
For errors that don’t fall into the predefined categories – declare in advance and allow oracle to raise an exception.
For problems that are not recognised as an error by Oracle – but still cause some difficulty within your application – declare a User Defined Error and raise it explicitly
i.e IF x >20 then RAISE …
Syntax:
EXCEPTION WHEN exception1 [OR exception2...]] THEN ... [WHEN exception3 [OR exception4...] THEN ...] [WHEN OTHERS THEN ...]
Where exception is the exception_name e.g. WHEN NO_DATA_FOUND… Only one handler is processed before leaving the block.
Trap non-predefined errors by declaring them You can also associate the error no. with a name so that you can write a specific handler.
This is done with the PRAGMA EXCEPION_INIT pragma.
PRAGMA (pseudoinstructions) indicates that an item is a ‘compiler directive’ Running this has no immediate effect but causes all subsequent references to the exception name to be interpreted as the associated Oracle Error.
–
Trapping a non-predefined Oracle server exception
DECLARE -- name for exception e_emps_remaining EXCEPTION PRAGMA_EXCEPTION_INIT ( e_emps_remaining, -2292); v_deptno dept.deptno%TYPE :=&p_deptno; BEGIN DELETE FROM dept WHERE deptno = v_deptno COMMIT; EXCEPTION WHEN e_emps_remaining THEN DBMS_OUTPUT.PUT_LINE ('Cannot remove dept '|| TO_CHAR(v_deptno) || '. Employees exist. '); END;
When an exception occurs you can identify the associated error code/message with two supplied functions SQLCODE and SQLERRM
SQLCODE – Number
SQLERRM – message
An example of using these:
DECLARE v_error_code NUMBER; v_error_message VARCHAR2(255); BEGIN ... EXCEPTION WHEN OTHERS THEN ROLLBACK; v_error_code := SQLCODE v_error_message := SQLERRM INSERT INTO t_errors VALUES ( v_error_code, v_error_message); END;
Trapping user-defined exceptions
DECLARE the exception
RAISE the exception
Handle the raised exception
e.g.
DECLARE e_invalid_product EXCEPTION BEGIN update PRODUCT SET descrip = '&prod_descr' WHERE prodid = &prodnoumber'; IF SQL%NOTFOUND THEN RAISE e_invalid_product; END IF; COMMIT; EXCEPTION WHEN e_invalid_product THEN DBMS_OUTPUT.PUT_LINE ('INVALID PROD NO'); END;
Propagation of Exception handling in sub blocks
If a sub block does not have a handler for a particular error it will propagate to the enclosing block – where it can be caught by more general exception handlers.
RAISE_APPLICATION_ERROR (error_no, message[,{TRUE|FALSE}]);
This procedure allows user defined error
messages from stored sub programs – call only from stored sub prog.
error_no = a user defined no (between -20000 and -20999)
TRUE = stack errors
FALSE = keep just last
This can either be used in the executable section of code or
the exception section
e.g.
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-2021,
‘manager not a valid employee’);
END;
Standard Exceptions, from the the STANDARD package
Oracle Exception Name |
Oracle Error |
Explanation |
DUP_VAL_ON_INDEX | ORA-00001 | You attempted to create a duplicate value in a field restricted by a unique index. |
TIMEOUT_ON_RESOURCE | ORA-00051 | A resource timed out, took too long. |
TRANSACTION_BACKED_OUT | ORA-00061 | The remote portion of a transaction has rolled back. |
INVALID_CURSOR | ORA-01001 | The cursor does not yet exist. The cursor must be OPENed before any FETCH cursor or CLOSE cursor operation. |
NOT_LOGGED_ON | ORA-01012 | You are not logged on. |
LOGIN_DENIED | ORA-01017 | Invalid username/password. |
NO_DATA_FOUND | ORA-01403 | No data was returned |
TOO_MANY_ROWS | ORA-01422 | You tried to execute a SELECT INTO statement and more than one row was returned. |
ZERO_DIVIDE | ORA-01476 | Divide by zero error. |
INVALID_NUMBER | ORA-01722 | Converting a string to a number was unsuccessful. |
STORAGE_ERROR | ORA-06500 | Out of memory. |
PROGRAM_ERROR | ORA-06501 | Generic "Contact Oracle support" message. |
VALUE_ERROR | ORA-06502 | You tried to perform an operation and there was a error on a conversion, truncation, or invalid constraining of numeric or character data. |
ROWTYPE_MISMATCH | ORA-06504 | |
CURSOR_ALREADY_OPEN | ORA-06511 | The cursor is already open. |
ACCESS_INTO_NULL | ORA-06530 | |
COLLECTION_IS_NULL | ORA-06531 |
exception_name exception
. Then, this exception must be connected to a number with the pragma exception_init(exception_name, exc-number)
. The number must be in the range -20999 through -20000 (these are negative numbers!).
create or replace package exc as some_exc exception; pragma exception_init(some_exc, -20005); procedure raise_exc; end exc; / create or replace package body exc as procedure raise_exc is begin raise some_exc; end raise_exc; end exc; / create or replace package use_exc as procedure do; end use_exc; / create or replace package body use_exc as procedure do is begin exc.raise_exc; exception when exc.some_exc then dbms_output.put_line('some_exc'); when others then dbms_output.put_line('others' ); end do; end use_exc; / set serveroutput on begin use_exc.do; end; /
The output:
some_exc
Reference -http://www.adp-gmbh.ch/ora/plsql/exception.html
Cases should be avoided –1. DO NOT use FUNCTIONS (,TO_NUMBER ,Decode,NVL, TO_CHAR) unnecessarily when a simple join would work
2. DO NOT use ‘SELECT *’ explicitly. Make sure that you select only required columns from the tables.
3. DO NOT use HAVING clause where a WHERE clause would do.
4. DO NOT use ORDER BY clause in a query unless necessarily by logic.
5. DO NOT use UNION and DISTINCT together in a single query
6. DO NOT use CURSOR LOOPS unnecessarily when a simple/single DML/SELECT can serve the purpose
7. DO NOT use DUAL table unnecessarily in programs and queries.
Cases should be followed –
1. Use UNION ALL as far as possible instead of UNION.
2. Use single/direct DML’s/SELECT’s instead of LOOP’s.
3. Use BULK COLLECT & FORALL for looping when the loop is going to get executed multiple time (take > 100 iterations as a base).
4. Use MATERIALIZED VIEWS or GLOBAL TEMPORARY TABLE for queries fetching data across dblinks
5. Use EXISTS in place of IN in the queries.
6. Care should be taken to use the LIMIT clause while using BULK operations in order to limit memory utilization. LIMIT should be set between 500-1000 and reduced or decreased as per performance behavior of the program
References – http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/tuning.htm#i48876
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/design.htm#i29012