Showing posts with label PL/SQL. Show all posts
Showing posts with label PL/SQL. Show all posts

ON SQL functions




Building examples of using SQL functions supported by Oracle
Check the post here (in construction)

SQL Functions (11gR2)





Single-row F Aggregate F Analytic F
Object Reference F Model F OLAP F


Single-row functions

  •  Numeric functions  (26, 11gR2)
  • ABS
  • ACOS
  •  ASIN
  • ATAN
  • ATAN2
  • BITAND
  • CEIL
  • COS
  • COSH
  • EXP
  • FLOOR
  • LN
  • LOG
  • MOD
  • NANVL
  • POWER
  • REMAINDER
  • ROUND (number)
  • SIGN
  • SIN
  • SINH
  • SQRT
  • TAN
  • TANH
  • TRUNC (number)
  • WIDTH BUCKET 
  •  Character F returning character values (22, 11gR2)
  • CHR
  • CONCAT
  • INITCAP
  • LOWER
  • LPAD
  • LTRIM
  • NCHR
  • NLS_INITCAP
  • NLS_LOWER
  • NLS_UPPER
  • NLSSORT
  • REGEXP_REPLACE
  • REGEXP_SUBSTR
  • REPLACE
  • RPAD
  • RTRIM
  • SOUNDEX
  • SUBSTR
  • TRANSLATE
  • TREAT
  • TRIM
  • UPPER
  •  Character F returning number values (5, 11gR2)
  • ASCII
  • INSTR
  • LENGTH
  • REGEXP_COUNT
  • REGEXP_INSTR

  •  NLS Character F (3, 11gR2)
  • NLS_CHARSET_DECL_LEN
  • NLS_CHARSET_ID
  • NLS_CHARSET_NAME

  • Datetime F (28, 11GR2)
  •  ADD_MONTHS
  • CURRENT_DATE
  • CURRENT_TIMESTAMP
  • DBTIMEZONE
  • EXTRACT (datetime)
  • FROM_TZ
  • LAST_DAY
  • LOCALTIMESTAMP
  • MONTHS_BETWEEN
  • NEW_TIME
  • NEXT_DAY
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • ORA_DST_AFFECTED
  • ORA_DST_CONVERT
  • ORA_DST_ERROR
  • ROUND (date)
  • SESSIONTIMEZONE
  • SYS_EXTRACT_UTC
  • SYSDATE
  • SYSTIMESTAMP
  • TO_CHAR (datetime)
  • TO_DSINTERVAL
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRUNC (date)
  • TZ_OFFSET

  • General comparison F (2, 11gR2)
  • GREATEST
  • LEAST

  •  Conversion F (38, 11gR2)
  • ASCIISTR
  • BIN_TO_NUM
  • CAST
  • CHARTOROWID
  • COMPOSE
  • CONVERT
  • DECOMPOSE
  • HEXTORAW
  • NUMTODSINTERVAL
  • NUMTOYMINTERVAL
  • RAWTOHEX
  • RAWTONHEX
  • ROWIDTOCAR
  • ROWIDTONCHAR
  • SCN_TO_TIMESTAMP
  • TIMESTAMP_TO_SCN
  • TO_BINARY_FLOAT
  • TO_BINARY_DOUBLE
  • TO_BLOB
  • TO_CHAR (character)
  • TO_CHAR (datetime)
  • TO_CHAR (number)
  • TO_CLOB
  • TO_DATE
  • TO_DSINTERVAL
  • TO_LOB
  • TO_MULTI_BYTE
  • TO_NCHAR (character)
  • TO_NCHAR (datetime)
  • TO_NCHAR (number)
  • TO_NCLOB
  • TO_NUMBER
  • TO_SINGLE_BYTE
  • TO_TIMESTAMP
  • TO_TIMESTAMP_TZ
  • TO_YMINTERVAL
  • TRANSLATE ... USING
  • UNISTR

  •  Large Object F (3, 11gR2)
  • BFILENAME
  • EMPTY_BLOB
  • EMPTY_CLOB

  •  Collection F (5, 11gR2)
  • CARDINALITY
  • COLLECT
  • POWERMULTISET
  • POWERMULTISET_BY_CARDINALITY
  • SET

  •  Hierarchical F (1, 11gR2)
  • SYS_CONNECT_BY_PATH
  •  Data Mining F (12, 11gR2)
  • CLUSTER_ID
  • CLUSTER_PROBABILITY
  • CLUSTER_SET
  • FEATURE_ID
  • FEATURE_SET
  • FEATURE_VALUE
  • PREDICTION
  • PREDICTION_BOUNDS
  • PREDICTION_COST
  • PREDICTION_DETAILS
  • PREDICTION_PROBABILITY
  • PREDICTION_SET

  •  XML F (26, 11gR2)

  • Encoding and Decoding F (4, 11gR2)
  • DECODE
  • DUMP
  • ORA_HASH
  • VSIZE

  • NULL-related F (6, 11gR2)
  • COALESCE
  • LNNVL
  • NANVL
  • NULLIF
  • NVL
  • NVL2

  • Environment and Identifier F (6, 11gR2)
  • SYS_CONTEXT
  • SYS_GUID
  • SYS_TYPEID
  • UID
  • USER
  • USERENV


    Aggregate Functions
    (41, 11gR2)
    Operate on groups of rows
    Appear in SELECT, ORDER BY and HAVING

    • AVG
    • COLLECT
    • CORR
    • CORR *
    • COUNT
    • COVAR POP
    • COVAR SAMP
    • CUME DIST
    • DENSE RANK
    • FIRST
    • GROUP_ID
    • GROUPING
    • GROUPING_ID
    • LAST
    • LISTAGG
    • MAX
    • MEDIAN
    • MIN
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • STATS_BINOMIAL_TEST
    • STATS_CROSSTAB
    • STATS_F_TEST
    • STATS_KS_TEST
    • STATS_MODE
    • STATS_MW_TEST
    • STATS_ONEW_WAY_ANOVA
    • STATS_T_TEST *
    • STATS_WSR_TEST
    • STDDEV
    • STDDEV_POP
    • STDDEV_SAMP
    • SUM
    • SYS_XMLAGG
    • VAR_POP
    • VAR_SAMP
    • VARIANCE
    • XMLAGG
    • Linear Regression Functions (9, 11gR2)
    • REGR_SLOPE
    • REGR_INTERCEPT
    • REGR_COUNT
    • REGR_R2
    • REGR_AVGX
    • REGR_AVGY
    • REGR_SXX
    • REGR_SYY
    • REGR_SXY


    Analytic Functions
    (32, 11gR2)
    Commonly used on Data Warehousing environments
    Analytic functions are the last set of operations performed in a query, followed only by the ORDER BY clause
    All joins and the WHERE, GROUP BY and HAVING clauses are completed prior to the analytic functions are processed
    Good to compute cumulative, moving, centered, and reporting aggregates

    • AVG *
    • CORR *
    • COUNT *
    • COVAR_POP *
    • COVAR_SAMP *
    • CUME_DIST
    • DENSE_RANK
    • FIRST
    • FIRST_VALUE *
    • LAG
    • LAST
    • LAST_VALUE *
    • LEAD
    • LISTAGG
    • MAX *
    • MIN *
    • NTH VALUE *
    • NTILE
    • PERCENT_RANK
    • PERCENTILE_CONT
    • PERCENTILE_DISC
    • RANK
    • RATIO_TO_REPORT
    • ROW_NUMBER
    • STDDEV *
    • STDDEV_POP *
    • STDDEV_SAMP *
    • SUM *
    • VAR_POP *
    • VAR_SAMP *
    • VARIANCE *
    • Linear Regression Functions *

    • AVG



    (a) Reporting  each employee's salary and the average salary of the employees within the same department.
    
    SELECT first_name || ' ' || last_name AS emp_name,
           department_id,
           salary,
           AVG (salary) OVER (PARTITION BY department_id) AS avg_dept_sal
      FROM hr.employees;
    
    EMP_NAME       DEPARTMENT_ID   SALARY  AVG_DEPT_SAL
    ------------------  -------------  ------- ------------
    Adam Fripp     50  8200 3475.55556
    Alana Walsh     50  3100 3475.55556
    Alberto Errazuriz 80  12000 8955.88235
    Alexander Hunold 60  9000 5760
    Alexander Khoo  30  3100 4150
    Alexis Bull  50  4100 3475.55556
    Allan McEwen  80  9000 8955.88235
    Alyssa Hutton  80  8800 8955.88235
    ...
    


    (a) Lists manager_id, employee's name, hire_date, salary and the average salary of all employees that have the same manager
    
    -- note the AVG is calculated over all rows within each partition.
    -- no windowing clause is present
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (salary) OVER (PARTITION BY manager_id) AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      11100
           100 Raphaely                  07-DEC-02      11000      11100
           100 Kaufling                  01-MAY-03       7900      11100
           100 Hartstein                 17-FEB-04      13000      11100
           100 Weiss                     18-JUL-04       8000      11100
           ...
           100 Zlotkey                   29-JAN-08      10500      11100
           101 Mavris                    07-JUN-02       6500     8983.2
           ...
           101 Whalen                    17-SEP-03       4400     8983.2
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4950
           ...
    
    
    
    (b)Lists employee salary and average salary of the employees reporting to the same manager who were hired in the range just before through just after the employee.
    
    -- note the query has
    -- 1. a partition clause (partition rows by manager)
    -- 2. a windowing clause (within each partition, AVG considers only the rows immediately preceding and following each employees' row).
    
      SELECT manager_id,
             last_name,
             hire_date,
             salary,
             AVG (
                salary)
             OVER (PARTITION BY manager_id
                   ORDER BY hire_date
                   ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
                AS c_mavg
        FROM hr.employees
    ORDER BY manager_id, hire_date, salary;
    
    MANAGER_ID LAST_NAME                 HIRE_DATE     SALARY     C_MAVG
    ---------- ------------------------- --------- ---------- ----------
           100 De Haan                   13-JAN-01      17000      14000
           100 Raphaely                  07-DEC-02      11000 11966.6667
           ...
           100 Cambrault                 15-OCT-07      11000 7766.66667
           100 Mourgos                   16-NOV-07       5800       9100
           100 Zlotkey                   29-JAN-08      10500       8150
           101 Mavris                    07-JUN-02       6500       8250
           101 Baer                      07-JUN-02      10000 9502.66667
           ...
           101 Whalen                    17-SEP-03       4400       8204
           102 Hunold                    03-JAN-06       9000       9000
           103 Austin                    25-JUN-05       4800       4800
           ...
    




    • FIRST_VALUE and LAST_VALUE
    (a) For each employee, return employee salary and the name of the employee that works in the same department and has the lowest salary 
    
      SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (last_name)
             OVER (PARTITION BY department_id
                   ORDER BY salary ASC, hire_date
                   ROWS UNBOUNDED PRECEDING)
                AS lowest_sal
        FROM hr.employees
    ORDER BY department_id, last_name;
    
    DEPARTMENT_ID LAST_NAME                     SALARY LOWEST_SAL               
    ------------- ------------------------- ---------- -------------------------
               10 Whalen                          4400 Whalen                   
               20 Fay                             6000 Fay                      
               20 Hartstein                      13000 Fay                      
               30 Baida                           2900 Colmenares               
               30 Colmenares                      2500 Colmenares               
               30 Himuro                          2600 Colmenares               
               30 Khoo                            3100 Colmenares               
               30 Raphaely                       11000 Colmenares               
               30 Tobias                          2800 Colmenares               
               40 Mavris                          6500 Mavris                   
     ...
               80 Zlotkey                        10500 Kumar                    
               90 De Haan                        17000 De Haan                  
               90 King                           24000 De Haan                  
               90 Kochhar                        17000 De Haan                  
              100 Chen                            8200 Popp                     
              100 Faviet                          9000 Popp                     
              100 Greenberg                      12008 Popp                     
              100 Popp                            6900 Popp                     
              100 Sciarra                         7700 Popp                     
              100 Urman                           7800 Popp                     
    ...
    
    
    (b) For each employee of department 50, return salary of employees on the previous and following rows of the record set.
     SELECT department_id,
             last_name,
             salary,
             FIRST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between 1 preceding and current row) as previous_sal,
             LAST_VALUE (salary)
             OVER (ORDER BY salary ASC
                   ROWS between current row and 1 following) as next_sal
        FROM hr.employees
    where department_id =50;
    
    DEPARTMENT_ID LAST_NAME                     SALARY PREVIOUS_SAL   NEXT_SAL
    ------------- ------------------------- ---------- ------------ ----------
               50 Olson                           2100         2100       2200
               50 Philtanker                      2200         2100       2200
               50 Markle                          2200         2200       2400
               50 Gee                             2400         2200       2400
               50 Landry                          2400         2400       2500
               50 Patel                           2500         2400       2500
               50 Vargas                          2500         2500       2500
               50 Marlow                          2500         2500       2500
               50 Perkins                         2500         2500       2500
               50 Sullivan                        2500         2500       2600
    ...
    







    • RANK
    SELECT department_id,
           last_name,
           salary,
           commission_pct,
           RANK ()
           OVER (PARTITION BY department_id
                 ORDER BY salary DESC, commission_pct DESC)
              "Rank"
      FROM hr.employees
     WHERE department_id = 80;
    


    Object Reference Functions
    • DEREF
    • MAKE_REF
    • REF
    • REFTOHEX
    • VALUE

    Model Functions
    • CV
    • ITERATION_NUMBER
    • PRESENTNNV
    • PRESENTV
    • PREVIOUS

    OLAP Functions
    • CUBE_TABLE

    Data Cartridge Functions
    • DATAOBJ_TO_PARTITION


    User-Defined Functions















































    (6) Control Structures: CONTINUE statement





    CONTINUE statement
    • CONTINUE jumps out of the current LOOP interaction and STARTS THE NEXT ONE.
    • Can be used on its own or as part of a CONTINUE WHEN

    set serveroutput on 
    declare 
     l_number number := 0;
    begin
     for i in 1 .. 10 LOOP
                                              -- code before CONTINUE: executed
       dbms_output.put_line('Before Continue: Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
       CONTINUE WHEN MOD(i,2) = 0;
                                              -- If WHEN condition TRUE, code after continue: Jumped.
       dbms_output.put_line('Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
       l_number := l_number +1;
     end loop;
     
     dbms_output.put_line('CONTINUE WHEN:'|| l_number);
    end;
    

    Alternativelly:
    set serveroutput on 
    declare 
     l_number number := 0;
    begin
     for i in 1 .. 10 LOOP
       IF mod(i,2) = 0 THEN 
         CONTINUE;
       END IF;
       dbms_output.put_line('Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
       l_number := l_number +1;
     end loop;
    

    • Before the CONTINUE statement, the same behavior could be implemented with IF, GOTO or EXCEPTIONS..:
    -- Implementing the same behavior using Exception:
    set serveroutput on 
    declare 
     e_continue exception;
     ln number :=0;
    begin 
     for i in 1 .. 10 loop
      begin
       if mod(i,2) != 0 then
         raise e_continue;
       end if;
        dbms_output.put_line('Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
        ln := ln + 1;   
      exception
       when e_continue then
         null;
      end;
     end loop;
     dbms_output.put_line('EXCEPTION: ' || ln);
    

    set serveroutput on 
    declare 
     ln number :=0;
    begin
     ln := 0;
     FOR i IN 1 .. 10 LOOP
        IF MOD(i,2) != 0 THEN
          dbms_output.put_line('Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
          ln := ln + 1;   
        END IF;
      END LOOP;
      dbms_output.put_line('IF       : ' || ln);
    end;
    

    set serveroutput on 
    declare 
     ln number :=0;
    begin
      ln := 0;
      FOR i IN 1 .. 10 LOOP
        IF MOD(i,2) = 0 THEN
          GOTO label_continue;
        END IF;
        dbms_output.put_line('Iteracao # '|| i ||'. MOD('||i||', 2) = '|| mod(i,2));
        ln := ln + 1;   
    
        << label_continue >>
        NULL;
      END LOOP;
      dbms_output.put_line('GOTO     : ' || ln);
    end;
    

    Exception Handling: Nested Blocks


    Question: Can you use the same name for User-Defined Exceptions in nested blocks?
    A: Yes. But, oracle treat them as DIFFERENT exceptions.
    If exception e1 is raised and NOT CATCHED in the inner block, it WILL NOT be caught in the outer block EVEN if there is an exception handler with that name.


    [ Oracle PL/SQL ]

    Case 1: Exception caught in the internal block
    set serveroutput on
    DECLARE                -- outer block
     e1 exception;
     a integer;
    BEGIN
      a :=2; 
      declare              -- inner block
        e1 exception;
        b integer;
      begin
        b := 2;
        raise e1;           -- exception e1 raised.
      exception
        when others then 
           dbms_output.put_line('error1 caught: inner block');
           dbms_output.put_line(sqlerrm);
      end;
      a :=3;
      raise e1;              -- exception e1 raised.
    EXCEPTION
     when e1 then 
       dbms_output.put_line('error1 caught: outer block');
    END;
    /
    anonymous block completed
    error1 caught: inner block
    User-Defined Exception
    error1 caught: outer block
    


    Case 2: Exception unhadled in the internal block is NOT caught in the external one
    set serveroutput on
    DECLARE                         -- outer block
     e1 exception;
     a integer;
    BEGIN
      a :=2; 
      declare                -- inner block
        e1 exception;
        b integer;
      begin
        b := 2;
        raise e1;                   -- exception e1 raised
      exception
        when no_data_found then 
           dbms_output.put_line('error1 caught: inner block');
           dbms_output.put_line(sqlerrm);
      end;
      a :=3;
      raise e1;
    EXCEPTION
     when e1 then 
       dbms_output.put_line('error1 caught: outer block');
    END;
    /
    
    Error report:
    ORA-06510: PL/SQL: unhandled user-defined exception
    ORA-06512: at line 11
    06510. 00000 -  "PL/SQL: unhandled user-defined exception"
    *Cause:    A user-defined exception was raised by PL/SQL code, but
               not handled.
    *Action:   Fix the problem causing the exception or write an exception
               handler for this condition. Or you may need to contact your
               application administrator or DBA.
    

    Native Dynamic SQL x DBMS_SQL




    With Oracle 11g:
    • Native dynamic SQL and DBMS_SQL package now support statments larger than 32Kb.
    • EXECUTE IMMEDIATE, OPEN-FOR and DBMS_SQL.PARSE accept SQL statements in the form of CLOBs.

    • DBMS_SQL.TO_REFCURSOR converts a DBMS_SQL cursor ID into a REF CURSOR.
    • DBMS_SQL.to_cursor_number converts a REF CURSOR into a DBMS_SQL cursor ID.


    DBMS_SQL.TO_REFCURSOR: From Cursor ID => to REF CURSOR.
    set serveroutput on 
    declare 
     lcursor number;                       -- for DBMS_SQL Cursor ID
     lreturn number;                       -- for DBMS_SQL Cursor ID
     
     lref_cursor sys_refcursor;            -- for REF CURSOR
     type        t_emptab is table of employees%rowtype;
     lemp_tab    t_emptab;
    
    begin
      lcursor := dbms_sql.open_cursor;
      dbms_sql.parse(lcursor, 
                    'Select * from employees',
                     DBMS_SQL.NATIVE);
      lreturn := dbms_sql.execute(lcursor);
      
                                            -- convert from dbms_sql Cursor ID to a REF CURSOR
      lref_cursor := dbms_sql.to_refcursor(lcursor);
      fetch lref_cursor bulk collect into lemp_tab;
      dbms_output.put_line('Employee count: '||lemp_tab.count);
      close lref_cursor;
    end;
    /
    anonymous block completed
    Elapsed: 00:00:00.050
    Employee count: 107
    

    DBMS_SQL.to_cursor_number: from REF CURSOR => to DBMS_SQL cursor ID.
    declare 
      lref_cursor sys_refcursor;
      lcursor number;
      lcount  number := 0;
    begin 
      OPEN lref_cursor FOR 'select * from employees';
                                              -- convert from REF CURSOR to a dbms_sql Cursor ID
      lcursor := dbms_sql.to_cursor_number(lref_cursor);
    
      while dbms_sql.fetch_rows(lcursor) > 0 LOOP
        lcount := lcount + 1;
      end loop;
      dbms_output.put_line('Employee count: '|| lcount);
      dbms_sql.close_cursor(lcursor);
    end;
    /
    anonymous block completed
    Elapsed: 00:00:00.024
    Employee count: 107
    

    Native Dynamic SQL: OPEN FOR, FETCH and CLOSE







    Native Dynamic SQL: OPEN FOR, FETCH and CLOSE

    Using dynamic SQL with OPEN FOR, FETCH and CLOSE:
    set serveroutput on
    declare 
     -- define a type cursor. 
     -- declare a variable of type cursor. This variable will 
     -- execute the dynamic SQL stmt later (OPEN, LOOP-FETCH, CLOSE).
     type empcurtype is ref cursor;
     v_emp_c    empcurtype;
     emp_rec    employees%rowtype;
     v_stmt_str varchar2(200);
     v_e_job    employees.job_id%type;
    begin
      -- dynamic SQL stmt. Note the placeholder (:j) for 
      -- a bind variable.
      v_stmt_str := 'SELECT * FROM employees '|| 
                    'WHERE job_id = :j';
      
      -- Open the cursor. List the bind variables
      -- with the USING clause. Placeholders, in this
      -- case (single SQL stmt), are associated 
      -- BY POSITION, and NOT by name.
      OPEN v_emp_c FOR v_stmt_str USING 'IT_PROG';
      LOOP
        FETCH v_emp_c INTO emp_rec;
        exit when v_emp_c%notfound;
        dbms_output.put_line('Empid: '|| emp_rec.employee_id || 
                       '--'||emp_rec.first_name || ' ' || emp_rec.last_name );
      END LOOP;
      CLOSE v_emp_c;
    end;
    
    nonymous block completed
    Empid: 103--Alexander Hunold
    Empid: 104--Bruce Ernst
    Empid: 105--David Austin
    Empid: 106--Valli Pataballa
    Empid: 107--Diana Lorentz
    

    (cont..)

    (19-20) PL/SQL Compiler


    PL/SQL Compiler
    • Several initialization parameters (compiler parameters) affect the compilation of PL/SQL units.
    • The values at the time of compilation of the PLSQL_CCFLAGS, PLSQL_CODE_TYPE, PLSQL_DEBUG, PLSQL_OPTIMIZE_LEVEL, PLSQL_WARNINGS, and NLS_LENGTH_SEMANTICS initialization parameters are stored with the unit's metadata.
    • ALL_PLSQL_OBJECT_SETTINGS view: Keeps information about the settings of these parameters.


    PLSCOPE_SETTINGS Controls compile-time collection, cross-reference, and storage of PL/SQL source text identifier data.
    PLSCOPE_SETTINGS = IDENTIFIERS:{ NONE | ALL }
    PLSQL_CCFLAGS Enables you to control conditional compilation of each PL/SQL unit independently.
    PLQL_CODE_TYPE Defines compilation mode for PL/SQL libary units.
    PLSQL_CODE_TYPE = { INTERPRETED | NATIVE }
    PLSQL_OPTIMIZE_LEVEL The higher the setting of this parameter, the more effort the compiler makes to optimize PL/SQL library units.
    Range: {0,3}
    PLSQL_DEBUG Specifies whether to compile PL/SQL units for debugging.
    Deprecated on 11g.
    To compile PL/SQL units for debugging, specify PLSQL_OPTIMIZE_LEVEL=1.
    PLSQL_NATIVE_LIBRARY_DIR
    PLSQL_NATIVE_LIBRARY_SUBDIR_COUNT
    Related to Native Compilation.Deprecated on 11g.
    PLSQL_WARNINGS Enables/disables the reporting of warning messages by the PL/SQL compiler.
    Compile-time warning categories: [ SEVERE | PERFORMANCE | INFORMATIONAL ]
    NLS_LENGTH_SEMANTICS




    PL/SQL Optimizer
    Prior to Oracle Database 10g Release 1 (10.1), the PL/SQL compiler translated your source text to system code without applying many changes to improve performance. Now, PL/SQL uses an optimizer that can rearrange code for better performance.

    The optimizer is enabled by default. In rare cases, if the overhead of the optimizer makes compilation of very large applications too slow, you can lower the optimization by setting the compilation parameter PLSQL_OPTIMIZE_LEVEL=1 instead of its default value 2. In even rarer cases, PL/SQL might raise an exception earlier than expected or not at all. Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged.



    On 10g R1

    PLSQL_OPTIMIZE_LEVEL init parameter: Controls Global optimization of Pl/SQL Code. Default value is often good enough.

    Native compilation: fewer initialization parameters to set and less compiler configuration. Object code is stored in the database.
    • PLSQL_NATIVE_LIBRARY_DIR: Only parameter required.(Deprecated on 11g)
    • PLSQL_CODE_TYPE: turn native compilation ON|OFF.
    • PLSQL_COMPILER_FLAGS: Deprecated.
    • $ORACLE_HOME/plsql/spnc_commands file contains the commands and options for compiling and linking
    • dbmsupgnv.sql: recompiles all the PL/SQL modules in a database as NATIVE.
    • dbmsupgin.sql: recompiles all the PL/SQL modules in a database as INTERPRETED.

    Compile-Time Warnings
    • May be issued when you compile subprograms that produce ambiguous results or use inefficient constructs.
    • Enable | Disable warnigns: Use PLSQL_WARNINGS init parameter or DBMS_WARNING package.


    On 10g R2
    • Conditional Compilation
      • Enables selective inclusion of code depending on the values of the conditions evaluated during compilation. For example:
      • You can determine which PL/SQL features in a PL/SQL application are used for specific database releases.
      • Also useful when you want to execute debugging procedures in a development environment, but want to turn off the debugging routines in a production environment.
    • Dynamic Wrap
      • DBMS_DDL wrap subprograms obfuscate (hide) dynamically generated PL/SQL code units.

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


    Pragmas
    A pragma is an instruction to the compiler that it processes at compile time


    Pragma Autonomous_transaction
    • Marks a routine [(not-nested) anonymous block, subprogram, method (of ADT), Trigger] as autonomous; that is, independent of the main transaction
    • Autonomous transactions do SQL operations and commit or rollback, without committing or rolling back the main transaction.
    • When you enter the executable section of an autonomous routine, the main transaction suspends. When you exit the routine, the main transaction resumes.
    • If you try to exit an active autonomous transaction without committing or rolling back, the database raises an exception.
    • To exit normally, you must explicitly commit or roll back all autonomous transactions.

    (1) Autonomous PL/SQL Block
    -- create table emp as bellow:
    SQL> create table emp
     (name varchar2(20), jobid varchar2(10));
    
    -- insert four initial rows and commit inserts.
    SQL> insert into emp values('John', 'Accounting');
    SQL> insert into emp values('Paul', 'Accounting');
    SQL> insert into emp values('George', 'Ads');
    SQL> insert into emp values('Ringo', 'Ads');
    SQL> commit;
    
    -- check the inserted rows.
    SQL> select * from emp;
    
    NAME                 JOBID
    -------------------- ----------
    John                 Accounting
    Paul                 Accounting
    George               Ads
    Ringo                Ads
    
    -- insert a 5th row. and do not commit.
    -- Check the inserted row.
    SQL> insert into emp values('Ghost', 'Ads');
    1 row created.
    
    SQL> select * from emp;
    NAME                 JOBID
    -------------------- ----------
    John                 Accounting
    Paul                 Accounting
    George               Ads
    Ringo                Ads
    Ghost                Ads
    
    -- Now add the autonomous block.
    
    SQL> set serveroutput on
    SQL> declare
      2     pragma autonomous_transaction;
      3    numemp number;
      4  begin
      5    select count(*) into numemp from emp;
      6    dbms_output.put_line('num emps before autonomous insert: ' || numemp);
      7    insert into emp values('Autonomous', 'New');
      8    dbms_output.put_line('Inserted: Autonomous');
      9    select count(*) into numemp from emp;
     10    dbms_output.put_line('num emps after autonomous insert: ' || numemp);
     11    commit;
     12  end;
     13  /
    num emps before autonomous insert: 4
    Inserted: Autonomous
    num emps after autonomous insert: 5
    
    PL/SQL procedure successfully completed.
    
    -- Now outside the autonomous block, check the existing 
    -- rows and rollback the insertion of "Ghost"
    SQL> select * from emp;
    NAME                 JOBID
    -------------------- ----------
    John                 Accounting
    Paul                 Accounting
    George               Ads
    Ringo                Ads
    Ghost                Ads
    Autonomous           New
    
    SQL> rollback;
    
    Rollback complete.
    
    -- Check that the row for "Ghost" was rolled back, but the 
    -- value inserted in the autonomous transaction remained.
    SQL> select * from emp;
    
    NAME                 JOBID
    -------------------- ----------
    John                 Accounting
    Paul                 Accounting
    George               Ads
    Ringo                Ads
    Autonomous           New
    SQL>
    

    (2) Autonomous standalone procedure
    In the example above, the anonymous block could be rewritten as an autonomous procedure
    as below:
    CREATE OR REPLACE PROCEDURE ins_emp
      AS
        pragma autonomous_transaction;
        numemp number;
    BEGIN
        select count(*) into numemp from emp;
        dbms_output.put_line('num emps before autonomous insert: ' || numemp);
        insert into emp values('Autonomous', 'New');
        dbms_output.put_line('Inserted: Autonomous');
        select count(*) into numemp from emp;
        dbms_output.put_line('num emps after autonomous insert: ' || numemp);
        commit;
    END ins_emp;
    

    (3) Autonomous Triggers
    A trigger must be autonomous to run TCL or DDL statements.
    To run DDL statements, the trigger must use native dynamic SQL.






    Associates a user-defined exception name with an error code.
    Can appear only in the same declarative part as its associated exception, anywhere after the exception declaration.
    DECLARE
        past_due  EXCEPTION;                       -- declare exception
        PRAGMA EXCEPTION_INIT (past_due, -20000);  -- assign error code to exception
      BEGIN
        ...
    
    Check herefor an example.

    Pragma Inline (check here)
    • Specifies whether a subprogram invocation (or statement) is to be inlined.
    • Inlining replaces a subprogram invocation with a copy of the invoked subprogram (if the invoked and invoking subprograms are in the same program unit).
    • To allow subprogram inlining: PLSQL_OPTIMIZE_LEVEL compilation parameter := [2 (default) | 3 ]
    • If a particular subprogram is inlined, performance almost always improves. However, because the compiler inlines subprograms early in the optimization process, it is possible for subprogram inlining to preclude later, more powerful optimizations.

    Pragma Restrict_References
    • Asserts that a user-defined subprogram does not read or write database tables or package variables.
    • Can appear only in a package specification or ADT specification. Typically, this pragma is specified for functions.
    • Subprograms that read or write database tables or package variables are difficult to optimize, because any invocation of the subprogram might produce different results or encounter errors.
    • .





    Package...memory usage
    ...IS NOT
    SERIALY_REUSABLE
    Package state stored in the user global area (UGA) for each user.
    The amount of UGA memory needed increases linearly with the number of users.
    Package state can persist for the life of a session, locking UGA memory.
    ...IS
    SERIALY_REUSABLE
    Package state stored in a work area in a small pool in the system global area (SGA).
    Package state persists only for the life of a server call, after which the work area returns to the pool. If a subsequent server call references the package, then Oracle Database reuses an instantiation from the pool. Reusing an instantiation re-initializes it; therefore, changes made to the package state in previous server calls are invisible.

    CREATE OR REPLACE PACKAGE pkg IS
      PRAGMA SERIALLY_REUSABLE;
      n number:= 5;
    END;
    /
     
    CREATE OR REPLACE PACKAGE BODY pkg IS
      PRAGMA SERIALLY_REUSABLE;
    BEGIN
      n := 5;
    END;
    /

    • Allow you to better manage memory for scalability.
    • Specifies that the package state is needed for only one call to the server, after which the storage for the package variables can be reused, reducing the memory overhead for long-running sessions.
    • Appropriate for packages that declare large temporary work areas that are used once in the same session.

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

    Conditional compilation
    • The Conditional compilation (CC) feature and related PL/SQL packages are available for the 10g Release 1 (10.1.0.4) and later.
    • CC allows constructs — with formally defined syntax and semantics — to be used to mark up text so that a preprocessor can deterministically derive the text that will be submitted to the compiler proper.
    • CC uses (a) selection directives($) (similar to IF stmt), (b) inquiry directives($$), and (c) error directives.
    • CC uses preprocessor control tokens($) to mark code that is processed before the PL/SQL unit is compiled: $IF, $THEN, $ELSE, $ELSIF, $ERROR
    • Oracle’s Applications Division provided the use case that motivated the introduction of PL/SQL conditional compilation. They wanted their code to be able to span different releases of Oracle Database, using the latest features in the latest release and using a fallback in earlier releases.(see more here)

    Uses of Conditional Compilation (CC) {see detailed discussion in this white paper}
    • Allowing self-tracing code to be turned on during development and to be turned off when the code goes live.
    • Allowing alternative code fragments, each appropriate for the peculiarities of a particular operating system and inappropriate or illegal for other operating systems, to coexist in the same source text so the correct fragment can be selected for compilation according to the circumstances.
    • Newer Oracle releases introduce new features with new syntax and programs that take advantage of these are illegal in earlier releases. PL/SQL conditional compilation supports this use in an elegant and powerful way.
    • A developer often realizes that more than one approach to the design of a subprogram will result in its correct behavior; sometimes the alternative approaches result in source code versions which are textually largely the same but which differ critically in small areas distributed fairly evenly thought the source. PL/SQL conditional compilation allows all the approaches to be coded in a single source text — while they are being evaluated — and thereby eliminates the risk of carelessly introduced unintended differences.
    • Modular delivery of extra functionality can be implemented by optional PL/SQL compilation units which are installed according to what the customer has licensed. PL/SQL’s dependency model prevents the core part of the application referring statically to optional components that are not installed. However, the core part of the application should not need reinstallation in order to accommodate the installation of a new optional component. This has forced the use of dynamic invocation — which has some drawbacks. Conditional compilation allows a new approach.
    Using selection directives ($IF, $ELSE) and error directives ($ERROR)
    set serveroutput on
    begin
      $IF dbms_db_version.ver_le_10_1 $THEN
         $ERROR  'unsupported database release' $END
      $ELSE
         dbms_output.put_line (
            'release ' || dbms_db_version.version || '.' || 
            dbms_db_version.release || ' is supported.'
         ); 
         -- since its a newer release, use a new commit syntax
        -- supported in 10.2
        commit write immediate nowait;
      $END
    end;
    /
    
    anonymous block completed
    release 11.2 is supported.
    

    The package DBMS_DB_VERSION provide static constants:
    • dbms_db_version.version
    • dbms_db_version.release
    • dbms_db_version.ver_le_v
    • dbms_db_version.ver_le_v_r



    Inquiry directives($$)
    • $$PLSQL_LINE: the number of the source line on which the directive appears in the current PL/SQL unit.
    • $$PLSQL_UNIT: the name of the current PL/SQL unit.

    • You can assign values to inquiry directives with the PLSQL_CCFLAGS compilation parameter
      • ALTER SESSION set PLSQL_CCFLAGS = 'flag:True, val:5'
    • $$plsql_compilation_parameter: a PL/SQL compilation parameter

    Displaying Values of PL/SQL Compilation Parameters
    BEGIN
      DBMS_OUTPUT.PUT_LINE('$$PLSCOPE_SETTINGS = '     || $$PLSCOPE_SETTINGS);
      DBMS_OUTPUT.PUT_LINE('$$PLSQL_CCFLAGS = '        || $$PLSQL_CCFLAGS);
      DBMS_OUTPUT.PUT_LINE('$$PLSQL_CODE_TYPE = '      || $$PLSQL_CODE_TYPE);
      DBMS_OUTPUT.PUT_LINE('$$PLSQL_OPTIMIZE_LEVEL = ' || $$PLSQL_OPTIMIZE_LEVEL);
      DBMS_OUTPUT.PUT_LINE('$$PLSQL_WARNINGS = '       || $$PLSQL_WARNINGS);
      DBMS_OUTPUT.PUT_LINE('$$NLS_LENGTH_SEMANTICS = ' || $$NLS_LENGTH_SEMANTICS);
    END;
    /
    
    anonymous block completed
    $$PLSCOPE_SETTINGS = 
    $$PLSQL_CCFLAGS = 
    $$PLSQL_CODE_TYPE = INTERPRETED
    $$PLSQL_OPTIMIZE_LEVEL = 0
    $$PLSQL_WARNINGS = DISABLE:ALL
    $$NLS_LENGTH_SEMANTICS = BYTE
    

    Using inquiry directives (example extracted from here)
    -- USE PACKAGE SPEC and BODY to define and declare a USER-DEFINED subtype.
    
    -- Package specification: Use selection directive ($IF) to test for the 
    -- version of the database.
    -- If version < 10g, define subtype as NUMBER.
    -- If version >= 10g, define subtype as BINARY_DOUBLE
    create or replace package my_pkg AS
      subtype my_real IS
        $IF dbms_db_version.version < 10 $THEN
          number;
        $ELSE
          binary_double;
        $END
      
      my_pi my_real;
      my_e  my_real;
    end my_pkg;
    /
    
    -- Package body: test for db version. Assign values
    -- for the subtype variable accordingly. 
    create or replace package body my_pkg AS
    begin
    $IF dbms_db_version.version < 10 $THEN 
         my_pi := 3.14159265358979323846264338327950288420;
        my_e  := 2.71828182845904523536028747135266249775;
      $ELSE
        my_pi := 3.14159265358979323846264338327950288420d;
        my_e  := 2.71828182845904523536028747135266249775d;
      $END
    END my_pkg;
    / 
    
    -- Use the subtype defined in the package on a standalone procedure.
    
    -- Uses inquiry directive($$my_debug) to decide whether or not 
    -- to run some code. If $$my_debug = TRUE, run the debug code that 
    -- check for the datatype of the subtype my_real.
    create or replace procedure circle_area (radius my_pkg.my_real) IS
      my_area       my_pkg.my_real;
      my_data_type  varchar2(30);
    begin
      my_area := my_pkg.my_pi * (radius**2);
      
      dbms_output.put_line
        ('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
      
      $IF $$my_debug $THEN
        select data_type into my_data_type
        from user_arguments
        where object_name = 'CIRCLE_AREA'
        and ARGUMENT_NAME = 'RADIUS';
        
        dbms_output.put_line
         ('Data type of the RADIUS argument is: '|| my_data_type);
      $END
    end circle_area;
    /
    
    -- Run procedure circle_area 
    (1) Set $$my_debug: FALSE
    SQL> alter session set plsql_ccflags = 'my_debug:FALSE';
    Session SET altered.
    
    SQL> set serveroutput on 
    SQL> exec circle_area(2);
    Radius: 2.0E+000 Area: 1.2566370614359172E+001
    PL/SQL procedure successfully completed.
    
    (1) Set $$my_debug: TRUE
    SQL> alter session set plsql_ccflags = 'my_debug:TRUE';
    Session SET altered.
    
    -- You need to recompile the procedure circle_area so it can 
    -- see the change in the session 
    SQL> alter procedure circle_area compile;
    Procedure altered.
    
    SQL> set serveroutput on 
    SQL> exec circle_area(2);
    Radius: 2.0E+000 Area: 1.2566370614359172E+001
    Data type of the RADIUS argument is: BINARY_DOUBLE
    
    PL/SQL procedure successfully completed.
    

    Using DBMS_PREPROCESSOR to print source text
    • You can use the dbms_preprocessor package to print the source text executed after
      the conditional compilation directives were all processed.
    • For the procedure circle_area above, you can retrieve the source code processed when the
      debug directive ($$my_debug) is set to TRUE.
    • Subprograms
      • DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE
      • DBMS_PREPROCESSOR.GET_POST_PROCESSED_SOURCE (overloaded (3))


    SQL> call dbms_preprocessor.print_post_processed_source
      2                                             ('PROCEDURE', 'DEV2', 'CIRCLE_AREA');
    procedure circle_area (radius my_pkg.my_real) IS
    my_area       my_pkg.my_real;
    my_data_type  varchar2(30);
    begin
    my_area := my_pkg.my_pi * (radius**2);
    dbms_output.put_line
    ('Radius: '|| to_char(radius) || ' Area: '|| to_char(my_area));
    select data_type into my_data_type
    from user_arguments
    where object_name = 'CIRCLE_AREA'
    and ARGUMENT_NAME = 'RADIUS';
    dbms_output.put_line
    ('Data type of the RADIUS argument is: '|| my_data_type);
    end circle_area;
    
    Call completed.
    


    CC Restrictions:
    • You cannot have a CC directive within a CREATE TYPE statement. (Why? Attributes of a TYPE will determine the physical structure of dependent tables.)
    • Package Spec, Package body, type body, procedure/function w/o parameters: first conditional compilation directive cannot appear before the keyword IS or AS.
    • subprogram with at least one formal parameter: the first conditional compilation directive cannot appear before the left parenthesis that follows the subprogram name.
    • Trigger or an anonymous block, the first conditional compilation directive cannot appear before the keyword DECLARE or BEGIN, whichever comes first.



    On 11g R2: Compiling PL/SQL Units for Native Execution

    • PL/SQL units can be compiled into native code (processor-dependent system code), which is stored in the SYSTEM tablespace.
    • Any PL/SQL unit of any type can be natively compiled, including Oracle supplied ones.
    • Natively compiled program units work in all server environments.
    • Greatest performance gains: for computation-intensive procedural operations. (i.e. Data warehouse and applications with extensive server-side transformations of data for display).
    • Least performance gains: for PL/SQL subprograms that spend most of their time running SQL.
    • The PLSQL_CODE_TYPE compilation parameter determines whether PL/SQL code is natively compiled or interpreted.
      • ALTER PROCEDURE my_proc COMPILE PLSQL_CODE_TYPE=NATIVE REUSE SETTINGS;
    • Run this query to determine how many objects are compiled NATIVE and INTERPRETED:
    SELECT TYPE, PLSQL_CODE_TYPE, COUNT(*)
    FROM DBA_PLSQL_OBJECT_SETTINGS
    WHERE PLSQL_CODE_TYPE IS NOT NULL
    GROUP BY TYPE, PLSQL_CODE_TYPE
    ORDER BY TYPE, PLSQL_CODE_TYPE;
    


    Obfuscation and wrapping













































    (12) Packages


    About Packages:
    • Schema object that groups logically related PL/SQL types, variables, constants, subprograms, cursors, and exceptions.

    Package specification:
    • Declares the public items that can be referenced from outside the package. Works as an (API).
    • If the public items include cursors or subprograms, then the package must also have a body.
    • The scope of a public item is the schema of the package. A public item is visible everywhere in the schema.
    • AUTHID clause determines:
      1. whether subprograms and cursors in the package run with the definer (the default) or invoker privileges.
      2. whether unqualified references to schema objects are resolved in the schema of the definer or invoker.

    Package Body:
    • Package body is optional, unless package specification declares cursors or subprograms.
    • The package body and package specification must be in the same schema.
    • Can also declare and define private items that cannot be referenced from outside the package
    • Can have an initialization part, whose statements initialize variables and do other one-time setup steps.
    • In either the package specification or package body, you can map a package subprogram to an external Java or C subprogram
    • For this, you use a call specification, which maps the external subprogram name, parameter types, and return type to their SQL counterparts.

    Why use packages?
    • Modularity:
      • encapsulate logically related types, variables, constants, subprograms, cursors, and exceptions.
      • You can modify package objects without recompiling dependent schema objects
      • Let you OVERLOAD subprograms.
    • Easier Application design
      • Can contain global variables and cursors that are available to all subprograms in the package.
      • Packages increase your namespace -- "you get pkg.proc instead of 5,000 little procedures with meaningless names, you can have 500 packages with good module names, each with 10 procedures that have meaningful names (that might be the same as the name of a procedure in another package). (ask tom)
    • good coding practices
      • Allow many small procedures instead a few large ones.
      • Packages maintain a state
      • Packages allow you to create types
    • Information Hiding
      • You may have 20 procedures in the body, but choose to make only 10 of them public in the specificaiton
    • Added functionality: Package public variables and cursors can persist for the life of a session.
    • Better performance:
      After first time you invoke a package subprogram, Oracle Database loads the package as needed into memory. Packages ARE NOT entirely loaded immediately.
    • Better Security control and privilege management: more efficient grant privileges

    What are appropriate public items?
    • types, variables, constants, subprograms, cursors, and exceptions
    • Associative arrays type of standalone subprogram parameters: Since you cannot declare an AA type at schema level.
    • variables that must remain available between subprogram invocations in the same session.
    • subprograms that read and write public variables.
    • subprograms that invoke each other.
    • overloaded subprograms

    Note: You cannot reference remote package public variables, even indirectly.


    Package instantiation and initialization:
    • When a session references a package item, Oracle Database instantiates the package for that session.
    • Every session that references a package has its own instantiation of that package.
    • Initialization includes whichever of the following are applicable:
      • Assigning initial values to public constants
      • Assigning initial values to public variables whose declarations specify them.
      • Executing the initialization part of the package body

    Package State

    • Package state: the values of the variables, constants, and cursors that a package declares (in either its specification or body)
    • If a PL/SQL package declares at least one variable, constant, or cursor, then the package is stateful;
    • otherwise, it is stateless.
    • Each session that references a package item has its own instantiation of that package.
    • If the package is stateful, the instantiation includes its state.
    • The package state persists for the life of a session, except in these situations:
      • package is SERIALLY_REUSABLE
      • package body recompiled

    SERIALLY_REUSABLE
    • The package state is stored in a work area in a small pool in the system global area (SGA).
    • If a package is NOT SERIALLY_REUSABLE:
      • its package state is stored in the user global area (UGA) for each user.
      • Limit scalability: the amount of UGA memory needed increases linearly with the number of users.
      • Potential high usage of memory: The package state can persist for the life of a session, locking UGA memory until the session ends.
    • For a SERIALLY_REUSABLE package, the work unit is a server call.
    • You must use its public variables only within the work unit.
    • If you depend on the value of a public variable that was set in a previous work unit, your program can fail. PL/SQL cannot check for such cases.


    About Packages: Initialization and variables.
    (1) The package body can have an executable section.
    (2) This section is executed when the first reference to a package object
    is made
    . In the example, when you print the value of variable 'value'
    in an anonymous block.

    (3) The package body can have an initialization part, whose statements
    initialize public variables and do other one-time setup steps.
    The initialization part runs only the first time the package is referenced.
    The initialization part can include an exception handler.


    -- (1) create package ..
    create or replace package my_pack 
    as
     value number := 100;
     const constant number := 999;
     procedure update_value (pval number);
    end my_pack;
    /
    

    -- (2) .. and package body
    create or replace package body my_pack
    as 
      procedure update_value (pval number) 
      is
      begin
        my_pack.value := pval;
        dbms_output.put_line('my_pack.value updated to: '|| my_pack.value);
        dbms_output.put_line('my_pack.const: '|| my_pack.const);    
      end;
    begin         --- executable section of package. Runs once. 
      dbms_output.put_line('initial value of my_pack.value: '|| my_pack.value);
      dbms_output.put_line('initial value of value: '|| value);
      dbms_output.put_line('initial value of my_pack.const: '|| my_pack.const);
      my_pack.value := 300;
      dbms_output.put_line('New value of my_pack.value: '|| my_pack.value);  
    end my_pack;
    /
    

    --(3) The initialization code is executed after the first call to a package object..
    set serveroutput on
    declare
      x number;
    begin 
      x := my_pack.const;            -- First call to a package object. 
      dbms_output.put_line('x is: '|| x);
      x := my_pack.value;
     dbms_output.put_line('x is: '|| x);
    end;
    /
    anonymous block completed
    initial value of my_pack.value: 100
    initial value of value: 100
    initial value of my_pack.const: 999
    New value of my_pack.value: 300
    x is: 999
    x is: 300
    

    --(4) Run the anonymous block again. The executable section in the package body IS NOT executed again.
    anonymous block completed
    x is: 999
    x is: 300
    

    --(5) If you recompile the package, the initialization code is executed again, in the first call to a package object.
    SQL> alter package my_pack compile;
    package MY_PACK altered.
    
    set serveroutput on
    declare
     x number;
    begin 
     x := my_pack.const;
     dbms_output.put_line('x is: '|| x);
     
      x := my_pack.value;
     dbms_output.put_line('x is: '|| x);
    end;
    /
    anonymous block completed
    initial value of my_pack.value: 100
    initial value of value: 100
    initial value of my_pack.const: 999
    New value of my_pack.value: 300
    x is: 999
    x is: 300
    

    One-time Procedures and Global Package variables

    USER A Executes:
    create or replace package set_global_commission
    as
     serv_commission number(4,2);
    end;
    /
    
    create or replace package body set_global_commission
    as 
     vtemp number(4,2);
    begin 
      select avg(commission_pct) into serv_commission
      from usera.employees;
      vtemp := serv_commission;
      set_global_commission.serv_commission := set_global_commission.serv_commission * 2;
      dbms_output.put_line('Original: '|| to_char(vtemp) || 
                           ' Current: ' || to_char(set_global_commission.serv_commission));
    end set_global_commission;
    /
    Package body created.
    

    -- Now initializing the package state:
    
    set serveroutput on
    begin
     dbms_output.put_line('Service commission is: '|| set_global_commission.serv_commission);
     set_global_commission.serv_commission := set_global_commission.serv_commission * 5.37;
     dbms_output.put_line('New value for User A session is: '|| set_global_commission.serv_commission);
    end;
    /
    anonymous block completed
    Original: .22 Current: .44
    Service commission is: .44
    New value for User A session is: 2.36
    

    Question: Now Can User B access the variable value?
    Answer: User B CANNOT access the value held in the session by User A. User B can ONLY see the value for the variable set within his own session:

    As USER B:
    set serveroutput on
    begin
     dbms_output.put_line('Service commission is: '|| usera.set_global_commission.serv_commission);
    end;
    /
    Error report:
    ORA-06550: line 2, column 51:
    PLS-00201: identifier 'USERA.SET_GLOBAL_COMMISSION' must be declared
    ORA-06550: line 2, column 2:
    

    Accessing Package variables: restriction
    • If a subprogram has as formal parameter a variable defined on package specification,
      calls to the subprogram need to reference the package variable definition when defining
      the ACTUAL parameter that will be used.

    -- Example:
    1. (1) package my_rec defines a RECORD type used as formal parameter type in a procedure proc1.
    2. (2) When calling proc1 from OUTSIDE the package, the ACTUAL parameter needs to be defined using the
      record type defined in the package
      .
    create or replace package my_rec
    is 
     type t_emprec is record (
        empid employees.employee_id%type,
        ln    employees.last_name%type,
        fs    employees.first_name%type);
     procedure proc1 (p_empid in number, p_emp_info OUT t_emprec);
    end my_rec;
    /
    
    create or replace package body my_rec 
    is 
      procedure proc1 (p_empid in number, p_emp_info OUT t_emprec)
      is
      begin
       select employee_id, last_name, first_name 
       into p_emp_info
       from employees
       where employee_id = p_empid;
      end proc1;
    end my_rec;
    

    -- Case 1: Success
    set serveroutput on
    declare
     emprec my_rec.t_emprec; 
    begin
      my_rec.proc1(100, emprec);
      dbms_output.put('Id => '|| emprec.empid || ' ');
      dbms_output.put_line('name => '|| emprec.fs || ' '|| emprec.ln);
    end;
    /
    anonymous block completed
    Id => 100 name => Steven King
    

    -- Case 2: Failure, even though the data type of the actual parameter RECORD was
    formed by the same components used in the definition of the FORMAL parameter.
    set serveroutput on
    declare
     type t_emprec2 is record (
        empid employees.employee_id%type,
        ln    employees.last_name%type,
        fs    employees.first_name%type);
     emprec2 t_emprec2;
     
    begin
      my_rec.proc1(100, emprec2);
      dbms_output.put('Id => '|| emprec2.empid || ' ');
      dbms_output.put_line('name => '|| emprec2.fs || ' '|| emprec2.ln);
    end;
    /
    Error report:
    ORA-06550: line 9, column 3:
    PLS-00306: wrong number or types of arguments in call to 'PROC1'
    ORA-06550: line 9, column 3:
    



    Information about Packages in the Data Dictionary
    SQL> SELECT object_name, object_type, last_ddl_time, timestamp, status
    FROM   user_objects
    WHERE  object_type IN ('PACKAGE','PACKAGE BODY');
    /
    
    OBJECT_NAME             OBJECT_TYPE          LAST_DDL_  TIMESTAMP           STATUS
    ------------------------- ------------------ ---------- ------------------- -------
    DEPARTMENT_PKG          PACKAGE              03-OCT-11  2011-10-03:14:22:52 VALID
    DEPARTMENT_PKG          PACKAGE BODY         03-OCT-11  2011-10-03:14:23:45 VALID
    MESSAGE_API             PACKAGE              19-SEP-11  2011-09-19:01:15:22 VALID
    MESSAGE_API             PACKAGE BODY         19-SEP-11  2011-09-19:01:18:22 VALID
    MY_PACK                 PACKAGE              19-SEP-11  2011-09-19:13:11:00 VALID
    MY_PACK                 PACKAGE BODY         19-SEP-11  2011-09-19:13:22:10 VALID
    SET_EMPNO_CTX_PKG       PACKAGE BODY         27-SEP-11  2011-09-27:09:56:41 INVALID
    SET_GLOBAL_COMMISSION   PACKAGE              23-SEP-11  2011-09-23:10:26:32 VALID
    SET_GLOBAL_COMMISSION   PACKAGE BODY         23-SEP-11  2011-09-23:10:37:00 VALID
    
    
    SQL> DESCRIBE my_pack
    PROCEDURE    Argument Name Type   IN/OUT Default 
    ------------ ------------- ------ ------ ------- 
    UPDATE_VALUE PVAL          NUMBER IN     unknown 
    

    Dependencies between subprograms
    • The view all_dependencies; user_dependencies; dba_dependencies; let you examine dependencies between stored programs.
    • Invalid package body does not invalidate procedures that reference (and thus depend upon) the package specification.
    • This is because the package specification is the schema-level component.
    • In this case, since the subprograms don't see the invalid package body, a run-time error will be raised if the
      procedure makes a call to an object that needs to execute within the invalid package body.
    SQL>SELECT name, type, referenced_name, referenced_type, dependency_type
    FROM user_dependencies
    ORDER BY name;
    
    NAME              TYPE        REFERENCED_NAME                    REFERENCED DEPE
    -------------------- ------------ ------------------------------ ---------- ----
    DEPARTMENT_PKG       PACKAGE BODY STANDARD                       PACKAGE    HARD
    DEPARTMENT_PKG       PACKAGE      STANDARD                       PACKAGE    HARD
    DEPARTMENT_PKG       PACKAGE      EMPLOYEES                      TABLE      HARD
    DEPARTMENT_PKG       PACKAGE BODY EMPLOYEES                      TABLE      HARD
    DEPARTMENT_PKG       PACKAGE BODY DEPARTMENT_PKG                 PACKAGE    HARD
    DEPARTMENT_PKG       PACKAGE      DEPARTMENTS                    TABLE      HARD
    DEPARTMENT_PKG       PACKAGE BODY DEPARTMENTS                    TABLE      HARD
    EMP_NO_SAL           VIEW         EMPLOYEES                      TABLE      HARD
    FIBONACCI            FUNCTION     STANDARD                       PACKAGE    HARD
    GET_BONUS            PROCEDURE    STANDARD                       PACKAGE    HARD
    GET_BONUS            PROCEDURE    BONUS                          TABLE      HARD
    GET_BONUS            PROCEDURE    DBMS_OUTPUT                    SYNONYM    HARD
    GET_BONUS            PROCEDURE    SYS_STUB_FOR_PURITY_ANALYSIS   PACKAGE    HARD
    MESSAGE_API          PACKAGE BODY DBMS_PIPE                      SYNONYM    HARD
    MESSAGE_API          PACKAGE BODY EMPLOYEES                      TABLE      HARD
    MESSAGE_API          PACKAGE BODY STANDARD                       PACKAGE    HARD
    MESSAGE_API          PACKAGE BODY DBMS_STANDARD                  PACKAGE    HARD
    MESSAGE_API          PACKAGE BODY MESSAGE_API                    PACKAGE    HARD
    MESSAGE_API          PACKAGE BODY DBMS_OUTPUT                    SYNONYM    HARD
    MESSAGE_API          PACKAGE      EMPLOYEES                      TABLE      HARD
    MY_PACK              PACKAGE BODY MY_PACK                        PACKAGE    HARD
    MY_PACK              PACKAGE BODY DBMS_OUTPUT                    SYNONYM    HARD
    MY_PACK              PACKAGE BODY STANDARD                       PACKAGE    HARD
    MY_PACK              PACKAGE      STANDARD                       PACKAGE    HARD
    

    Checking dependency and status:
    
    create or replace procedure pget_salary 
    (p_empid in employees.employee_id%type) 
    as 
      vsal employees.salary%type;
    begin
      select salary into vsal 
      from employees
      where employee_id = p_empid;
      dbms_output.put_line('Salary of empid: '|| p_empid || ' is '|| vsal);
    end pget_salary;
    /
    
    create or replace package my_pack as
     procedure p1 (p_empid employees.employee_id%type);
    end;
    /
    
    create or replace package body my_pack as
      procedure p1 (p_empid employees.employee_id%type) 
      is
      begin
        dbms_output.put_line('calling pget_salary for employee '|| p_empid);
        pget_salary(p_empid);
      end p1;
    end my_pack;
    /
    

    Now check user_objects, user_dependencies, user_arguments

    -- (1) Check object dependencies on USER_DEPENDENCIES
    
    select name, type, referenced_name, referenced_type, dependency_type
    from user_dependencies
    where name in ('MY_PACK', 'PGET_SALARY')
    order by name, type;
    
    NAME          TYPE       REFERENCED_NAME            REFERENCED_T DEPENDENCY
    ---------------- ------------ ---------------------------- ------------ ----------
    MY_PACK          PACKAGE      EMPLOYEES                    TABLE        HARD
    MY_PACK          PACKAGE BODY STANDARD                     PACKAGE      HARD
    MY_PACK          PACKAGE BODY DBMS_OUTPUT                  SYNONYM      HARD
    MY_PACK          PACKAGE BODY MY_PACK                      PACKAGE      HARD
    MY_PACK          PACKAGE BODY EMPLOYEES                    TABLE        HARD
    MY_PACK          PACKAGE BODY PGET_SALARY                  PROCEDURE    HARD
    PGET_SALARY      PROCEDURE    EMPLOYEES                    TABLE        HARD
    PGET_SALARY      PROCEDURE    STANDARD                     PACKAGE      HARD
    PGET_SALARY      PROCEDURE    DBMS_OUTPUT                  SYNONYM      HARD
    PGET_SALARY      PROCEDURE    SYS_STUB_FOR_PURITY_ANALYSIS PACKAGE      HARD
    

    
    -- (2) Check LAST_DDL_TIME and validity status on USER_OBJECTS 
    select object_name, object_type, 
           to_char(last_ddl_time, 'DD-MON-YY:HH:MI:SS') LAST_DDL_TIME, 
           timestamp, status 
    from user_objects
    where object_name in ('MY_PACK', 'PGET_SALARY');
    
    /
    OBJECT_NAME   OBJECT_TYPE    LAST_DDL_TIME      TIMESTAMP           STATUS
    ------------- -------------- ------------------ ------------------- -------
    MY_PACK       PACKAGE        04-OCT-11:11:35:09 2011-10-04:11:35:09 VALID
    MY_PACK       PACKAGE BODY   04-OCT-11:11:35:14 2011-10-04:11:35:14 VALID
    PGET_SALARY   PROCEDURE      04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID
    

    
    set serveroutput on 
    begin
     my_pack.p1(100);
    end;
    /
    anonymous block completed
    calling pget_salary for employee 100
    Salary of empid: 100 is 7500
    

    
    alter package my_pack compile;
    
    OBJECT_NAME   OBJECT_TYPE    LAST_DDL_TIME TIMESTAMP     STATUS
    ------------- -------------- ------------------ ------------------- -------
    MY_PACK       PACKAGE        04-OCT-11:12:20:21 2011-10-04:11:35:09 VALID
    MY_PACK       PACKAGE BODY   04-OCT-11:12:20:21 2011-10-04:11:35:14 VALID
    PGET_SALARY   PROCEDURE      04-OCT-11:12:06:52 2011-10-04:11:31:12 VALID
    


    Packages break the cycle of dependency.
    source: Ask Tom

    Scenario:
    • (a) Procedure A does something.
    • (b) Procedure B calls Procedure A :(B depends on A)
    • (c) If procedure A is recompiled, then Procedure B becomes invalid.

    -- (1) - create procedure A
    SQL> create or replace procedure A as
     begin null; end;
    /
    
    -- (2) create procedure B. B calls A.
    SQL> create or replace procedure B as
     begin A; end;
    /
     
    -- (3) Check object status: A and B are VALID
    SQL> select object_type, object_name, status
     from user_objects
     where object_name in ('A', 'B');
     
    OBJECT_TYPE         OBJECT_NAME     STATUS  
    ------------------- -------------   ------- 
    PROCEDURE           A               VALID   
    PROCEDURE           B               VALID   
    


    --(4) B depends on A. If you modify A, B becomes invalid.
    
    SQL> create or replace procedure A as
      x number;
    begin 
       x :=1;
       null; 
    end;
    /
    
    SQL> select object_type, object_name, status
     from user_objects
     where object_name in ('A', 'B');
    
     OBJECT_TYPE         OBJECT_NAME   STATUS  
    ------------------- -------------- ------- 
    PROCEDURE           A              VALID   
    PROCEDURE           B              INVALID 
    


    Now, if A and B are within PACKAGES:
    SQL> create or replace package a as
     procedure a; end;
    /
    
    SQL> create or replace package b as
     procedure b; end;
    /
    
    SQL> create or replace package body a as
     procedure a is
      begin null; end;
    end;
    /
    
    SQL> create or replace package body b as
     procedure b is
      begin a.a; end;
    end;
    /
    
    SQL> select object_type, object_name, status
     from user_objects
     where object_name in ('A', 'B');
    
    OBJECT_TYPE         OBJECT_NAME  STATUS  
    ------------------- ------------ ------- 
    PACKAGE             A            VALID   
    PACKAGE BODY        A            VALID   
    PACKAGE             B            VALID   
    PACKAGE BODY        B            VALID   
    
    

    -- Now, if package body B is modified:
    SQL> create or replace package body a as
     procedure a is
       x number;
      begin 
       x:=1; 
       null;
      end;
    end;
    
    SQL> select object_type, object_name, status
     from user_objects
     where object_name in ('A', 'B');
    
    OBJECT_TYPE         OBJECT_NAME   STATUS  
    ------------------- ------------- ------- 
    PACKAGE             A             VALID   
    PACKAGE BODY        A             VALID   
    PACKAGE             B             VALID   
    PACKAGE BODY        B             VALID   
    
    Here package B remains VALID. 
    
    • This is because only package BODY b was modified.
    • As long as the spec does not change, package B will remain valid.