Pages

Tuesday, March 29, 2011

Predefined PL/SQL Exceptions

ExceptionRaised when ...
ACCESS_INTO_NULLA program attempts to assign values to the attributes of an uninitialized object.
CASE_NOT_FOUNDNone of the choices in the WHEN clauses of a CASE statement is selected, and there is no ELSE clause.
COLLECTION_IS_NULLA 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_OPENA 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_INDEXA program attempts to store duplicate values in a database column that is constrained by a unique index.
INVALID_CURSORA program attempts a cursor operation that is not allowed, such as closing an unopened cursor.
INVALID_NUMBERIn 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_DENIEDA program attempts to log on to Oracle with an invalid username or password.
NO_DATA_FOUNDSELECT 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_ONA program issues a database call without being connected to Oracle.
PROGRAM_ERRORPL/SQL has an internal problem.
ROWTYPE_MISMATCHThe 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_NULLA 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_ERRORPL/SQL runs out of memory or memory has been corrupted.
SUBSCRIPT_BEYOND_COUNTA program references a nested table or varray element using an index number larger than the number of elements in the collection.
SUBSCRIPT_OUTSIDE_LIMITA program references a nested table or varray element using an index number (-1 for example) that is outside the legal range.
SYS_INVALID_ROWIDThe conversion of a character string into a universal rowid fails because the character string does not represent a valid rowid.
TIMEOUT_ON_RESOURCEA time-out occurs while Oracle is waiting for a resource.
TOO_MANY_ROWSSELECT INTO statement returns more than one row.
VALUE_ERRORAn 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_DIVIDEA program attempts to divide a number by zero.

Wednesday, March 23, 2011

Report Formating

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