IT Bangla Ltd . s e t a fili f a s t i r o / d n o a e t e n s e acl e li c Or a b l e r 1, s f n 1 a 20 n -tr © Oracle aD an toabase 10g: Develop ht PL/Sa Qs L Proe ฺgram Units g h d pyri a ฺ n e t) n t G u i o g l Vd oelume 1 • Student Guide C n u b a S t bited. m e e r @ uits e t h i s hi a ro A li ( p e r n m e o A ti d uM b ri t s di r o n o ti D17169GC21 c u Edition 2.1 d o r December 2006 p e r D48230 d e z ri o h t u a n U IT Bangla Ltd Authors Copyright © 2006,Oracle. All rights reserved. Tulika Srivastava Disclaimer Glenn Stokol This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your Technical Contributors own use in an Oracle training course. The document may not be modified or altered in and Reviewers any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform,reproduce, publish, . s Chaitanya Koratamaddi license, post, transmit, or distribute this document in whole orin part without the e at Dr. Christoph Burandt express authorization of Oracle. fili Zarko Cesljas The information contained in this document is subject to change without notice. If you af Yanti Chang find any problems in the document, please report them in writingto: Oracle University, s Kathryn Cunningham 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not r it Burt Demchick warranted to be error-free. o / Laurent Dereac Restricted Rights Notice d n Peter Driver o a If this documentation is delivered to the United States Government or anyone using e t e Bryan Roberts the documentation on behalf of the United States Government, thefollowing notice is n s acl BNraynnc yL lGewreeelnlybnerg applicable: e li c e Or Craig Hollister U.S. GOVERNMENT RIGHTS a b l The U.S. Government’s rights to use, modify, reproduce, releasee, rperform, display, or 1, Thomas Hoogerwerf disclose these training materials are restricted by the termn s soffthe applicable Oracle 201 TInagj-eUr Jl oIselragmensenEric Lee license agreement and/or the applicable U.S. Gn o-vterrnma ent contract. o © Malika Marghadi Trademark Notice a n ht Hildegard Mayr Oracle, JD Edwards, PeopleSaofts, a nd Siebele aฺre registered trademarks of Oracle g h d pyri NSuangiathvaal Plia Ptealtaballa Cowonrpeorsra.tion aanฺd/nor eitst a)f filiatnest. OGtheru niames may be trademarksof their respective o Srinivas Putrevu g l d e C Denis Raphaely b a n S t u bited. HGGlreealnennnt SRSptooebknecoreltsron m e e r @ uits e t h i s hi a pro TProinyeea r TV heAonmlniaa ps(usa n m Leex Van Der Werff o A ti d uM Graphic Designer b ri t s di Satish Bettegowda r o Editors n o ti c Nita Pavitran u d Richard Wallis o r p e Publisher r d Sheryl Domingue e z ri o h t u a n U IT Bangla Ltd Contents . s e t a Preface fili f a s I Introduction t r i Lesson Objectives I-2 o d/ Course Objectives I-3 n o a Course Agenda I-4 e t e n s acl HCuremaatinn gR ae sMooudrcuelasr i(zHeRd) a Sncdh Leamyae r e Id-7 S ubprogram Design I-8 e li c e Or a b l Modularizing Development with PL/SQL Blocks I-9 e r 1, s f n 1 Review of Anonymous Blocks I-10 a 20 Introduction to PL/SQL Procedures I-11 n -tr o © Introduction to PL/SQL Functions I-12 a n ht a s e ฺ g Introduction to PL/SQL Packages I-13h d pyri Introduction to PL/SQL Triggaeฺrsn eI-t1)4 n t G u i Co PL/SQL Execution Envinrognlment uI-1d5 e b a S t bited. PCLo/dSinQmgL P eDLee/SvreQ@loLp uiinmt seiSneQt EtL*nhPviliursos n m I-e1n7t s I-16 hi Coda ing PL/SQL in SQL*Plus I-18 ro A liC (oding PL/SQL in Oracle JDeveloper I-19 p e r n m e Summary I-20 o A ti d Practice I: Overview I-21 uM b ri t s 1 Creating Stored Procedures di Objectives 1-2 r o What Is a Procedure? 1-3 n o ti Syntax for Creating Procedures 1-4 c u Developing Procedures 1-5 d o What Are Parameters? 1-6 r p e Formal and Actual Parameters 1-7 r d Procedural Parameter Modes 1-8 e z Using IN Parameters: Example 1-9 ri o Using OUT Parameters: Example 1-10 h t u Viewing OUT Parameters with iSQL*Plus 1-11 a n U Calling PL/SQL Using Host Variables 1-12 Using IN OUT Parameters: Example 1-13 iii IT Bangla Ltd Syntax for Passing Parameters 1-14 Parameter Passing: Examples 1-15 Using the DEFAULT Option for Parameters 1-16 Summary of Parameter Modes 1-18 . s Invoking Procedures 1-19 e at Handled Exceptions 1-20 fili Handled Exceptions: Example 1-21 f a Exceptions Not Handled 1-22 s t i Exceptions Not Handled: Example 1-23 r o / Removing Procedures 1-24 d n o a Viewing Procedures in the Data Dictionary 1-25 e t e Benefits of Subprograms 1-26 e n s acl Summary 1-27 e li c Or a b l Practice 1: Overview 1-29 e r 1, n s f 1 a 20 2 Creating Stored Functions n -tr o © Objectives 2-2 a n ght Overview of Stored Functions 2-3 h a s d e ฺ pyri Syntax for Creating Functions 2-4 a ฺ n e t) n t G u i o Developing Functions 2-5 g l d e C n u hibited. SWAtdaovyraesnd tt oaF guEenxasce tmociouf ntUee: s EFeexurra-nD@mcetpifuoiilnetnse sb d e2 a F-26tu- 7nh ictsio nSs tin SQL Statements 2-8 ro FuncAtlioi n( in SQL Expressions: Example 2-9 p e r n m e Locations to Call User-Defined Functions 2-10 tio d A Restrictions on Calling Functions from SQL Expressions 2-11 uM b Controlling Side Effects When Calling Functions from SQL Expressions 2-12 ri st Restrictions on Calling Functions from SQL: Example 2-13 di Removing Functions 2-14 r o Viewing Functions in the Data Dictionary 2-15 n o Procedures Versus Functions 2-16 ti c u Summary 2-17 d o Practice 2: Overview 2-18 r p e r d e z ri o h t u a n U iv IT Bangla Ltd 3 Creating Packages Objectives 3-2 PL/SQL Packages: Overview 3-3 Components of a PL/SQL Package 3-4 s. Visibility of Package Components 3-5 e at Developing PL/SQL Packages 3-6 fili Creating the Package Specification 3-7 f a Example of Package Specification: comm_pkg 3-8 s t i Creating the Package Body 3-9 r o / Example of Package Body: comm_pkg 3-10 d n o a Invoking Package Subprograms 3-11 e t e n s Creating and Using Bodiless Packages 3-12 e acl Removing Packages 3-13 e li c Or a b l Viewing Packages in the Data Dictionary 3-14 e r 1, s f 1 Guidelines for Writing Packages 3-15 a n 20 Advantages of Using Packages 3-16 n -tr o © Summary 3-18 a n ht a s e ฺ g Practice 3: Overview 3-20 h d pyri a ฺ n e t) n t G u i o 4 Using More Package Cognlcepts d e C n u hibited. OOOvvbeeajerrcllmootiaavddeeiisnne gg r : 4 SE@-u2xb auipmtrsobpgelear a t m 4hs-i5 s 4 -S3 t ro A liO (verloading and the STANDARD Package 4-7 p e r n m e Using Forward Declarations 4-8 o A ti d Package Initialization Block 4-10 uM b Using Package Functions in SQL and Restrictions 4-11 ri t s Package Function in SQL: Example 4-12 di Persistent State of Packages 4-13 r o Persistent State of Package Variables: Example 4-14 n o ti Persistent State of a Package Cursor 4-15 c u Executing CURS_PKG 4-16 d o Using PL/SQL Tables of Records in Packages 4-17 r p e PL/SQL Wrapper 4-18 r d Running the Wrapper 4-19 e z Results of Wrapping 4-20 ri o Guidelines for Wrapping 4-21 h t u Summary 4-22 a n Practice 4: Overview 4-23 U v IT Bangla Ltd 5 Using Oracle-Supplied Packages in Application Development Objectives 5-2 Using Oracle-Supplied Packages 5-3 List of Some Oracle-Supplied Packages 5-4 s. How the DBMS_OUTPUT Package Works 5-5 e at Interacting with Operating System Files 5-6 fili File Processing Using the UTL_FILE Package 5-7 f a s Exceptions in the UTL_FILE Package 5-8 t r i FOPEN and IS_OPEN Function Parameters 5-9 o d/ Using UTL_FILE: Example 5-10 n o a Generating Web Pages with the HTP Package 5-12 e t e n s e acl Using the HTP Package Procedures 5-13 e li c Or Creating an HTML File with iSQL*Plus 5-14 a b l 1, Using UTL_MAIL 5-15 s f e r n 201 Installing and Using UTL_MAIL 5-16 n -tr a © Sending E-Mail with a Binary Attachment 5-17 a n o ht Sending E-Mail with a Text Attachment 5-19 a s e ฺ g h d pyri DCBreMaSt_inSgC aH EJoDbU L E5R-2 P3 ackage 5-21 a ฺ n e t) n t G u i o g l d e C Creating a Job with In-Lineb PaarnameterSs t u5-24 bited. CCrreeaattiinngg aa JJoombb Ufeosre inarg P@ aro Puigtrrsoagmer a wtmiht hi 5As - r2g5u ments 5-26 hi a ro CreaAtilnig (a Job Using a Schedule 5-27 p eSre tting the Repeat Interval for a Job 5-28 n m e o A Creating a Job Using a Named Program and Schedule 5-29 ti d uM Managing Jobs 5-30 b ri Data Dictionary Views 5-31 t s di Summary 5-32 or Practice 5: Overview 5-33 n o cti 6 Dynamic SQL and Metadata u d Objectives 6-2 o r Execution Flow of SQL 6-3 p e r Dynamic SQL 6-4 d e Native Dynamic SQL 6-5 z ri Using the EXECUTE IMMEDIATE Statement 6-6 o h Dynamic SQL with a DDL Statement 6-7 t u a Dynamic SQL with DML Statements 6-8 n U Dynamic SQL with a Single-Row Query 6-9 Dynamic SQL with a Multirow Query 6-10 vi IT Bangla Ltd Declaring Cursor Variables 6-11 Dynamically Executing a PL/SQL Block 6-12 Using Native Dynamic SQL to Compile PL/SQL Code 6-13 Using the DBMS_SQL Package 6-14 . s Using DBMS_SQL with a DML Statement 6-15 e at Using DBMS_SQL with a Parameterized DML Statement 6-16 fili Comparison of Native Dynamic SQL and the DBMS_SQL Package 6-17 f a s DBMS_METADATA Package 6-18 t r i Metadata API 6-19 o / d Subprograms in DBMS_METADATA 6-20 n o a FETCH_xxx Subprograms 6-21 e t e n s e acl SET_FILTER Procedure 6-22 e li c Or Filters 6-23 a b l 1, Examples of Setting Filters 6-24 s f e r n 1 a 20 Programmatic Use: Example 1 6-25 n -tr © Programmatic Use: Example 2 6-27 a n o ht Browsing APIs 6-29 a s e ฺ g h d pyri BSuromwmsianrgy A P6-Is3:2 E xamples 6a-ฺ30n e t) n t G u i o g l d e C Practice 6: Overviewb a 6n-33 S t u bited. 7 Desigmn Ce oenrs@ideuirtas tieo ntsh fiosr PL/SQL Code prohi e r A liOS (tbaajnedctaivrdeisz i n 7g- C2 onstants and Exceptions 7-3 n m e Standardizing Exceptions 7-4 o A utiM d Standardizing Exception Handling 7-5 b ri Standardizing Constants 7-6 t s di Local Subprograms 7-7 r Definer’s Rights Versus Invoker’s Rights 7-8 o n Specifying Invoker’s Rights 7-9 o ti Autonomous Transactions 7-10 c u d Features of Autonomous Transactions 7-11 o r Using Autonomous Transactions 7-12 p e RETURNING Clause 7-13 r d e Bulk Binding 7-14 z ri Using Bulk Binding 7-15 o h Bulk Binding FORALL: Example 7-16 t u a Using BULK COLLECT INTO with Queries 7-18 n U Using BULK COLLECT INTO with Cursors 7-19 Using BULK COLLECT INTO with a RETURNING Clause 7-20 vii IT Bangla Ltd Using the NOCOPY Hint 7-21 Effects of the NOCOPY Hint 7-22 NOCOPY Hint Can Be Ignored 7-23 PARALLEL_ENABLE Hint 7-24 . s Summary 7-25 e t a Practice 7: Overview 7-26 fili f a s 8 Managing Dependencies t r i Objectives 8-2 o d/ Understanding Dependencies 8-3 n o a Dependencies 8-4 e t e n s Oracl LAo Sccael nDaerpioe nodf eLnocciaels D e8p-5e ndencies 8-7 a b l e li c e Displaying Direct Dependencies by Using USER_DEPENDENCIES 8-8 e r 1, s f n 1 Displaying Direct and Indirect Dependencies 8-9 a 20 Displaying Dependencies 8-10 o n -tr ht© Another Scenario of Local Dependencies 8-11 a s a n e ฺ pyrig AU nSdceernstaarniod ionfg L Roceaml oNtaem Dinegp eDnedpeenncdiaeeฺsn n c i8ee-st1 )3 8h-1n 2t G u i d Co Concepts of Remote Dependenncgiels 8-15u d e b a S t bited. RREeMmOoTteE _DDeEpPmeEnNdeDeEencNriCe@Is EauiStns_dM eTO iDmtEhe i PSsat arammpe Mteor d e8 - 1 86- 17 hi Remote Praocedure B Compiles at 8:00 a.m. 8-19 pro eLro caAl lPi ro(cedure A Compiles at 9:00 a.m. 8-20 n m e Execute Procedure A 8-21 o A ti d Remote Procedure B Recompiled at 11:00 a.m. 8-22 uM b ri Execute Procedure A 8-23 t s Signature Mode 8-24 di r Recompiling a PL/SQL Program Unit 8-25 o n Unsuccessful Recompilation 8-26 o ti Successful Recompilation 8-27 c u Recompilation of Procedures 8-28 d o r Packages and Dependencies 8-29 p e Summary 8-31 r d Practice 8: Overview 8-32 e z ri o h t u a n U viii IT Bangla Ltd 9 Manipulating Large Objects Objectives 9-2 What Is a LOB? 9-3 Contrasting LONG and LOB Data Types 9-5 . s Anatomy of a LOB 9-6 e t a Internal LOBs 9-7 ffili Managing Internal LOBs 9-8 a s What Are BFILEs? 9-9 t i r Securing BFILEs 9-10 o / d A New Database Object: DIRECTORY 9-11 n o a e t e Guidelines for Creating DIRECTORY Objects 9-12 n s e acl Managing BFILEs 9-13 e li c Or Preparing to Use BFILEs 9-14 a b l e r 1, Populating BFILE Columns with SQL 9-15 s f n 1 a 20 Populating a BFILE Column with PL/SQL 9-16 n -tr o © Using DBMS_LOB Routines with BFILEs 9-17a n ht Migrating from LONG to LOB 9-18 a s e ฺ g h d pyri DBMS_LOB Package 9-20 a ฺ n e t) n t G u i o DBMS_LOB.READ and DBMg lS_LOB.WdRIe TE 9-23 C n u bited. IPnoitpiaulmlizaitniengg eL LrOOB@B C Cuiotolsulbumema nntss hA i d9sd- e2d5S tto a Table 9-24 hi Upda ating LOB by Using DBMS_LOB in PL/SQL 9-26 ro A liS (electing CLOB Values by Using SQL 9-27 p e r n m e Selecting CLOB Values by Using DBMS_LOB 9-28 o A ti d Selecting CLOB Values in PL/SQL 9-29 uM b ri Removing LOBs 9-30 t s di Temporary LOBs 9-31 or Creating a Temporary LOB 9-32 n Summary 9-33 o cti Practice 9: Overview 9-34 u d o r 10 Creating Triggers p e r Objectives 10-2 d e Types of Triggers 10-3 z ri Guidelines for Designing Triggers 10-4 o h Creating DML Triggers 10-5 t u a Types of DML Triggers 10-6 n U Trigger Timing 10-7 Trigger-Firing Sequence 10-8 ix IT Bangla Ltd Trigger Event Types and Body 10-10 Creating a DML Statement Trigger 10-11 Testing SECURE_EMP 10-12 Using Conditional Predicates 10-13 . s Creating a DML Row Trigger 10-14 e at Using OLD and NEW Qualifiers 10-15 fili Using OLD and NEW Qualifiers: Example Using AUDIT_EMP 10-16 f a s Restricting a Row Trigger: Example 10-17 t r i Summary of the Trigger Execution Model 10-18 o d/ Implementing an Integrity Constraint with a Trigger 10-19 n o a INSTEAD OF Triggers 10-20 e t e n s acl Creating an INSTEAD OF Trigger 10-21 e li c e Or Comparison of Database Triggers and Stored Procedures 10-24 a b l Comparison of Database Triggers and Oracle Forms Triggers 10-25 e r 1, s f n 1 Managing Triggers 10-26 a 20 Removing Triggers 10-27 o n -tr ht© Testing Triggers 10-28 a s a n e ฺ pyrig SPuramcmticaer y1 0 :1 O0-v2e9r view 10-30 a ฺ n e t) h n t G u i d Co n g l u d e b a S t bited. 11 AOpbpjelcictiavteiso n s1m 1fo-e2r eTrrig@geuirtss e t h i s hi Creating Daatabase Triggers 11-3 ro CreaAtilnig (Triggers on DDL Statements 11-4 p e r n m e Creating Triggers on System Events 11-5 o A ti d LOGON and LOGOFF Triggers: Example 11-6 uM b ri CALL Statements 11-7 t s di Reading Data from a Mutating Table 11-8 r Mutating Table: Example 11-9 o n Benefits of Database Triggers 11-11 o ti Managing Triggers 11-12 c u Business Application Scenarios for Implementing Triggers 11-13 d o r Viewing Trigger Information 11-14 p e Using USER_TRIGGERS 11-15 r d Listing the Code of Triggers 11-16 e z ri Summary 11-17 o h Practice 11: Overview 11-18 t u a n U x