Predefined PL/SQL Exceptions

Exception	Raised when ...
ACCESS_INTO_NULL	A program attempts to assign values to the attributes of an uninitialized object.
CASE_NOT_FOUND	None of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULL	A program attempts to apply collection methods other than EXISTS to an uninitialized nested table or varray, or the program attempts to assign values to the elements of an uninitialized nested table or varray.
CURSOR_ALREADY_OPEN	A program attempts to open an already open cursor. A cursor must be closed before it can be reopened. A cursor FOR loop automatically opens the cursor to which it refers, so your program cannot open that cursor inside the loop.
DUP_VAL_ON_INDEX	A program attempts to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSOR	A program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBER	In a SQL statement, the conversion of a character string into a number fails because the string does not represent a valid number. (In procedural statements, VALUE_ERROR is raised.) This exception is also raised when the LIMIT-clause expression in a bulk FETCH statement does not evaluate to a positive number.
LOGIN_DENIED	A program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUND	A SELECT INTO statement returns no rows, or your program references a deleted element in a nested table or an uninitialized element in an index-by table.Because this exception is used internally by some SQL functions to signal that they are finished, you should not rely on this exception being propagated if you raise it within a function that is called as part of a query.
NOT_LOGGED_ON	A program issues a database call without being connected to Oracle.
PROGRAM_ERROR	PL/SQL has an internal problem.
ROWTYPE_MISMATCH	The host cursor variable and PL/SQL cursor variable involved in an assignment have incompatible return types. For example, when an open host cursor variable is passed to a stored subprogram, the return types of the actual and formal parameters must be compatible.
SELF_IS_NULL	A program attempts to call a MEMBER method, but the instance of the object type has not been initialized. The built-in parameter SELF points to the object, and is always the first parameter passed to a MEMBERmethod.
STORAGE_ERROR	PL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNT	A program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMIT	A program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWID	The conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCE	A time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWS	A SELECT INTO statement returns more than one row.
VALUE_ERROR	An arithmetic, conversion, truncation, or size-constraint error occurs. For example, when your program selects a column value into a character variable, if the value is longer than the declared length of the variable, PL/SQL aborts the assignment and raises VALUE_ERROR. In procedural statements,VALUE_ERROR is raised if the conversion of a character string into a number fails. (In SQL statements,INVALID_NUMBER is raised.)
ZERO_DIVIDE	A program attempts to divide a number by zero.

Reference: http://www.stanford.edu/dept/itss/docs/oracle/10g/appdev.101/b10807/07_errs.htm

ORACLE PLSQL - Report Formatting

Changing a Column Heading

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION
SELECT LAST_NAME, SALARY, COMMISSION_PCT  FROM EMP_DETAILS_VIEW
WHERE JOB_ID='SA_MAN';
LAST NAME                 MONTHLY SALARY COMMISSION
------------------------- -------------- ----------
Russell                            14000         .4
Partners                           13500         .3

Splitting a Column Heading

COLUMN SALARY HEADING 'MONTHLY|SALARY'
COLUMN LAST_NAME HEADING 'LAST|NAME'

LAST                         MONTHLY
NAME                          SALARY COMMISSION
------------------------- ---------- ----------
Russell                        14000         .4
Partners                       13500         .3

Setting the Underline Character

SET UNDERLINE =

/

LAST                         MONTHLY

NAME                          SALARY COMMISSION

========================= ========== ==========

Russell                        14000         .4

Partners                       13500         .3

Formatting a NUMBER Column

COLUMN SALARY FORMAT $99,990

LAST                       MONTHLY

NAME                        SALARY COMMISSION

------------------------- -------- ----------

Russell                    $14,000         .4

Partners                   $13,500         .3

Formatting a Character Column

LAST  MONTHLY

NAME   SALARY COMMISSION

---- -------- ----------

Russ  $14,000         .4

ell

Resetting Column Display Attributes to their Defaults

CLEAR COLUMNS

Suppressing Duplicate Values in a Break Column

BREAK ON DEPARTMENT_ID;

BREAK ON DEPARTMENT_ID SKIP 1

SELECT DEPARTMENT_ID, LAST_NAME, SALARY  

FROM EMP_DETAILS_VIEW

WHERE SALARY > 12000 

ORDER BY DEPARTMENT_ID;

DEPARTMENT_ID LAST_NAME                     SALARY

------------- ------------------------- ----------

           20 Hartstein                      13000

           80 Russell                        14000

              Partners                       13500

           90 King                           24000

              Kochhar                        17000

Combining Spacing Techniques

BREAK ON DEPARTMENT_ID SKIP PAGE ON JOB_ID SKIP 1

TTITLE COL 35 FORMAT 9 'Page:' SQL.PNO

Page: 1
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000

Page: 2
DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

Computing and Printing Subtotals

break on DEPARTMENT_ID page  nodup on JOB_ID skip 1 nodup

COMPUTE SUM OF SALARY ON DEPARTMENT_ID




DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           20 MK_MAN     Hartstein                      13000
************* **********                           ----------
sum                                                     13000

DEPARTMENT_ID JOB_ID     LAST_NAME                     SALARY
------------- ---------- ------------------------- ----------
           80 SA_MAN     Russell                        14000
                         Partners                       13500

************* **********                           ----------
sum                                                     27500




Example

COLUMN DUMMY NOPRINT;
COMPUTE SUM OF SALARY ON DUMMY;
BREAK ON DUMMY SKIP 1;
SELECT DEPARTMENT_ID DUMMY,DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE SALARY>12000 ORDER BY DEPARTMENT_ID;



DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           20 Hartstein                      13000
                                        ----------
                                             13000

           80 Russell                        14000
           80 Partners                       13500
                                        ----------
                                             27500

           90 King                           24000
           90 Kochhar                        17000
           90 De Haan                        17000
                                        ----------
                                             58000








Computing Multiple Summary Lines on the Same Break Column



BREAK ON DEPARTMENT_ID
COMPUTE AVG SUM OF SALARY ON DEPARTMENT_ID

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW WHERE DEPARTMENT_ID = 30
ORDER BY DEPARTMENT_ID, SALARY;




DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
              Himuro                          2600
              Tobias                          2800
              Baida                           2900
              Khoo                            3100
              Raphaely                       11000
*************                           ----------
avg                                           4150
sum                                          24900








Listing and Removing COMPUTE Definitions



CLEAR BREAKS

CLEAR COMPUTES


Setting the Top and Bottom Titles and Headers and Footers

TTITLE ON
BTITLE ON


TTITLE CENTER - "ACME SALES DEPARTMENT PERSONNEL REPORT"
BTITLE CENTER "COMPANY CONFIDENTIAL"
REPHEADER PAGE CENTER 'PERFECT WIDGETS'
RIGHT 'SALES DEPARTMENT' SKIP 2
SET LINESIZE 60

ACME SALES DEPARTMENT PERSONNEL REPORT
                            PERFECT WIDGETS

                         COMPANY CONFIDENTIAL

                ACME SALES DEPARTMENT PERSONNEL REPORT
DEPARTMENT_ID LAST_NAME                     SALARY
------------- ------------------------- ----------
           30 Colmenares                      2500
           30 Himuro                          2600
           30 Tobias                          2800
           30 Baida                           2900
           30 Khoo                            3100
           30 Raphaely                       11000

                         COMPANY CONFIDENTIAL

Setting Page Dimensions

To set the number of lines between the beginning of each page and the top title, use the NEWPAGE variable of the SET command:
SET NEWPAGE number_of_lines




To set the number of lines on a page, use the PAGESIZE variable of the SET command:
SET PAGESIZE number_of_lines




You can change the line width using the LINESIZE variable of the SET command:
SET LINESIZE number_of_characters




SET PAGESIZE 66
SET NEWPAGE 0
SET LINESIZE 70



Creating a Flat File
SET NEWPAGE 0
SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF

To store the results of a query in a file—and still display them on the screen—enter the SPOOL command in the following form:

SPOOL file_name


Sending Query Results to a Printer
First, use EDIT to create the script with your operating system text editor.


EDIT EMPRPT

Next, enter the following commands into the file, using your text editor:


SPOOL TEMP
CLEAR COLUMNS
CLEAR BREAKS
CLEAR COMPUTES

COLUMN DEPARTMENT_ID HEADING DEPARTMENT
COLUMN LAST_NAME HEADING 'LAST NAME'
COLUMN SALARY HEADING 'MONTHLY SALARY' FORMAT $99,999

BREAK ON DEPARTMENT_ID SKIP 1 ON REPORT
COMPUTE SUM OF SALARY ON DEPARTMENT_ID
COMPUTE SUM OF SALARY ON REPORT

SET PAGESIZE 24
SET NEWPAGE 0
SET LINESIZE 70

TTITLE CENTER 'A C M E  W I D G E T' SKIP 2 -
LEFT 'EMPLOYEE REPORT' RIGHT 'PAGE:' -
FORMAT 999 SQL.PNO SKIP 2
BTITLE CENTER 'COMPANY CONFIDENTIAL'

SELECT DEPARTMENT_ID, LAST_NAME, SALARY
FROM EMP_DETAILS_VIEW
WHERE SALARY>12000
ORDER BY DEPARTMENT_ID;

SPOOL OFF

If you do not want to see the output on your screen, you can also add SET TERMOUT OFF to the beginning of the file and SET TERMOUT ON to the end of the file.

run the script EMPRPT:


@EMPRPT

SQL*Plus displays the output on your screen (unless you set TERMOUT to OFF), and spools it to the file TEMP:


A C M E  W I D G E T

EMPLOYEE REPORT                                              PAGE: 1

DEPARTMENT LAST NAME                 MONTHLY SALARY
---------- ------------------------- --------------
        20 Hartstein                        $13,000
**********                           --------------
sum                                         $13,000

        80 Russell                          $14,000
           Partners                         $13,500
**********                           --------------
sum                                         $27,500

        90 King                             $24,000
           Kochhar                          $17,000
           De Haan                          $17,000
**********                           --------------
sum                                         $58,000

                                     --------------
sum                                         $98,500
                         COMPANY CONFIDENTIAL




Ref: http://www.stanford.edu/dept/itss/docs/oracle/10g/server.101/b12170/ch7.htm