
Building examples of using SQL functions supported by Oracle
Check the post here (in construction)
Single-row F | Aggregate F | Analytic F |
Object Reference F | Model F | OLAP F |
- 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
- 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
- ASCII
- INSTR
- LENGTH
- REGEXP_COUNT
- REGEXP_INSTR
- NLS_CHARSET_DECL_LEN
- NLS_CHARSET_ID
- NLS_CHARSET_NAME
- 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
- GREATEST
- LEAST
- 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
- BFILENAME
- EMPTY_BLOB
- EMPTY_CLOB
- CARDINALITY
- COLLECT
- POWERMULTISET
- POWERMULTISET_BY_CARDINALITY
- SET
- 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
- DECODE
- DUMP
- ORA_HASH
- VSIZE
- COALESCE
- LNNVL
- NANVL
- NULLIF
- NVL
- NVL2
- SYS_CONTEXT
- SYS_GUID
- SYS_TYPEID
- UID
- USER
- USERENV
- REGR_SLOPE
- REGR_INTERCEPT
- REGR_COUNT
- REGR_R2
- REGR_AVGX
- REGR_AVGY
- REGR_SXX
- REGR_SYY
- REGR_SXY
(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 ...
(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 ...
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;
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;
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;
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;
| ![]() [ Oracle PL/SQL ] |
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
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.
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 |
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
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 |
On 10g R1 |
PLSQL_OPTIMIZE_LEVEL
init parameter: Controls Global optimization of Pl/SQL Code. Default value is often good enough.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 linkingdbmsupgnv.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.PLSQL_WARNINGS
init parameter or DBMS_WARNING
package.On 10g R2 |
DBMS_DDL
wrap subprograms obfuscate (hide) dynamically generated PL/SQL code units.Pragmas |
-- 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>
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;
DECLARE past_due EXCEPTION; -- declare exception PRAGMA EXCEPTION_INIT (past_due, -20000); -- assign error code to exception BEGIN ...Check herefor an example.
PLSQL_OPTIMIZE_LEVEL
compilation parameter := [2 (default) | 3 ]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; /
Conditional compilation |
$IF, $THEN, $ELSE, $ELSIF, $ERROR
($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.
DBMS_DB_VERSION
provide static constants:$$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.PLSQL_CCFLAGS
compilation parameterALTER SESSION set PLSQL_CCFLAGS = 'flag:True, val:5'
$$plsql_compilation_parameter
: a PL/SQL compilation parameter 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.
DBMS_PREPROCESSOR
to print source textDBMS_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.
CREATE TYPE
statement. (Why? Attributes of a TYPE will determine the physical structure of dependent tables.) On 11g R2: Compiling PL/SQL Units for Native Execution |
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;
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 |
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)![]() | Package instantiation and initialization: |
![]() | Package State |
![]() | SERIALLY_REUSABLE |
![]() | 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.
create or replace package my_pack
as
value number := 100;
const constant number := 999;
procedure update_value (pval number);
end my_pack;
/
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;
/
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
anonymous block completed
x is: 999
x is: 300
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 |
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.
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
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 |
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;
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
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 |
all_dependencies; user_dependencies; dba_dependencies;
let you examine dependencies between stored programs.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;
/
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 |
-- (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
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
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.