Using Procedures & Functions Oracle Database PL/SQL 10g Programming

1. Using Procedures & FunctionsOracle Database PL/SQL 10g Programming Chapter 9

2. Using Procedures & Functions • Data Dictionary • Subprograms • Definer Rights • Invoker Rights • Purity Levels • Multiple-Valued Functions • Shared Pool Oracle Database PL/SQL 10g Programming (Chapter 9)

3. Using Procedures & FunctionsData Dictionary: Locations • Stored Program Catalog Views: • Definition views: • Stored Program Source – USER_SOURCE • Stored Program Information – USER_PROCEDURES • Stored Program Header – USER_ARGUMENTS • Stored Program Settings – USER_PLSQL_OBJECT_SETTINGS • Dependency view: USER_DEPENDENCIES Oracle Database PL/SQL 10g Programming (Chapter 9)

4. Using Procedures & FunctionsData Dictionary: Locations • Object Catalog Views: • Object definitions: USER_SOURCE • Table definitions: • Table Structure – USER_TABLES • Table Columns – USER_TAB_COLS • Trigger definitions: • Trigger Structure – USER_TRIGGERS • Trigger Columns – USER_TRIGGER_COLS Oracle Database PL/SQL 10g Programming (Chapter 9)

5. Using Procedures & FunctionsData Dictionary: Query Status SQL> SELECT object_name 2 , object_type 3 , status 4 FROM user_objects; Oracle Database PL/SQL 10g Programming (Chapter 9)

6. Using Procedures & FunctionsData Dictionary: Query Tables SQL> SELECT table_name 2 , column_id 3 , column_name 4 , data_type 5 FROM user_tab_cols 6 ORDER BY table_name 7 , column_id; Oracle Database PL/SQL 10g Programming (Chapter 9)

7. Using Procedures & FunctionsData Dictionary: Query Programs SQL> SELECT line 2 , text 3 FROM user_source 4 WHERE name = UPPER('program_name‘); Oracle Database PL/SQL 10g Programming (Chapter 9)

8. Using Procedures & FunctionsPackage Specification Subprograms: Rules • Stored Package Specification Programs: • Are stored in compiled p-code, like Java byte code. • Can be called by any other block. • Are modularized into smaller program units. • Compiled p-code can be pinned in the SGA for faster execution. • Can be overloaded. Oracle Database PL/SQL 10g Programming (Chapter 9)

9. Using Procedures & FunctionsPackage Subprograms: Stored • Package stored subprograms: • Are defined as procedures in the package specification. • Are defined as functions in the package specification. • Can use schema defined types from the package specification. • Can use schema defined SQL types. • Can use locally defined types from package body ONLY in their internal implementation: • Locally defined types can declared in local subprogram as variables. • Locally defined types cannot be used as parameter data types. • Locally defined types cannot be used as function return data types. • Can be called from any schema PL/SQL program. • Stored functions CANNOT be called from SQL when they return a PL/SQL data type. Oracle Database PL/SQL 10g Programming (Chapter 9)

10. Using Procedures & FunctionsPackage Subprograms: Stored • Package stored subprogram referencing: • Do not require forward referencing: • The specification publishes their definitions. • The body relies on the specification to avoid forward referencing issues. • Package stored subprogram overloading: • Signature rules: • The list of formal parameters must differ by either: • The position and type of formal parameters. • The number of formal parameters. • The function signature may change based on the return data type. Oracle Database PL/SQL 10g Programming (Chapter 9)

11. Using Procedures & FunctionsPackage Body (Local) Subprograms: Rules • Local Package Body Programs: • Are stored in compiled p-code, like Java byte code. • Can be called ONLY by package blocks. • Tightly coupled design. • Compiled p-code cannot be pinned in the SGA. • Can be overloaded only within the same block. Oracle Database PL/SQL 10g Programming (Chapter 9)

12. Using Procedures & FunctionsPackage Subprograms: Local • Package local subprograms: • Are defined as procedures in the package body. • Are defined as functions in the package body. • Can use locally defined types from the package body. • Can use schema defined types from package specifications. • Can use schema defined SQL types. • Can ONLY be called from a package body program unit. • Package local subprogram referencing: • Require forward referencing, like anonymous block subprograms. • Package local subprogram overloading: • Follows the same rules as stored subprograms. Oracle Database PL/SQL 10g Programming (Chapter 9)

13. Using Procedures & FunctionsPackage Subprograms: Dependencies • Timestamp Model (default): • Checks to make sure that dependents have an early compilation timestamp. • Cannot resolve distributed timestamps in different timezones. • Signature Model: • Checks to make sure that the signature for functions and procedures do not change between compilations. • Works in distributed configurations without impacts of region timezones. Oracle Database PL/SQL 10g Programming (Chapter 9)

14. Using Procedures & FunctionsPackage Subprograms: Serially Reusable • Non-Serially Reusable (default): • Runtime state is kept in process memory between calls during a session. • Requires memory in the SGA for all logged-on users. • Serially Reusable: • Runtime state is refreshed after each database call. • Requires memory in the SGA for users concurrently calling the same program. Oracle Database PL/SQL 10g Programming (Chapter 9)

15. Using Procedures & FunctionsDefiner Rights: Description • Definer rights • Is the default when creating stored programs. • Means that the stored program executes with the same privileges as the defining user. • Can mean that calling the stored programs lets it run against any schema level data. • Typically means that users only access a slice of data in any schema, like a private virtual database. Oracle Database PL/SQL 10g Programming (Chapter 9)

16. Using Procedures & FunctionsDefiner Rights: Depiction Oracle Database PL/SQL 10g Programming (Chapter 9)

17. Using Procedures & FunctionsInvoker Rights: Description • Invoker rights • Is the override when creating stored programs. • Means that the stored program executes with the local privileges, which generally differ from the definer’s privileges. • Typically means that users only access their own schema data, like a distributed or local database. Oracle Database PL/SQL 10g Programming (Chapter 9)

18. Using Procedures & FunctionsInvoker Rights: Depiction Oracle Database PL/SQL 10g Programming (Chapter 9)

19. Using Procedures & FunctionsPurity Level: Defined • Purity levels restrict the behavior of stored programs. • Purity level guarantees are optional beginning with Oracle 8i. • Purity levels are: • Writes no database states (WNDS), which disallows DML statements, like INSERT, UPDATE and DELETE. • Reads no database states (RNDS), which disallows DQL statements, like SELECT. • Writes no package states (WNPS), which disallows changes to package variables by assignment operations (including the FETCHcursorINTOvariable syntax). • Reads no package states (RNPS), which disallows assigning any package variable values. Oracle Database PL/SQL 10g Programming (Chapter 9)

20. Using Procedures & FunctionsMultiple-Valued Functions: Defined A multiple-valued function exists when the return type of a stored or local function returns a system reference cursor, or a user-defined data type that has multiple rows. Oracle Database PL/SQL 10g Programming (Chapter 9)

21. Using Procedures & FunctionsMultiple-Valued Functions: Rules • A multiple-valued functions must return the value to the same type variable, which is done by: • Assignment in a calling PL/SQL block. • Assignment to a bind variable using a system reference cursor. • A multiple-valued function returning a PL/SQL variable can ONLY be used inside another PL/SQL block. Oracle Database PL/SQL 10g Programming (Chapter 9)

22. Using Procedures & FunctionsShared Pool: Pinning • Pinning in the SGA shared pool can accelerate and guarantee behavior of frequently called stored programs: • This places the p-code in the shared pool. • This prevents the least used algorithm from removing it inbetween calls to it. • The DBMS_SHARED_POOL.KEEP enables pinning a stored program into the SGA. • The DBMS_SHARED_POOL.UNKEEP removes a pinned stored program from the SGA. Oracle Database PL/SQL 10g Programming (Chapter 9)

23. Summary • Data Dictionary • Subprograms • Definer Rights • Invoker Rights • Purity Levels • Multiple-Valued Functions • Shared Pool Oracle Database PL/SQL 10g Programming (Chapter 9)