Author : Denis P Gohel email : denisgohel@caltiger.com Structure of Anonymous PL/SQL Block ----------------------------------- DECLARE BEGIN EXCEPTION END; Structure of PL/SQL Subprograms ------------------------------- HEADER AS / IS BEGIN EXCEPTION END; e.g. of creating Procedure -------------------------- CREATE or REPLACE PROCEDURE (a IN number, b OUT varchar2, c IN OUT boolean) IS BEGIN b:= a*15; c := c+b; END;  Stored Programs are functions, procedures which are stored in database  Program Units are Anonmyous block  In Procedure Builder, the subprograms which are in program units are lost, if you exit from Procedure Builder. So, you must save it in Database, if it is required in future. Once it is stored in database, it will be called Stored Program Units.  The subprograms which are stored in Database are called Server-Side Program Units.  Server-Side Program Units are created from Database Objects (option) of Procedure Builder  For processing stored program units (SPU), server side PL/SQL engine executes.  In case of SPU, use DBMS_OUTPUT.PUT_LINE and in case of Client-side Program Units use TEXT_IO.PUT_LINE  In case of SPU, BIND / HOST variables cannot be used. BUT, IT CAN BE PASSED AS PARAMETER.  Use SHOW ERRORS to see compliation errors  When "CREATE OR REPLACE" or "CREATE" procedure/function is issued, data dictionary gets updated. So, even if it contains compilation errors, data dictionary shows it.  In case of procedure builder, you can convert CPU to SPU by drag and drop option  If Proc/Func are created from Procedure Builder, the words "CREATE OR REPLACE" "/" are of no value (i.e. invalid characters)  To convert the source code into file, use FILE - EXPORT DATATYPE CAN BE %TYPE, %ROWTYPE, NUMBER, VARCHAR2, BOOLEAN.... WITH NO SIZE SPECIFICATION Parameter Modes --------------- IN -- it is a default mode, no values can be assigned to this variable in subprogram it can be literal, variable, expression, contant, initialised variable OUT -- pass uninitialised variable, assign value during execution it must be a variable IN OUT -- it takes some value, process it and then store new value in it. it must be a variable OUT and IN OUT will return the value to calling environment  use colon ( : ) to use bind variable  to see the value of bind variable, use PRINT  use " VARIABLE var1 VARCHAR2(10)" to assign bind variable var1  if in above, no length is used, default length is 1 character CREATING BIND VARIABLE IN PROCEDURE BUILDER ------------------------------------------- .CREATE CHAR var1 LENGTH 10 .CREATE NUMBER var2 PRECISION 2  for executing a procedure from Procedure Builder, do not use EXECUTE word METHODS OF PASSING PARAMETERS ----------------------------- PROCEDURE TEST(a number, b varchar2, c boolean, d number) a. Positional passing values in same sequence as is specified in procedure/function definition TEST(1,'DENIS',true,10) TEST(1,NULL,NULL,10) BUT CANNOT TEST(1,,,10) b. Named Association " => " pass values prefixing name of parameter followed by special character " =>" TEST(a=>1,d=>10) c. Combination First values by positional and then after name association TEST(1,'DENIS',d=>10) Assigning default value of parameter, if no values are passsed when executed ----------------------------------------------------------------------------- CREATE OR REPLACE PROCEDURE (A NUMBER DEFAULT 100, B VARCHAR2 DEFAULT 'DENIS', C BOOLEAN DEFAULT TRUE) OR CREATE OR REPLACE PROCEDURE (A NUMBER := 100, B VARCHAR2 := 'DENIS', C BOOLEAN := TRUE)  YOU CAN DECLARE A COMPLETE PROCEDURE IN A DECLARATION SECTION OF ANY PROCEDURE. THIS IS EQUIVALENT TO CREATING A PRIVATE PROCEDURE WHICH CAN BE CALLED ONLY BY THE PROCEDURE IN WHICH IT IS INCLUDED. BUT THE SUB PROCEDURE MUST BE DECLARED IN DECLARATION SECTION OF MAIN BLOCK AS LAST ITEM. IF AFTER DECLARATION OF SUB PROCEDURE ANY VARIABLE IS DECLARED, IT WILL CAUSE COMPLIATION ERROR E.G. CREATE OR REPLACE PROCEDURE leave_emp2 (v_id IN emp.empno%type) IS PROCEDURE log_exec IS BEGIN insert into log_table(user_id, log_date) values (user,sysdate); END log_exec; -- if any variables are declared here, will give error BEGIN delete from emp where empno = v_id; log_exec; END leave_emp2;  @abc.sql = start abc.sql  Since Procedure Builder is client side tool, you can develop client side PUs.  functions can be used in SQL statement. e.g. CREATE or REPLACE FUNCTION tax(abc NUMBER) RETURN number IS BEGIN return (abc*10); END; in SQL statement SELECT ENAME,EMPNO,tax(sal) FROM emp;  In following clauses of SQL Statements, Functions can be called 1. In SELECT statement 2. In WHERE, HAVING 3. In CONNECT BY, START WITH, ORDER BY and GROUP BY 4. VALUES clause of INSERT statement 5. SET clause of UPDATE statement 6. It cannot be used in CREATE TABLE statement to assign default value of any column OR to CHECK any integrity constraint. BUT FUNCTION MUST BE PURE FUNCTION (i.e. it must have following characteristics) -------------------------------------------------------------------------------- 1. must be a stored function 2. must be a single row function 3. must only take IN parameter, NO OUT or IN OUT type of modes 4. datatype must be only valid SQL datatype (CHAR, DATE, NUMBER) 5. NO BOOLEAN, RECORD TYPE, PL/SQL TABLE IS ALLOWED. 6. Must not contain any DML, TRANSACTION, SELECT in function If any of the above condition fails, the function is said to have SIDE EFFECT and cannot be used in SQL statement If the function is PURE but it calls other function which has side effect, then both of these cannot be called in SQL statement. -------------------------------------------------------------------- procedure | function ---------------------------------|----------------------------------- 1 may or may not have parameters | 2 may or may not return value | must return one and only one value 3 in, in out, or out type of | only in type of parameter (other can be parameters can be passed | passed, but not appreciated.) 4 cannot be used in SQL statement| pure function can be used in SQL stmt 5 no return datatype | return datatype must be specified | --------------------------------------------------------------------  A function can be said as " it is a procedure having one as OUT type of parameter" Packages =-=-=-=-  A procedure body is the implementation of package specification  Package itself cannot be parameterised, called or nested.  Benefit of making Package ------------------------- When called for the first time, the entire body (all subprograms) gets loaded into memory (for the session) and subsequent calling does not require any disk I/O  Package Specification contains - Public Variable - Prodecure / Function declaration (i.e. Public procedure/function)  Package Body contains - Private variable - Private procedure/function - public procedure/function  Package Variables ----------------- LOCAL - variables which are declared within DECLARE section of proc/func The value of this variable is available only in the func/proc in which it is declared. It is not available to other func/proc of the same/other package. PRIVATE - This variable are declared in PACKAGE BODY. This variable can be accessed by any proc/func defined within that package body. PUBLIC - This are declared in Package specification  It is possible to have package specification without body but not vice versa.  You should drop stand-alone procedure, if incorporated into package.  The order of declaration of variables / proc / func are very important.  The variable/ func / proc must be defined prior to its reference  Global package variable does not require "GLOBAL" word / " : " before its usage.  execute packeged program in a remote database EXECUTE packagename.procedure@aa(par1,par2)  Global variable is available for the session e.g. CREATE OR REPLACE package g_vars IS var1 number := 22; var2 number := 33; end g_vars; SQL> EXECUTE dbms_output.put_line(g_vars.var1);  Values of global variable (defined by package) can be accessed using EXECUTE or PL/SQL block  If you want to declare only global variables, just mention package specification. No need to declare package body.  using global variables ---------------------- in case of same package, no need to specify package name in case of other package, must have to specify package name  DROP PACKAGE packagename will drop package specification as well as package body  DROP PACKAGE BODY packagename will drop only package body  declare those variabless in package specification, which are to be made public  public variable should be as minimum as possible  changes in package specification requires recompilation of package body, but not vice versa.  why packages ? -------------- a. quick access to stored p/f once accessed b. offers global variable c. bundle similar p/f in package for meaningful interpretation d. offers private p/f e. ONLY ONE COPY IN MEMORY FOR ALL USERS f. offers to create p/f of same name with different datatype of parameter, overloading.  Package Overloading means passing different parameter datatype to same p/f e.g. package.function(n number) package.function(v varchar2) ...., etc. here function remains same but datatype of parameter decides which function to execute The different may be due to a. number of parameters b. datatype of parameters c. order of parameters Note : Only local or packaged subprogrames can be overloaded. CANNOT OVERLOAD, if : -=-=-=-=-=-=-=-=-=-= a. parameter differs in name or mode (in out) b. if datatype is in same family (e.g.number and decimal) c. only return type is diff. (even if belongs to diff datatype family e.g. number and varchar2) When and Why Forward Declaration ? ---------------------------------- Forward declaration refers to declaration of those function which are used prior to their creation. e.g. procedure A calls function B. But in package body, procedure A is prior to function B. It will give error. So, for function B you can use forward declaration enabling Oracle pl/sql that function B will be created subsequently. IT IS MUST WHEN BOTH THE FUNCTION/PROCEDURE USES EACH OTHER. FOR E.G. PROCEDURE A CALLS FUNCTION B AND FUNCTION B CALLS PROCEDURE A. IN THIS CASE, FORWARD DECLARATION IS MUST. IF YOU WANT TO MAINTAIN F/P ALPHABATICALLY, THEN ALSO YOU MAY HAVE TO USE FORWARD DECLARATION.  One-Time-Only procedures are created using PACKAGE. This public variables which are created are availble to the user untill end of session.  BEGIN section is not required in PACKAGE BODY, if no ony-time-only execution code exists. syntax is CREATE OR REPLACE PACKAGE my_package IS procedure a(n number); procedure b(v varchar2); END my_package; BUT in case of ony-time-only CREATE OR REPLACE PACKAGE my_package IS aa number := 0 procedure a(n number); procedure b(v varchar2); BEGIN aa := 90; END my_package; Restrictions on Packaged Functions ---------------------------------- a. No DML allowed (not >= 8i) b. only local function can update packaged variables c. etc. PRAGMA RESTRICT_REFERENCES are to check the purity of function for using it in SQL statement. No longer required in Oracle8i PRAGMA RECTRICT_REFERENCES(function_name,WNDS,WNPS,RNDS,RNPS) WNDS - Write No Database State WNPS - Write No Package State RNDS - Read No Database State RNPS - Read No Package State If package contains multiple functions with same name, it applies to last one  public cursor remains open untill closed (only for basic and do while loop)  must (U) page 6-16 Triggers ----------  Trigger may be on TABLES, VIEWS, or applications (e.g. form builder's)  Trigger timing is - BEFORE or AFTER  trigger event may be INSERT, UPDATE or DELETE  at a time each row may be processed or entire table  can restrict the scope of trigger by applying WHERE clause BEFORE - executes trigger body before DML is executed AFTER - executes trigger body after DML is executed INSTEAD OF - use this when trigger to be written on VIEW and not on TABLE In this case, the TRIGGER BODY fires/executes instead of triggering statement  In case of UPDATE trigger, you can specify the name of column which will be updated. But in case of INSERT or DELETE, entire row is affected and this it is not possible to specify column in case of INSERT or DELETE for e.g. CREATE OR REPLACE TRIGGER upd_sal BEFORE UPDATE OF sal ON emp DECLARE ..... So, the above trigger will fire only when SAL column of emp is updated. Other-wise, it will not fire.  Default trigger type STATEMENT. to turn it FOR EACH ROW, you must specify it as such. In case of FOR EACH ROW, the trigger body will fire as many times, the statement affects the number of rows.  *** STATEMENT TRIGGER FIRES EVEN IF NO ROWS ARE AFFECTED BY TRIGGER STATEMENT *** BUT IN CASE OF ROW TRIGGER, IT FIRES ONLY WHEN ANY ROWS ARE AFFECTED BY TRIGGER STMT.  In trigger body, even Java procedure can also be called.  to get the old and new values use :OLD.column_name :NEW.column_name NEW is used in case of INSERT or UPDATE only OLD can be used in INSERT, UPDATE or DELETE.  When INSERT, UPDATE or DELETE statement affects excatly one row, both statement and row level trigger fires once.  Before DML is fired, the old/existing values are stored in rollback segment.  availability of old and new values INSERT UPDATE DELETE ------ ------ ------ OLD NO YES YES NEW YES YES NO  syntax CREATE OR REPLACE TRIGGER trigger_name BEFORE INSERT OR DELETE OR UPDATE OF SAL ON EMP FOR EACH ROW DECLARE BEGIN EXCEPTION END;  To stop the execution of DML, use BEFORE trigger and stop it using RAISE APPLICATION_ERROR  Mutation can only be on ROW level triggers (never on stmt level) Managing Dependencies =-=-=-=-=-=-=-=-=-=-=  To recompile object explicitly ALTER object object_name COMPILE; Object may be function, procedure, package, pakcage body, view  Dependency may be DIRECT or INDIRECT  Again depending upon database, it may be LOCAL (with same database) or REMOTE (with another database)  If any dependent object has been invalidated due to change in referenced object, Oracle implicitly recompiles that dependent object at its first call.  DEPTREE table is for tabular view of dependencies where 0 indicates referenced object and 1 indicates, its dependencies over 0 level and so on and so forth.  When any referenced object is dropped after running DEPTREE_FILL, next "SELECT * FROM IDEPTREE" shows " <> ". BUT, after dropping, you again run DEPTEE_FILL, it will update IDEPTREE and remove the traces of dropped referenced object from ideptree view.  EXCEPT LOCAL-PROCEDURE-TO-REMOTE-PROCEDURE (this includes function, package, triggers ) Oracle does not maintain REMOTE dependencies implicitly. These will be invalidated BUT NOT RECOMPILED automatically. How Oracle manages these dependencies ? --------------------------------------- using (as per user's preferences) 1. TIMESTAMP checking 2. SIGNATURE checking Timestamping - When a procedure or func is modified/created, oracle internally stamps date and time to it. It is actually compared, when it is called. Signature - both timestamp and signature are recorded. Signature includes (name of proc, parameter datatypes, mode, number of parameters, return datatype - in function). First, it uses timestamp, if finds OK, allows, else refers Signature using RPC Layer. and if this also fails, gives error. RPC Layer - Remote Procedure Call Layer HERE THE SIGNATURE OF REMOTE PROCEDURE IS RECORDED IN LOCAL PROCEDURE. How to set these preferences ? (USING ANY ONE OF THESE, NOT ALL) ------------------------------ in init.ora, REMOTE_DPENDENCIES_MODE = TIMESTAMP or SIGNATURE to set system level -- ALTER SYSTEM SET REMOTE_DEPENDENCIES_MODE = value to set session level - ALTER SESSION SET REMOTE_DEPENDENCIES_MODE = value When to recompile ? ------------------- In case of local dependencies Here whenever any referenced object is altered, all dependent objects are invalidated and when a dependent object is called, then it checks it validity. If the status is INVALID, Oracle automatically recompiles that object and executed. In case of Remote dependencies Let, assume databases A (referenced) and B (dependent) When an referenced object in A is changed, the status of dependent object in B remains valid and on first execution, oracle gives ERROR due to wrong status and then marks it as INVALID. Once it become INVALID, subsequent call will force the Oracle to recompile that dependent object (in B) to compile. (BELOW IS FOR PROC-2-PROC ONLY AND NOT FOR OTHER OBJECTS ) IN SHORT, ORACLE RECOMPILES THOSE DEPENDENT OBJECT WHICH HAS INVALID STATUS. SINCE, IN CASE OF REMOTE DATABASE, THE STATUS REMAINS VALID EVEN IF REFERENCED OBJECT HAS BEEN CHANGED, IT GIVES ERROR IN FIRST EXECUTION. BUT THEN AFTER ITS STATUS TURNS TO INVALID AND THE SUBSEQUENT CALL FORCES IT TO RECOMPILE. WHEN PROCEDURE OF B IS COMPILED, IT RECORDS TIMESTAMP OF PROCEDURE AT A IN ITS P CODE. Oracle records timestamp of referenced object in dependent object. Whenever a dependent object is called, it compares timestamp of referenced object with timestamp of the same recorded in dependent object. If this matches, it processes otherwise will recompile, if it is a local dependency and in case of remote, it will give error and invalidate dependent object. How recompile works ? ---------------------  First recompile those invalid object on which this object depends. IF ONE TABLE IS BOTH PRIVATE AND PUBLIC, THEN FIRST REFERENCE TO PRIVATE. IF PRIVATE IS DROPPED, THEN DEPENDENT OBJECT WILL BE INVALIDATED. BUT THE RECOMPILATION WILL BE SUCCESSFUL IF THAT PUBLIC OBJECT (REFERENCED) HAS SAME STRUCTURE AS PRIVATE OBJECT WAS HAVING. *********** IN CASE OF PACKAGE, IF PACKAGE BODY IS CHANGED (PACKAGE SPECIFICATION REMAINS AS IT IS) THEN STAND-ALONE PROC/FUNC CALLING THAT PACKAGED P/F, WILL REMIN VALID. BUT IF PACKAGE SPECIFICATION IS CHANGED, BOTH STAND-ALONE AND PACKAGE BODY WILL BE INVALIDATED. AGAIN, WHEN A PACKAGE BODY CALLS ANY STAND ALONE P/F, AND THAT STAND-ALONE P/F IS CHANGED, THEN ENTIRE PACKAGE BODY IS INVALIDATED. SO, THERE ARE TWO SITUATIONS : 1. STAND-ALONE CALLS PACKAGE P/F, HERE IF PACKAGE P/F CHANGES, STAND-ALONE NOT AFFECTED. 2. PACKAGE CALLS STAND-ALONE P/F, HERE IF STAND-ALONE CHANGED, ENTIRE PACKAGE BODY INVALIDATED. ---------------couchman ---------- 1. parameters cannot be passed to ananonymous block 2. in case of granting execute procedure, the grantee acquired the same privileges as of owner but it is not so in case of DBMS_SQL. 3. OUT parameter type of variable may has value before passing it to p/f 4. cannot give literal value in case of OUT, IN OUT variable type. 5. parameter and variable datatypes must be Oracle internal datatypes like NUMBER and VARCHAR2, not datatype extensions like PLS_INTEGER. Also, the function must only work on row data, not group data 6. EXEC procedure_name is equivalent to EXECUTE procedure_name 7. RUN is not equivalent to EXECUTE 8. If a procedure is called without EXECUTE command, it shows error of "Variable not found" 9. The equivalent command in Procedure Builder to the get command in SQL*Plus would be load file 10. ** OTHERS EXCEPTION ALSO HANDLES USER DEFINED EXCEPTIONS DATA DICTIONARY --------------- DBA_OBJECTS - P/F/P DETAILS, STATUS, OWNER, ETC. DBA_SOURCE - SOURCE CODE OF P/F/P - MAY BE ENCRYPTED USING ORACLE WRAPPER FOR SECURITY PURPOSE DBA_ERRORS - ERROR GENERATED IN LAST COMPILE dbms_job - When you want to work to be done irrespective of login, password, whether password has been changed, etc. Means, the job will be done, if database is running, no matter whether the user exists or not, etc. RULES OF DEPENDENCY IN PACKAGE ------------------------------ 1. Package Body compilation failed, still package specification VALID 2. Package Specification failed during compilation a. Body becomes INVALID b. all dependent p/f becomes INVALID 3. Referenced package body must be compiled before referencing package body (specification, no effect) 4. If a p/f calls INVALID package body (specification valid), the caller p/f remains VALID, even though Oracle shows ERROR 5. If the package body is INVALID, none of the procedure from the package will execute (even if the package may have valid procedures). 6. Package body treatment is like independent. If the package body is invalid, the dependent objects still remains VALID even after Oracle comes to know about its INVALIDITY. 7. Local body fails (invalid), remote body remains as it is (till it calls local body). BUT IF Local specification fails, local body fails and remote body also fails immediately. 8. Code in packages should be logically grouped according to whether they are used for the same overall function in an application, or whether they are depended upon consistently by other applications. 9. Compilation of the bodies of packages that reference the utilities package must happen after the utilities package specification is compiled. But, because other packages are procedurally dependent only on the utilities package specification, you can recompile the utilities package body as often as you need to. package A depends on B's Specification only. i.e. a part of specification then package A (sp/body) must be compiled after specification of B B's body can be recompiled without any effects on A's validity as A dpepends only on B's specification. 10. Re-instantiation If after execution of package for first time, Package is changed, it will give error "existing state of packages has been discarded" and then again will be valid for subsequent time. OBJECT Type ------------ Types - Transient Object Type which are declared, initialised and deleted programatically (like record type, PL/SQL table) - Persistent Object Type Stored in database **Transient object type always created using datatype of persistent object and as such its datatype can never by PL/SQL datatype.  Object type structure is divided into SPECIFICATION (public) AND BODY (contains method to access object type attributes.)  Rules of Object Type -------------------- 1. METHODS must be declared last in specification 2. if no methods used in specification, body not needed. 3. no declaration in body section 4. body may contain private p/f 5. cannot initialise or give default values to attributes 6. not null constraint to attribute not possible  creating new object type create or replace type address_type as object (add1 varchar2(20), add2 varchar2(20), city varchar2(10), pin varchar2(10), country varchar2(10), phone varchar2(25), email varchar2(20), member function fulladd return varchar2) create or replace type body address_type as member function fulladd return varchar2 is begin return add1||','||add2||chr(10)||city||' - '||pin||' '||country||chr(10)||phone||' '||email end; end;  inserting values in table using object insert into student values (1,'DENIS',ADDRESS_TYPE('AA AA Street', '0TH FLOOR', 'USA','00009','USA','99099','xx@vv.com'));  selecting columns having object type select e.id, e.name, e.address.fulladd() from student e;  MUST TYPE SETTING VALUE USING METHOD FOR INSERTING  To create table of object type create table table_name of object_type  no need to use constructor to insert data  select statement as usual, no need to use method, constructor  if you want to use method, must give table alias  in case of table defined as object type, there are two ways to insert data into it 1. use constructor, fill all subfields in constructor 2. use column name and values (as usual for insert)  VALUE word extract values from object and stores into itself like record type e.g. SELECT VALUE(P) FROM STUDENT P // USED ONLY WHEN TABLE IS OBJECT TYPE