making the most of the best of oracle pl/sql · filename_from_demo_zip.sql download and use any of...
TRANSCRIPT
Oracle PL/SQL Programming
Steven Feuerstein [email protected]
Making the Most of the Best
of Oracle PL/SQL
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 2
Making the Most of the Best of Oracle PL/SQL - Agenda • Get ready! Profiling, timing and memory mgt
• The Oracle Database 10g compiler – Optimization, warnings, conditional compilation
• Data caching techniques – Oracle Database 11g Function Result Cache
• Bulk Processing of SQL in PL/SQL – BULK COLLECT and FORALL
• Table Functions
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 3
Agenda - continued
• Dynamic SQL in PL/SQL – NDS and DBMS_SQL
– Oracle Database 11g enhancements
• "Must Know" error management features – DBMS_UTILITY functions
– DBMS_ERRLOG and LOG ERRORS
• PL/Scope - in-depth code analysis
• Say Goodbye to Hard-coding – Hide implementations
– Extreme Modularization
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 4
How to benefit most from this training
• Watch, listen, ask questions, focus on concepts and principles.
• Download and use any of my training materials:
You have my permission to use all these materials to do internal trainings and build your own applications. – But remember: they are not production ready.
– You must test them and modify them to fit your needs.
filename_from_demo_zip.sql
Download and use any of my scripts (examples, performance scripts, reusable code) from the same location: the demo.zip file.
http://www.ToadWorld.com/SF PL/SQL Obsession
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 5
Websites for PL/SQL Developers
www.plsqlchallenge.com Daily PL/SQL quiz with weekly and monthly prizes
www.plsqlchannel.com 27+ hours of detailed video training on Oracle PL/SQL
www.stevenfeuerstein.com Monthly PL/SQL newsletter
www.toadworld.com/SF Quest Software-sponsored portal for PL/SQL developers
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 6
To Optimize PL/SQL, You Need To...
• Profile execution of code – Identify performance bottlenecks
• Calculate elapsed time of execution – Critical for granular analysis of performance
and comparison of performance between different implementations of same program
• Manage memory – Most optimizations involve a tradeoff: less
CPU, more memory.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 7
Profiling execution of PL/SQL code
• First of all, is it SQL or is it PL/SQL? – Most of the time, SQL is the problem. – Use the v$SQL view to answer the question.
• Profile the execution of your PL/SQL program units to identify bottlenecks. – Which lines consume the most CPU? – Which subprograms take the most time?
• Two profilers: – DBMS_PROFILER: line by line performance – DBMS_HPROF: hierarchical profiler, rollup to
program units
cachedPLSQL.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 8
DBMS_PROFILER
• Requires EXECUTE privilege on DBMS_PROFILER.
• Must create tables: $RDBMS_ADMIN/proftab.sql
• Run queries (or use your IDE) to view results.
• This profiler also provides raw data for code coverage analysis.
BEGIN DBMS_OUTPUT.PUT_LINE ( DBMS_PROFILER.START_PROFILER ( 'my_application ' || TO_CHAR (SYSDATE, 'YYYYMDD HH24:MI:SS') )); run_your_application; DBMS_PROFILER.STOP_PROFILER; END;
profrep.sql
dbms_profiler_example.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 9
DBMS_HPROF
• Requires EXECUTE privilege on DBMS_HPROF.
• Must create tables: $RDBMS_ADMIN/dbmshptab.sql
• Run queries against tables or call plshprof to generate HTML reports
11g
DECLARE l_runid NUMBER; BEGIN DBMS_HPROF.start_profiling ('HPROF_DIR', 'run1'); run_application; DBMS_HPROF.stop_profiling (); l_runid := DBMS_HPROF.analyze (location => 'HPROF_DIR' , filename => 'run1.trc' , run_comment => 'First run'); END;
$ plshprof -output hprof run1.trc
dbms_hprof_example.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 10
Calculating Elapsed Time of Programs
• Many options for analyzing Oracle performance: TKPROF, SET TIMING ON, etc. – But they usually don't offer the granularity I need for my PL/SQL
performance analysis.
• Oracle offers DBMS_UTILITY.GET_TIME and GET_CPU_TIME (10g) to compute elapsed time down to the hundredth of a second. – Can also use SYSTIMESTAMP
DECLARE l_start_time PLS_INTEGER; BEGIN l_start_time := DBMS_UTILITY.get_time; -- Do stuff here... DBMS_OUTPUT.put_line ( DBMS_UTILITY.get_time – l_start_time); END;
sf_timer.* get_time.sql
plvtmr.* plvtmr_ts.pkg
tmr.ot thisuser*.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 11
What’s the Problem?
• What will happen when I run this code?
DECLARE l_strings DBMS_SQL.varchar2a; BEGIN FOR indx IN 1 .. 2 ** 31 - 1 LOOP l_strings (indx) := RPAD ('abc', 32767, 'def'); END LOOP; END; /
memory_error.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 12
Analyze Memory Usage of PL/SQL Code
• It is certainly possible to write PL/SQL code that consumes so much memory, it kills a user's session.
– It's quite easy to do, in fact.
• As you work with more advanced features, like collections and FORALL, you will need to pay attention to memory, and make adjustments.
• First, let's review how Oracle manages memory at run-time.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 13
System Global Area (SGA) of RDBMS Instance
PL/SQL Runtime Memory Architecture
Shared Pool
Large Pool
Reserved Pool
show_emps calc_totals upd_salaries
Select *
from emp
Shared SQL
Pre-parsed Update emp
Set sal=...
Library cache
Session 1 memory UGA – User Global Area PGA – Process Global Area
emp_rec emp%rowtype;
tot_tab pkg.tottabtype;
Session 2 memory UGA – User Global Area PGA – Process Global Area
emp_rec emp%rowtype;
tot_tab pkg.tottabtype; Session 1 Session 2
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 14
How PL/SQL uses the SGA, PGA and UGA
• The SGA contains information that can be shared across schemas connected to the instance. – From the PL/SQL perspective, this is limited to package static
constants.
• The Process Global Area contains session-specific data that is released when the current server call terminates. – Local data
• The User Global Area contains session-specific data that persists across server call boundaries – Package-level data
PACKAGE Pkg is Nonstatic_Constant CONSTANT PLS_INTEGER := My_Sequence.Nextval; Static_Constant CONSTANT PLS_INTEGER := 42; END Pkg;
top_pga.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 15
Calculating PGA and UGA Consumption
• Oracle keeps track of and shows the PGA and UGA consumption for a session in the v_$sesstat dynamic view.
• With the correct privileges, PL/SQL developers can analysis their code's memory usage.
show_pga_uga.sql
grantv$.sql
plsql_memory.pkg
plsql_memory_demo.sql
SELECT n.name, s.VALUE FROM sys.v_$sesstat s, sys.v_$statname n WHERE s.statistic# = n.statistic# AND s.sid = my_session.sid AND n.name IN ('session uga memory', 'session pga memory')
BEGIN plsql_memory.start_analysis; run_my_application; plsql_memory.show_memory_usage; END;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 16
Tips for managing memory
• Use LIMIT clause with BULK COLLECT.
• Use varrays with BULK COLLECT to declaratively guard against "memory creep."
• Use NOCOPY hint when passing IN OUT collections.
• Be very careful about defining variables at the package level. – Memory will not be released when the block
terminates.
• Use pipelined table functions. bulklimit.sql
varray_collection_limit.sql
nocopy*.tst
tabfunc_pipelined.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 17
Fully Leverage the Oracle10g PL/SQL Compiler
• Oracle demonstrated its long-term commitment to PL/SQL with the release of Oracle Database 10g – Many new features and a complete re-write of
the compiler.
• Automatic, transparent optimization of code
• Compile-time warnings framework to help you improve the quality of your code.
• Conditional compilation: you decide what code should be compiled/ignored!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 18
The Optimizing Compiler • The PL/SQL compiler now has the ability to
automatically optimize your code. – The compiler rearranges your code.
– Compile time increases, runtime performance improves.
• You choose the level of optimization : – 0 Pre-10g compilation without optimization
– 1 Smaller scale change, less impact on compile times
– 2 Most aggressive, maximum possible code transformations, biggest impact on compile time. [default]
– 3 (Oracle11g) In-lining of local subprograms, in addition to all the optimization performed at level 2
• Stick with the default, unless you have a clear need for an exception.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 19
The PL/SQL Optimizer: High Level View
• The optimizer takes advantage of "freedoms" to re-order the execution of statements. – In essence, changing the route that the runtime engine
takes to get from point A to point B in your code.
• Some examples: – Unless otherwise specified, the operands of an expression
operator may be evaluated in any order. – Operands of a commutative operator may be commuted. – The actual arguments of a call or a SQL statement may be
evaluated in any order (including default actual arguments).
• Optimization does not change the logical behavior of your code. – Optimization should not, for example, cause any of your
regression tests to suddenly fail!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 20
Some Examples
... A + B ...
...
... A + B ...
T := A + B; ... T ... ... ... T ...
T is a generated variable. We never see
it. And one operation is saved.
for i in 1 .. 10 loop A := B + C; ... end loop;
A := B + C; for i in 1 .. 10 loop ... end loop;
Automatic relocation of a loop invariant.
Avoid repetitive computations.
10g_optimize_cfl.sql
FOR rec in (SELECT ...) LOOP ... do stuff END LOOP;
SELECT ... BULK COLLECT INTO ... FROM ...
Execute cursor FOR loop
at BULK COLLECT
levels of performance.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 21
Things to Keep in Mind
• The PL/SQL runtime engine will always execute your subprograms, even if the optimizer detects that the results of that subprogram call are "not needed." – Exception: DETERMINISTIC functions in 11g
• You cannot rely on a specific order of evaluation of arguments in a subprogram call or even when package initialization takes place. – The compiler will even avoid initialization of a package
if it not needed (using a TYPE for example).
my_function () * NULL
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 22
Changing the optimizer level
• Oracle retains optimizer settings on a module-by-module basis.
– When you recompile a particular module with non-default settings, the settings will "stick," allowing you to recompile later using REUSE SETTINGS. For example:
• and then:
ALTER PROCEDURE bigproc COMPILE PLSQL_OPTIMIZE_LEVEL = 1;
ALTER PROCEDURE bigproc COMPILE REUSE SETTINGS;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 23
Oracle11g In-lining optimization
• A new level, 3, tells Oracle to automatically search out opportunities to "inline" code for nested subprograms.
– This means that a pointer to the subprogram is replaced with the implementation of the subprogram.
• Oracle's own tests have shown 10-20% performance improvement.
– Depends on how many local modules you create and how often they are used.
• Note: compile code size increases.
ALTER SESSION SET PLSQL_OPTIMIZE_LEVEL = 3;
11g_inline*.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 24
Selective Inlining with PRAGMA
• You can also keep the optimization level at 2 and request inlining explicitly for specific subprogram invocations with a new INLINE pragma.
• Inlining applies to the following statements: – Assignment, CALL, conditional, CASE, CONTINUE-WHEN,
EXECUTE IMMEDIATE, EXIT-WHEN, LOOP, RETURN
• You can also request inlining for all executions of the subprogram by placing the PRAGMA before the declaration of the subprogram.
• Inlining, like NOCOPY, is a request. – Under some circumstances, inlining will not take place.
11g
PRAGMA INLINE (subprogram, 'YES')
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 25
Inlining Could Slow Down Code
• Oracle warns that inlining occurs early in the optimization process and may "preclude later, more powerful optimizations."
• If you find that inlining is slowing down a program unit, profile execution to identify subprograms for which to turn off inlining. – Oracle recommends the new-to-11g hierarchical
profiler, DBMS_HPROF.
• Selectively disable inlining with pragma:
PRAGMA INLINE (subprogram, 'NO')
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 26
Learn more about the PL/SQL optimizer
• PL/SQL Just Got Faster – Explains the workings of the PL/SQL compiler and runtime system and
shows how major improvements on this scale are indeed possible.
• Freedom, Order, and PL/SQL Optimization – Intended for professional PL/SQL programmers, explores the use and
behavior of the new compiler.
• PL/SQL Performance — Debunking the Myths – Re-examines some old notions about PL/SQL performance.
• PL/SQL Performance Measurement Harness – Describes a performance experiment whose conclusion is the large factors
quoted above. Oracle provides a downloadable kit to enable you to repeat the experiment yourself.
http://www.oracle.com/technology/tech/pl_sql/htdocs/new_in_10gr1.htm
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 27
Warnings help you build better code
• Your code compiles without errors. Great, you can run that program!
• But does it use the PL/SQL language optimally?
• In Oracle 10g, Oracle added a compile-time warnings framework. – Automatically informs you of ways to improve the
quality or performance of your code.
• All warnings shown in Error Messages manual, with the PLW prefix.
http://tahiti.oracle.com
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 28
Enable and Disable Warnings
• To use compiler warnings, you must turn them on for session or for a particular program unit.
– By default, warnings are disabled.
• Can specify individual warnings or categories.
ALTER SESSION [ENABLE | DISABLE |ERROR]: [ALL|SEVERE|INFORMATIONAL|PERFORMANCE|warning_number] REM To enable all warnings in your session: ALTER SESSION SET plsql_warnings = 'enable:all‘; REM If you want to enable warning message number 06002 and all warnings in REM the performance category, and treat 5005 as a "hard" compile error: ALTER PROCEDURE my_procedure SET plsql_warnings = 'enable:06002', 'enable:performance', 'ERROR:05005';
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 29
Checking for Warnings
• The USER_ERRORS data dictionary view shows both "hard" errors and compilation warnings.
• Use the SHOW ERRORS command in SQL*Plus.
• IDEs will usually display warnings within the edit window.
• Or run your own query against USER_ERRORS.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 30
Example: check for unreachable code • There may be lines of code that could never, ever
execute.
SQL> CREATE OR REPLACE PROCEDURE unreachable_code IS 2 x NUMBER := 10; 3 BEGIN 4 IF x = 10 THEN 5 x := 20; 6 ELSE 7 x := 100; -- unreachable code 8 END IF; 9 END unreachable_code; 10 / SP2-0804: Procedure created with compilation warnings SQL> show err Errors for PROCEDURE UNREACHABLE_CODE: LINE/COL ERROR -------- ------------------------------------- 7/7 PLW-06002: Unreachable code
plw6002.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 31
New Warnings in Oracle Database 11g
• PLW-6017: something's going to raise an error! – Such as VARCHAR2(1) := 'abc'....FINALLY!
• PLW-6009: OTHERS exception handler does not re-raise an exception.
• More feedback on impact of optimization – PLW-6007: Notification that entire subprograms were
removed
• PLW-7205: warning on mixed use of integer types – Namely, SIMPLE_INTEGER mixed with PLS_INTEGER and
BINARY_INTEGER
• PLW-7206: unnecessary assignments • Lots of PRAGMA INLINE-related warnings
plw*.sql files
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 32
Finally, Oracle warns me of too-large value
• One big frustration I have had with compile-time warnings is that it did not flag code like you see above. What could be more basic?
• This is finally addressed – sort of – in Oracle11g with the PLW-06017 warning.
CREATE OR REPLACE PROCEDURE plw6017 IS c VARCHAR2 (1) := 'abc'; BEGIN
plw6017.sql
PLW-06017: an operation will raise an exception
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 33
Treating a warning as "hard" compile error
• You might identify a warning that reflects such bad coding practices, that you want to ensure it never makes its way into production code.
– Just set the warning as an error and stop the use of that program "in its tracks."
• "Function does not return value" is a prime example.
plw5005.sql
ALTER SESSION SET PLSQL_WARNINGS='ERROR:5005'
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 34
Watch out for "false negatives" and "nuisances" warnings
• The check for unreachable code is not very useful/reliable prior to Oracle11g.
– Shows as "unreachable" code that is removed by the optimizer.
• You might be overwhelmed by warnings about which you don't really care.
– Example: missing AUTHID clause
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 35
Conclusions - Compile-time Warnings
• Review the available warnings. Identify those which of greatest importance to you.
– And with each new release of Oracle check for additions.
• Consider setting up scripts to enable different sets of warnings to match different development scenarios and to ignore those "nuisance" warnings.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 36
Conditional Compilation
• Compile selected parts of a program based on conditions you provide with various compiler directives.
• Conditional compilation will allow you to: – Write code that will compile and run under different
versions of Oracle (relevant for future releases). – Run different code for test, debug and production
phases. That is, compile debug statements in and out of your code.
– Expose private modules for unit testing.
• First released in Oracle Database 10g Release 2 – Also implemented in later patch sets of 10gR1, plus 9iR2
(contact Oracle Support for details)
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 37
A finely-nuanced feature of PL/SQL
• Conditional compilation affects how your code is compiled and therefore executed.
• It is not something to employ casually.
– This training will serve as an introduction.
• Before using conditional compilation, check out OTN's detailed whitepaper on the topic.
– 100 pages covering all common use cases
– See URL below or search for "conditional compilation white paper".
http://bit.ly/eXxJ9Q
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 38
Three types of compiler directives
• Inquiry directives: $$identifier – Use the $$identifier syntax to refer to conditional
compilation flags. These inquiry directives can be referenced within an $IF directive, or used independently in your code.
• Selection directives: $IF – Use the $IF directive to evaluate expressions and
determine which code should be included or avoided. – Can reference inquiry directives and package static
constants. • Error directives: $ERROR
– Use the $ERROR directive to report compilation errors based on conditions evaluated when the preprocessor prepares your code for compilation.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 39
Example: toggle inclusion of tracing • Set up conditional compilation of debugging and
tracing with special "CC" flags that are placed into the compiler settings for a program. – Only integer and Boolean values are allowed.
ALTER SESSION SET PLSQL_CCFLAGS = 'oe_debug:true, oe_trace_level:10' / CREATE OR REPLACE PROCEDURE calculate_totals IS BEGIN $IF $$oe_debug AND $$oe_trace_level >= 5 $THEN DBMS_OUTPUT.PUT_LINE ('Tracing at level 5 or higher'); $END application_logic; END calculate_totals; /
cc_debug_trace.sql
cc_expose_private.sql
cc_max_string.sql
cc_plsql_compile_settings.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 40
Access to post-processed code • You can display or retrieve post-processed code with the
DBMS_PREPROCESSOR package. – Oracle is careful to preserve both horizontal and vertical
whitespace so runtime stack and error information correlates to your actual source code.
CREATE OR REPLACE PROCEDURE post_processed IS BEGIN $IF $$PLSQL_OPTIMIZE_LEVEL = 1 $THEN -- Slow and easy NULL; $ELSE -- Fast and modern and easy NULL; $END END post_processed; /
BEGIN DBMS_PREPROCESSOR.PRINT_POST_PROCESSED_SOURCE ('PROCEDURE', USER, 'POST_PROCESSED'); END; / PROCEDURE post_processed IS BEGIN -- Fast and modern and easy NULL; END post_processed;
cc_postprocessed.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 41
Error directive example • If my program has been compiled with
optimization level 1 (less aggressive) or 0 (disabled), then raise an error. – You can in this way add "meta-requirements" to
your code definitions.
SQL> CREATE OR REPLACE PROCEDURE long_compilation 2 IS 3 BEGIN 4 $IF $$plsql_optimize_level < 2 5 $THEN 6 $error 'Program must be compiled with full optimization' $end 7 $END 8 NULL; 9 END long_compilation; 10 /
cc_opt_level_check.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 42
Using DBMS_DB_VERSION
• This package, present in any Oracle Database version supporting conditional compilation, contains a set of Boolean constants showing absolute and relative version information.
PROCEDURE insert_rows ( rows_in IN otn_demo_aat ) IS BEGIN $IF DBMS_DB_VERSION.VER_LE_10_1 $THEN BEGIN ... FORALL indx IN 1 .. l_dense.COUNT INSERT INTO otn_demo VALUES l_dense (indx); END; $ELSE FORALL indx IN INDICES OF rows_in INSERT INTO otn_demo VALUES rows_in (indx); $END
cc_bf_or_number.sql
cc_version_check.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 43
Conclusions – Conditional Compilation
• Conditional compilation is a very powerful and useful feature.
• It is not terribly difficult to learn, but it is hard for developers to be confident of working with and maintaining code that contains "$" syntax elements.
• Most important: keep it in mind when you encounter a clear and compelling use case, such as writing code that must run under multiple versions of Oracle.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 44
Compiler Improvements - Summary
• Optimizer – Go with the default and enjoy the performance!
• Compile-time warnings – Try them out, see how much value you can extract
from it.
• Conditional compilation – Lots of potential, mainly for use into the future
– Smart tool support needed to make it feasible and maintainable (one's code becomes very hard to read)
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 45
Data Caching in PL/SQL
• A time-honored technique for improving performance.
• Store data that doesn’t change for some period of time in a location that can be accessed more quickly than the source.
• The SGA is an enormous, complex cache for the entire database instance.
• But there are other caches (in SGA and PGA) we can leverage in our PL/SQL code.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 46
Data Caching Options
• Functions declared as DETERMINISTIC
• PGA caching
– Used most effectively with collections
– Accessing PGA memory generally more efficient than SGA, especially if executing SQL.
• Oracle Database 11g Function Result Cache
– The best caching technique and the most important new feature in 11g for PL/SQL developers.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 47
DETERMINSTIC Functions
• A function is deterministic if the value it returns is determined completely by its inputs (IN arguments). – In other words, no side effects.
• Add the DETERMINISTIC keyword to your function to enable optimizations: – Function-based indexes
– Cache results in scope of a query
• Don’t lie! Oracle will not reject your use of the keyword, even if it isn’t true.
deterministic.sql
deterministic_in_plsql.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 48
PGA-Based Caching
• When you declare variables at the package level, their state persists in your session.
– A PGA-based cache, specific to each session.
• And if you declare a collection at the package level, you can cache multiple rows of data.
• Not a reliable technique for Web-based (usually stateless) applications
• Let's start with a trivial example: USER
thisuser*.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 49
PGA Caching with Collections
• Collections are PL/SQL's "version" of arrays.
• You can define and manipulate collections of scalars (numbers, strings, etc.) or much more complex data structures (records, nested collections).
• Which means you can cache multiple rows of complex data.
• In this course, I will offer a quick introduction to collections. – If they are new for you, time to study!
associative_array_example.sql
nested_table_example.sql
varray_example.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 50
Avoiding Unnecessary SGA Lookups
First access
PGA
Function
Database
/ SGA Not in cache;
Request data
from database
Pass Data
to Cache
Application
Application
Requests Data
Data retrieved
from cache Data returned
to application
Function
PGA
Subsequent accesses
Application
Application
Requests Data
Data returned
to application
Data retrieved
from cache
Database
/ SGA
Data found in
cache. Database
is not needed.
emplu.pkg / emplu.tst
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 51
PGA Caching: Things to keep in mind
• Must use package-level data so that it persists. – Memory is consumed by the PGA and so is multiplied for all
users of the application.
– Not a reliable technique for stateless application (Internet)
• Very difficult to share cache across sessions in the same instance. – One possibility involves DBMS_PIPE.
• Very difficult to update the cache once the data source is changed. – Especially by/from, other sessions. Possible to use DBMS_ALERT.
• Useful under specific scenarios.... – Small, static dataset
– Single or small number of batch processes
syscache.pkg
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 52
The Oracle 11g Function Result Cache
• Oracle offers a far superior caching solution than PGA caching in 11g: the Function Result Cache.
• This cache is... – stored in the SGA
– shared across sessions
– purged of dirty data automatically
• You can use and should use it to retrieve data from any table that is queried more frequently than updated.
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 53
How the Function Result Cache Works
• Add the RESULT_CACHE clause to your function's header.
• When a call is made to function, Oracle compares IN argument values to the cache.
• If no match, the function is executed and the inputs and return data are cached.
• If a match is found, the function is not executed; cached data is returned.
• If changes to a "relies on" table are committed, the cache is marked invalid and will be re-built.
11g
11g_frc_demo.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 54
Minimal Impact on Code with Result Cache
• Add RESULT_CACHE keyword to header of function in both specification and body.
• RELIES_ON clause is deprecated in 11.2. Oracle will automatically determine all tables on which the function relies. RELIES_ON is then ignored.
CREATE OR REPLACE PACKAGE emplu11g IS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN employees%ROWTYPE RESULT_CACHE; END emplu11g; CREATE OR REPLACE PACKAGE BODY emplu11g IS FUNCTION onerow (employee_id_in IN employees.employee_id%TYPE) RETURN employees%ROWTYPE RESULT_CACHE RELIES_ON (employees) IS .... END onerow; END emplu11g;
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 55
Performance Impact of Result Cache
• The result cache is stored in the SGA. • So we should expect it be slower than a PGA-
based cache. • But accessing result cache data does not
require going through the SQL engine. • So it should be much faster than executing a
query. – Even if the statement is parsed and the data
blocks are already in the SGA.
• Let's find out!
11g_emplu*.*
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 56
Result Cache – Things to Keep in Mind - 1
• If you have uncommitted changes in your session, dependent caches are ignored. – The cache will not override your own changed
data.
• Caching is not performed for complex types: records with CLOBs, collections, etc.
• The cache is not related to SQL statements in your function. – It only keeps track of the input values and the
RETURN clause data.
11g_frc_demo.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 57
Result Cache – Things to Keep in Mind - 2 • You cannot use the result cache with invoker rights
program units. – Bypass execution of function body, Oracle cannot
resolve references to objects - the whole point of IR.
• Functions with session-specific dependencies must be "result-cached" with great care. – Virtual private database configurations – References to SYSDATE, reliance on NLS_DATE_FORMAT,
time zone changes – Application contexts (calls to SYS_CONTEXT)
• Solution: move all dependencies into parameter list.
11g_frc_vpd.sql
11g_frc_vpd2.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 58
Managing the Result Cache
• Oracle offers a number of ways to manage the result cache and tune it to your specific application needs:
• RESULT_CACHE_SIZE initialization parameter
– If the cache is too small, then the LRU algorithm negates the point of the cache.
• DBMS_RESULT_CACHE management package
• v$RESULT_CACHE_* performance views
11g
show_frc_dependencies.sp
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 59
Fine Grained Dependencies in 11.2
• Oracle keeps track of table dependencies on a per-result level.
– Each result cached could have a different set of dependencies.
• A change to a table could invalidate just a subset of the results in the cache.
– It's not all or nothing - when your function's different logic paths could "hit" different tables.
11g
11g_frc_dependencies.sql
11g_frc_dependencies2.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 60
Conclusions - Caching
• Oracle offers several different ways you can build upon its own caching.
• DETERMINISTIC for functions in SQL
• PGA caching is very fast, but cannot be used in most situations
• The function result cache is the simplest, most widely applicable, and biggest-impact technique. – Get ready for it now by hiding queries inside
functions.
11g_emplu.pkg
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 61
Bulk Processing of SQL in PL/SQL
• The central purpose of PL/SQL is to provide a portable, fast, easy way to write and execute SQL against an Oracle database.
• Unfortunately, this means that most developers take SQL for granted when writing SQL...and just assume Oracle has fully (automagically) optimized how SQL will run from within PL/SQL.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 62
The Problem with SQL in PL/SQL • Many PL/SQL blocks execute the same SQL statement
repeatedly with different bind values. – Retrieve data one row at a time. – Performs same DML operation for each row retrieved.
CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employee.department_id%TYPE ,newsal_in IN employee.salary%TYPE) IS CURSOR emp_cur IS SELECT employee_id,salary,hire_date FROM employee WHERE department_id = dept_in; BEGIN FOR rec IN emp_cur LOOP adjust_compensation (rec, newsal_in); UPDATE employee SET salary = rec.salary WHERE employee_id = rec.employee_id; END LOOP; END upd_for_dept;
The result? Simple and
elegant but inefficient...
Why is this?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 63
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL block Procedural
statement
executor SQL
statement
executor
FOR rec IN emp_cur LOOP UPDATE employee SET salary = ... WHERE employee_id = rec.employee_id; END LOOP;
Performance penalty
for many “context
switches”
Repetitive statement processing from PL/SQL
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 64
Bulk Processing in PL/SQL
• The goal is straightforward: reduce the number of context switches and you improver performance.
• To do this, Oracle "bundles up" the requests for data (or to change data) and then passes them with a single context switch.
• FORALL speeds up DML. – Use with inserts, updates, deletes and merges. – Move data from collections to tables.
• BULK COLLECT speeds up queries. – Can be used with all kinds of queries: implicit, explicit,
static and dynamic. – Move data from tables into collections.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 65
Bulk processing with FORALL
Oracle server
PL/SQL Runtime Engine SQL Engine
PL/SQL block Procedural
statement
executor SQL
statement
executor
FORALL indx IN list_of_emps.FIRST.. list_of_emps.LAST UPDATE employee SET salary = ... WHERE employee_id = list_of_emps(indx);
Fewer context switches,
same SQL behavior
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Update...
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 66
Impact of Bulk Processing in SQL layer
• The bulk processing features of PL/SQL change the way the PL/SQL engine communicates with the SQL layer.
• For both FORALL and BULK COLLECT, the processing in the SQL engine is almost completely unchanged. – Same transaction and rollback segment management – Same number of individual SQL statements will be
executed.
• Only one difference: BEFORE and AFTER statement-level triggers only fire once per FORALL INSERT statements. – Not for each INSERT statement passed to the SQL engine
from the FORALL statement.
statement_trigger_and_forall.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 67
BULK COLLECT Agenda
• Introduction to BULK COLLECT
• Unlimited BULK COLLECTs
• Using the LIMIT clause
• When to convert to BULK COLLECT
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 68
BULK COLLECT for multi-row querying
• Retrieve multiple rows into a collection with a single fetch (context switch to the SQL engine).
• Deposit the multiple rows of data into one or more collections.
SELECT * BULK COLLECT INTO collection(s) FROM table; FETCH cur BULK COLLECT INTO collection(s); EXECUTE IMMEDIATE query BULK COLLECT INTO collection(s);
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 69
"Good to Know" about BULK COLLECT
• NO_DATA_FOUND is not raised when no rows are fetched; instead, the collection is empty.
• The "INTO" collections are filled sequentially from index value 1.
– There are no "gaps" between 1 and the index value returned by the COUNT method.
• Only integer-indexed collections may be used.
• No need to initialize or extend nested tables and varrays. Done automatically by Oracle.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 70
An "unlimited" BULK COLLECT
DECLARE TYPE employees_aat IS TABLE OF employees%ROWTYPE; l_employees employees_aat; BEGIN SELECT * BULK COLLECT INTO l_employees FROM employees; FOR indx IN 1 .. l_employees.COUNT LOOP process_employee (l_employees(indx)); END LOOP; END;
bulkcoll.sql
bulkcollect.tst
Declare a nested
table of records to
hold the queried
data.
Fetch all rows into
collection
sequentially,
starting with 1.
Iterate through
the collection
contents with a
loop. But what if I need to fetch and process
millions of rows?
This approach could consume unacceptable
amounts of PGA memory.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 71
Limiting retrieval with BULK COLLECT
• If you are certain that your table with never have more than N rows, use a VARRAY (N) to hold the fetched data.
– If that limit is exceeded, Oracle will raise an error.
– This is not, however, a very common scenario.
• If you do not know in advance how many rows you might retrieve, you should:
– 1. Declare an explicit cursor.
– 2. Fetch BULK COLLECT with the LIMIT clause.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 72
Limit rows returned by BULK COLLECT CREATE OR REPLACE PROCEDURE bulk_with_limit (deptno_in IN dept.deptno%TYPE) IS CURSOR emps_in_dept_cur IS SELECT * FROM emp WHERE deptno = deptno_in; TYPE emp_tt IS TABLE OF emps_in_dept_cur%ROWTYPE; emps emp_tt; BEGIN OPEN emps_in_dept_cur; LOOP FETCH emps_in_dept_cur BULK COLLECT INTO emps LIMIT 1000; EXIT WHEN emps.COUNT = 0; process_emps (emps); END LOOP; CLOSE emps_in_dept_cur; END bulk_with_limit;
Use the LIMIT clause with the
INTO to manage the amount of
memory used with the BULK
COLLECT operation.
Definitely the preferred approach
in production applications with
large or varying datasets.
bulklimit.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 73
Details on that LIMIT clause
• The limit value can be a literal or a variable.
– I suggest using passing the limit as a parameter to give you maximum flexibility.
• A limit of 100 seems like a good default value.
– Setting it to 500 or 1000 doesn't seem to make much difference in performance.
• With very large volumes of data and small numbers of batch processes, however, a larger LIMIT could help.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 74
Terminating loops containing BULK COLLECT
• You will need to break the habit of checking %NOTFOUND right after the fetch.
– You might skip processing some of your data.
• Instead, do one of the following:
– At the end of the loop, check %NOTFOUND.
– Right after fetch, exit when collection.COUNT = 0.
– At end of loop, exit when collection.COUNT < limit.
LOOP FETCH my_cursor BULK COLLECT INTO l_collection LIMIT 100; EXIT WHEN my_cursor%NOTFOUND; BAD IDEA
bulklimit_stop.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 75
When to convert to BULK COLLECT
• Prior to Oracle10g, you should convert all multiple row fetch code to BULK COLLECTs.
• On 10.1 and higher, the optimizer will automatically optimize cursor FOR loops to run at performance levels similar to BULK COLLECT.
• So leave your cursor for loops in place if they... – contain no DML operations.
– seem to be running fast enough.
• Explicit BULK COLLECTs will usually run a little faster than cursor for loops optimized to BC.
10g_optimize_cfl.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 76
BULK COLLECT Conclusions
• BULK COLLECT improves performance of queries that retrieve more than one row.
• Use the LIMIT clause to avoid excessive PGA memory consumption.
• Leave it to the optimizer to speed up "read only" cursor FOR loops.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 77
FORALL Agenda
• Introduction to FORALL
• Using the SQL%BULK_ROWCOUNT
• Referencing fields of collections of records
• Using FORALL with sparsely-filled collections
• Handling errors raised during execution of FORALL
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 78
Use FORALL for repeated DML operations
• Convert loops that contain inserts, updates, deletes or merges to FORALL statements.
• Header looks identical to a numeric FOR loop. – Implicitly declared integer iterator – At least one "bind array" that uses this iterator as its
index value. – You can also use a different header "style" with INDICES
OF and VALUES OF (covered later)
PROCEDURE upd_for_dept (...) IS BEGIN FORALL indx IN low_value .. high_value UPDATE employee SET salary = newsal_in WHERE employee_id = list_of_emps (indx); END; Bind array
forall_timing.sql
forall_examples.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 79
More on FORALL
• Use any type of collection with FORALL.
• Only one DML statement is allowed per FORALL.
– Each FORALL is its own "extended" DML statement.
• The collection must be indexed by integer.
• The bind array must be sequentially filled.
– Unless you use the INDICES OF or VALUES OF clause.
• Indexes cannot be expressions. forall_restrictions.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 80
How many rows were modified?
• SQL%ROWCOUNT returns total number of rows modified by entire FORALL. – Not to be relied on when used with LOG
ERRORS.
• Use the SQL%BULK_ROWCOUNT cursor attribute to determine how many rows are modified by each statement. – A "pseudo-collection" of integers; no methods
are defined for this element.
bulk_rowcount.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 81
FORALL and collections of records
• Prior to 11g, you cannot reference a field of a record in FORALL.
• You must instead break data into separate collections, or...
• You can also perform record-level inserts and updates.
• In 11g, this restriction is lifted (but it is an undocumented feature).
11g_field_of_record.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 82
Using FORALL with Sparse Collections
• Prior to Oracle10g R2, the binding arrays in a FORALL statement must be sequentially filled.
• Now, however, you can bind sparse collections by using INDICES OF and VALUES OF in the FORALL header.
10g_indices_of*.sql
10g_values_of*.sql
PROCEDURE upd_for_dept (...) IS BEGIN FORALL indx IN INDICES OF list_of_emps UPDATE employee SET salary = newsal_in WHERE employee_id = list_of_emps (indx);
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 83
FORALL and DML Errors
• FORALLs typically execute a large number of DML statements.
• When an exception occurs in one of those DML statement, the default behavior is: – That statement is rolled back and the FORALL stops.
– All (previous) successful statements are not rolled back.
• What if you want the FORALL processing to continue, even if an error occurs in one of the statements?
• Just add the SAVE EXCEPTIONS clause!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 84
SAVE EXCEPTIONS and FORALL
• The SAVE EXCEPTIONS clause tells Oracle to save exception information and continue processing all of the DML statements.
• When the FORALL statement completes, if at least one exception occurred, Oracle then raises ORA-24381.
• You then check the contents of SQL%BULK_EXCEPTIONS.
PROCEDURE upd_for_dept (newsal_in IN NUMBER, list_of_emps_in IN DBMS_SQL.NUMBER_TABLE) IS BEGIN FORALL indx IN list_of_emps_in.FIRST .. list_of_emps_in.LAST SAVE EXCEPTIONS UPDATE employees SET salary = newsal_in WHERE employee_id = list_of_emps_in (indx); END;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 85
Example: FORALL with SAVE EXCEPTIONS
• Add SAVE EXCEPTIONS to enable FORALL to suppress errors at the statement level.
CREATE OR REPLACE PROCEDURE load_books (books_in IN book_obj_list_t) IS bulk_errors EXCEPTION; PRAGMA EXCEPTION_INIT ( bulk_errors, -24381 ); BEGIN FORALL indx IN books_in.FIRST..books_in.LAST SAVE EXCEPTIONS INSERT INTO book values (books_in(indx)); EXCEPTION WHEN bulk_errors THEN FOR indx in 1..SQL%BULK_EXCEPTIONS.COUNT LOOP log_error (SQL%BULK_EXCEPTIONS(indx).ERROR_INDEX , SQL%BULK_EXCEPTIONS(indx).ERROR_CODE); END LOOP; END;
Allows processing of all
statements, even after
an error occurs.
Iterate through
"pseudo-collection"
of errors.
bulkexc.sql
If any exception is
encountered,
Oracle raises -
24381 when done.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 86
SAVE EXCEPTIONS in Detail
• For each exception raised, Oracle populates the SQL%BULK_EXCEPTIONS pseudo-collection of records.
– The record has two fields : ERROR_INDEX and ERROR_CODE.
– ERROR_INDEX: the number of the DML statement that failed (sequentially generated).
– ERROR_CODE: the number (positive) for the error that was raised
• It's a pseudo-collection, because it only supports a single method: COUNT.
• So you iterate from 1 to SQL%BULK_EXCEPTIONS.COUNT to get information about each error.
• Unfortunately, it does not store the error message.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 87
Converting to Bulk Processing
• Let's take a look at the process by which you go from "old-fashioned" code to a bulk processing-based solution.
• From integrated row-by-row to phased processing
• With multiple DML statements in loop, how do you "communicate" from one to the other?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 88
The "Old Fashioned" Approach • Cursor FOR loop with two DML statements, trap
exception, and keep on going.
CREATE OR REPLACE PROCEDURE upd_for_dept ( dept_in IN employees.department_id%TYPE , newsal_in IN employees.salary%TYPE) IS CURSOR emp_cur ...; BEGIN FOR rec IN emp_cur LOOP BEGIN INSERT INTO employee_history ... adjust_compensation (rec.employee_id, rec.salary); UPDATE employees SET salary = rec.salary ... EXCEPTION WHEN OTHERS THEN log_error; END; END LOOP; END upd_for_dept;
cfl_to_bulk_0.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 89
A phased approach with bulk processing • Change from integrated, row-by-row approach to
a phased approach.
Relational Table
Relational Table
Phase 1: Bulk collect from table(s) to collection
Phase 3: FORALL from collection to table
Phase 2: Modify contents of collection according to requirements
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 90
Translating phases into code
• The cfl_to_bulk_5.sql file contains the converted program, following the phased approach.
cfl_to_bulk_0.sql
cfl_to_bulk_5.sql
BEGIN OPEN employees_cur; LOOP fetch_next_set_of_rows ( bulk_limit_in, employee_ids, salaries, hire_dates); EXIT WHEN employee_ids.COUNT = 0; insert_history; adj_comp_for_arrays (employee_ids, salaries); update_employee; END LOOP; END upd_for_dept;
Phase 1: Get Data
Phase 3: Push Data
Phase 2: Massage Data
Phase 3: Push Data
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 91
Conclusions – Bulk Processing
• FORALL is the most important performance tuning feature in PL/SQL. – Almost always the fastest way to execute repeated SQL
operations in PL/SQL.
• You trade off increased complexity of code for dramatically faster execution. – But remember that Oracle will automatically optimize
cursor FOR loops to BULK COLLECT efficiency.
– No need to convert unless the loop contains DML or you want to maximally optimize your code.
• Watch out for the impact on PGA memory!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 92
Table Functions
• A table function is a function that you can call in the FROM clause of a query, and have it be treated as if it were a relational table.
• Table functions allow you to perform arbitrarily complex transformations of data and then make that data available through a query: "just" rows and columns! – After all, not everything can be done in SQL.
• Table functions can also help improve performance in several ways.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 93
When should you use a table function?
• To pass datasets back to a non-PL/SQL host environment, such as Java and .Net.
– They just deal with rows and columns of data.
– To do this, you will need to take advantage of cursor variables.
• Improve query performance with pipelined table functions.
– For parallel query environments (data warehouse)
– And to reduce user perceptions of elapsed time
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 94
Short tangent: Cursor variables and the OPEN FOR • A cursor variable is a variable that points to a
cursor's result set (rows and columns of data).
• The type of a cursor variables is a REF CURSOR.
– Strong REF CURSOR: select lists must match
– Weak REF CURSOR: use with any select.
• Cursor variables can be passed as an argument to a program.
– Or passed back to Java, .Net.
ref_cursors.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 95
Building a table function
• A table function must return a nested table or varray based on a schema-defined type. – Types defined in a PL/SQL package can only be
used with pipelined table functions.
• The function header and the way it is called must be SQL-compatible: all parameters use SQL types; no named notation allowed until 11g. – In some cases (streaming and pipelined
functions), the IN parameter must be a cursor variable -- a query result set.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 96
Simple table function example
• Return a list of names as a nested table, and then call that function in the FROM clause.
CREATE OR REPLACE FUNCTION lotsa_names ( base_name_in IN VARCHAR2, count_in IN INTEGER ) RETURN names_nt IS retval names_nt := names_nt (); BEGIN retval.EXTEND (count_in); FOR indx IN 1 .. count_in LOOP retval (indx) := base_name_in || ' ' || indx; END LOOP; RETURN retval; END lotsa_names;
tabfunc_scalar.sql
SELECT column_value FROM TABLE ( lotsa_names ('Steven' , 100)) names; COLUMN_VALUE ------------ Steven 1 ... Steven 100
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 97
Streaming data with table functions
• You can use table functions to "stream" data through several stages within a single SQL statement. – Example: transform one row in the stocktable to two rows in
the tickertable. CREATE TABLE stocktable ( ticker VARCHAR2(20), trade_date DATE, open_price NUMBER, close_price NUMBER ) / CREATE TABLE tickertable ( ticker VARCHAR2(20), pricedate DATE, pricetype VARCHAR2(1), price NUMBER) /
tabfunc_streaming.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 98
Streaming data with table functions - 2
• In this example, transform each row of the stocktable into two rows in the tickertable.
CREATE OR REPLACE PACKAGE refcur_pkg IS TYPE refcur_t IS REF CURSOR RETURN stocktable%ROWTYPE; END refcur_pkg; / CREATE OR REPLACE FUNCTION stockpivot (dataset refcur_pkg.refcur_t) RETURN tickertypeset ... BEGIN INSERT INTO tickertable SELECT * FROM TABLE (stockpivot (CURSOR (SELECT * FROM stocktable))); END; /
tabfunc_streaming.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 99
Use pipelined functions to enhance performance.
• Pipelined functions allow you to return data iteratively, asynchronous to termination of the function. – As data is produced within the function, it is passed
back to the calling process/query.
• Pipelined functions can only be called within a SQL statement. – They make no sense within non-multi-threaded
PL/SQL blocks.
CREATE FUNCTION StockPivot (p refcur_pkg.refcur_t) RETURN TickerTypeSet PIPELINED
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 100
Applications for pipelined functions
• Execution functions in parallel. – In Oracle9i Database Release 2 and above, use the
PARALLEL_ENABLE clause to allow your pipelined function to participate fully in a parallelized query.
– Critical in data warehouse applications.
• Improve speed of delivery of data to web pages. – Use a pipelined function to "serve up" data to the webpage
and allow users to begin viewing and browsing, even before the function has finished retrieving all of the data.
• And pipelined functions use less PGA memory than non-pipelined functions!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 101
Piping rows out from a pipelined function
CREATE FUNCTION stockpivot (p refcur_pkg.refcur_t) RETURN tickertypeset PIPELINED IS out_rec tickertype := tickertype (NULL, NULL, NULL); in_rec p%ROWTYPE; BEGIN LOOP FETCH p INTO in_rec; EXIT WHEN p%NOTFOUND; out_rec.ticker := in_rec.ticker; out_rec.pricetype := 'O'; out_rec.price := in_rec.openprice; PIPE ROW (out_rec); END LOOP; CLOSE p; RETURN; END;
tabfunc_setup.sql
tabfunc_pipelined.sql
Add PIPELINED
keyword to header
Pipe a row of data
back to calling block
or query
RETURN...nothing at
all!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 102
Enabling Parallel Execution
• You can use pipelined functions with the Parallel Query option to avoid serialization of table function execution.
• Include the PARALLEL_ENABLE hint in the program header. – Choose a partition option that specifies how the function's
execution should be partitioned.
– "ANY" means that the results are independent of the order in which the function receives the input rows (through the REF CURSOR).
{[ORDER | CLUSTER] BY column_list} PARALLEL_ENABLE ({PARTITION p BY [ANY | (HASH | RANGE) column_list]} )
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 103
Table functions - Summary
• Table functions offer significant new flexibility for PL/SQL developers.
• Consider using them when you...
– Need to pass back complex result sets of data through the SQL layer (a query);
– Want to call a user defined function inside a query and execute it as part of a parallel query.
• Use pipelined table functions for performance improvement and reduced PGA consumption.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 104
The NOCOPY hint
• By default, Oracle passes all IN OUT and OUT arguments by value, not reference.
– This means that OUT and IN OUT arguments always involve some copying of data.
• With NOCOPY, you turn off the copy process.
– But it comes with a risk: Oracle will not automatically "rollback" or reverse changes made to your variables if the NOCOPY-ed program raises an exception.
nocopy*.*
string_nocopy.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 105
Dynamic SQL in PL/SQL Programs
• Dynamic SQL actually refers, in the world of PL/SQL, to two things:
– SQL statements, such as a DELETE or DROP TABLE, that are constructed and executed at run-time.
– Anonymous PL/SQL blocks that are constructed, compiled and executed at run-time.
'DROP ' || l_type || ' ' || l_name
'BEGIN ' || l_proc_name || ' (' || l_parameters || '); END;'
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 106
Use Dynamic SQL To...
• Build ad-hoc query and update applications.
– The user decides what to do and see.
• Execute DDL statements from within PL/SQL.
– Not otherwise allowed in a PL/SQL block.
• Soft-code your application logic, placing business rules in tables and executing them dynamically.
– Usually implemented through dynamic PL/SQL
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 107
Two Mechanisms Available
• DBMS_SQL – A large and complex built-in package that made
dynamic SQL possible in Oracle7 and Oracle8.
• Native Dynamic SQL – A new (with Oracle8i), native implementation of
dynamic SQL that does almost all of what DBMS_SQL can do, but much more easily and usually more efficiently.
– EXECUTE IMMEDIATE
– OPEN cv FOR 'SELECT ... '
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 108
Four Dynamic SQL Methods
• Method 1: DDL or non-query DML without bind variables – EXECUTE IMMEDIATE string
• Method 2: Non-query DML with fixed number of bind variables – EXECUTE IMMEDIATE string USING
• Method 3: Query with fixed number of expressions in the select list and fixed number of bind variables – EXECUTE IMMEDIATE string USING ... INTO
• Method 4: Query with dynamic number of expressions in select list or DML with dynamic number of bind variables. – DBMS_SQL is best. And then there's
dynamic PL/SQL....
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 109
Method 1: DDL within PL/SQL
• The simplest kind of dynamic SQL. – All you can do is pass a string for execution, no
values are bound in, no values are passed out.
• Always performs an implicit commit when executed.
• Should be used with great care, since a DDL change can cause a ripple effect of invalidating program units.
• Common problem: Insufficient privileges. – Directly granted privileges are needed!
dropwhatever.sp
create_index.sp
settrig.sp
ddl_insuff_privs.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 110
Method 2: DML with fixed # of bind variables
• Add the USING clause to EXEC IMMEDIATE to supply bind values for placeholders. – Placeholders are strings starting with ":".
• USING elements can include a mode, just like a parameter: IN, OUT or IN OUT. – OUT and IN OUT are for dynamic PL/SQL
• Must provide a value for each placeholder. – With dynamic SQL, even if the same placeholder
is repeated, you must provide the repeat value.
method_2_example.sql
updnval*.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 111
Dynamic FORALL Method 2 Example
• This example shows the use of bulk binding and collecting, plus application of the RETURNING clause.
CREATE TYPE NumList IS TABLE OF NUMBER; CREATE TYPE NameList IS TABLE OF VARCHAR2(15); PROCEDURE update_emps ( col_in IN VARCHAR2, empnos_in IN numList) IS enames NameList; BEGIN FORALL indx IN empnos_in.FIRST .. empnos_in.LAST EXECUTE IMMEDIATE 'UPDATE emp SET ' || col_in || ' = ' || col_in || ' * 1.1 WHERE empno = :1 RETURNING ename INTO :2' USING empnos_in (indx ) RETURNING BULK COLLECT INTO enames; ... END;
Notice that empnos_in is
indexed, but enames is not.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 112
Method 3: Query with fixed # in select list
• Add the INTO clause to EXEC IMMEDIATE to retrieve values from query.
– May be in addition to the USING clause.
– If you don't know the number at compile time, cannot use the INTO clause.
• Usually you are dealing with a dynamic table or column name.
• The INTO clause can contain a list of variables, a record, a collection, etc.
tabcount81.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 113
Dynamic BULK COLLECT Method 3
• Now you can even avoid the OPEN FOR and just grab your rows in a single pass!
CREATE OR REPLACE PROCEDURE fetch_by_loc (loc_in IN VARCHAR2) IS TYPE numlist_t IS TABLE OF NUMBER; TYPE namelist_t IS TABLE OF employee.name%TYPE; TYPE employee_t IS TABLE OF employee%ROWTYPE; emp_cv sys_refcursor; empnos numlist_t; enames namelist_t; l_employees employee_t; BEGIN OPEN emp_cv FOR 'SELECT empno, ename FROM emp_' || loc_in; FETCH emp_cv BULK COLLECT INTO empnos, enames; CLOSE emp_cv; EXECUTE IMMEDIATE 'SELECT * FROM emp_' || loc_in BULK COLLECT INTO l_employees; END;
return_nested_table.sf
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 114
Quiz! PROCEDURE process_lineitem ( line_in IN PLS_INTEGER) IS BEGIN IF line_in = 1 THEN process_line1; END IF; IF line_in = 2 THEN process_line2; END IF; ... IF line_in = 22045 THEN process_line22045; END IF; END;
• What's wrong with this code?
• How would you fix it?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 115
From 22,000 lines of code to 1!
• Identify the pattern and resolve it either with reusable modules or dynamic abstractions.
PROCEDURE process_lineitem ( line_in IN INTEGER) IS BEGIN IF line_in = 1 THEN process_line1; END IF; IF line_in = 2 THEN process_line2; END IF; ... IF line_in = 22045 THEN process_line22045; END IF; END;
PROCEDURE process_lineitem ( line_in IN INTEGER) IS BEGIN EXECUTE IMMEDIATE 'BEGIN process_line'|| line_in ||'; END;'; END;
dynplsql.txt
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 116
Dynamic PL/SQL
• Dynamically construct, compile and run an anonymous block with EXECUTE IMMEDIATE. – Begins with BEGIN or DECLARE.
– Ends with END;. The trailing semi-colon is required; otherwise it is parsed as an SQL statement.
• You can only reference globally-accessible data structures (declared in a package specification).
• Exceptions can (and should) be trapped in the block from which the dynamic PL/SQL was executed.
dynplsql*.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 117
Method 4 Dynamic SQL with DBMS_SQL
• Method 4 dynamic SQL is the most generalized and most complex - by far! – You don't know at compile time either the number
of columns or the number of bind variables. – With DBMS_SQL, you must put calls to
DBMS_SQL.DEFINE_COLUMN and/or DBMS_SQL.BIND_VARIABLE into loops.
• With NDS, you must shift from dynamic SQL to dynamic PL/SQL. – How else can you have a variable INTO or USING
clause?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 118
Dynamic "SELECT * FROM <table>" in PL/SQL
• You provide the table and WHERE clause. I display all the data.
– I don't know in advance which or how many rows to query.
• I can obtain the column information from ALL_TAB_COLUMNS...and from there the fun begins!
• A relatively simple example to use as a starting point.
intab_dbms_sql.sp - uses DBMS_SQL
intab_nds.sp - uses NDS
intab.tst
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 119
Pseudo-code flow for DBMS_SQL implementation
BEGIN FOR each-column-in-table LOOP add-column-to-select-list; END LOOP; DBMS_SQL.PARSE (cur, select_string, DBMS_SQL.NATIVE); FOR each-column-in-table LOOP DBMS_SQL.DEFINE_COLUMN (cur, nth_col, datatype); END LOOP; fdbk := DBMS_SQL.EXECUTE (cur); LOOP fetch-a-row; FOR each-column-in-table LOOP DBMS_SQL.COLUMN_VALUE (cur, nth_col, val); END LOOP; END LOOP; END;
Build the
SELECT list
Define each
column
Extract each
value
Parse the
variable SQL
Execute the
query
Lots of code, but relatively
straightforward Also:
dyn_placeholder.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 120
Parsing very long strings
• One problem with EXECUTE IMMEDIATE is that you pass it a single VARCHAR2 string. – Maximum length 32K.
• So what do you do when your string is longer? – Very likely to happen when you are generating SQL
statements based on tables with many columns.
– Also when you want to dynamically compile a program.
• Time to switch to DBMS_SQL! – Or upgrade to 11g…
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 121
DBMS_SQL.PARSE overloading for collections
• Oracle offers an overloading of DBMS_SQL.PARSE that accepts a collection of strings, rather than a single string.
• DBMS_SQL offers two different array types:
– DBMS_SQL.VARCHAR2S - max 255 bytes.
– DBMS_SQL.VARCHAR2A - max 32,767 bytes
• New in Oracle11g: both NDS and DBMS_SQL accept CLOBs.
exec_ddl_from_file.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 122 Copyright 2000-2008 Steven Feuerstein - Page 122
Describe columns in a query
• DBMS_SQL offers the ability to "ask" a cursor to describe the columns defined in that cursor.
• By using the DESCRIBE_COLUMNS procedure, you can sometimes avoid complex parsing and analysis logic.
– Particularly useful with method 4 dynamic SQL.
desccols.pkg
desccols.tst
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 123
Best Practices for Dynamic SQL
• Stored programs with dynamic SQL should be defined as AUTHID CURRENT_USER.
• Remember that dynamic DDL causes an implicit commit. – Consider making all DDL programs autonomous
transactions.
• Always EXECUTE IMMEDIATE a variable, so that you can then display/log/view that variable's value in case of an error.
• Avoid concatenation; bind whenever possible. dropwhatever.sp
usebinding.sp
toomuchbinding.sp
useconcat*.*
ultrabind.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 124
NDS or DBMS_SQL: Which should you use?
Reasons to go with
NDS:
– Ease of use
– Works with all SQL
datatypes (including user-
defined object and
collection types)
– Fetch into records and
collections of records
– Usually faster runtime
performance
Why You'd Use
DBMS_SQL:
– Method 4 Dynamic SQL
– DESCRIBE columns of cursor
– SQL statements larger than
32K (advantage disappears in
11g)
– Better reuse of parsed SQL
statements -- persistent cursor
handles!
tabcount.sf
tabcount81.sf
Bottom line: NDS should be your first choice.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 125
Oracle11g Enhancements for Dynamic SQL
• Parse and execute very large strings. – EXECUTE IMMEDIATE a CLOB
– DBMS_SQL.PARSE a CLOB
• Interoperability – Convert DBMS_SQL cursor to cursor variable
– Convert cursor variable to DBMS_SQL cursor
• Improved security – Random generation of DBMS_SQL cursor handles
– Denial of access/use of DBMS_SQL with invalid cursor or change of effective user.
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 126
Parse very large SQL statements
• Both EXECUTE IMMEDIATE and OPEN FOR now accept a CLOB.
• A new DBMS_SQL.PARSE overloading also accepts a CLOB.
• You no longer need to use the collection overloadings of DBMS_SQL.PARSE to work with very large strings.
exec_ddl_from_file.sql
exec_ddl_from_file_11g.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 127
Interoperability
• DBMS_SQL.TO_REFCURSOR
– Cursor handle to cursor variable
– Useful when you need DBMS_SQL to bind and execute, but easier to fetch through cursor variable.
• DBMS_SQL.TO_CURSOR_NUMBER
– Cursor variable to cursor handle
– Binding is static but SELECT list is dynamic
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 128
DBMS_SQL.TO_REFCURSOR
• Converts a SQL cursor number to a weak cursor variable, which you can use in native dynamic SQL statements.
• Before passing a SQL cursor number to the DBMS_SQL.TO_REFCURSOR function, you must OPEN, PARSE, and EXECUTE it (otherwise an error occurs).
• After you convert a SQL cursor number to a REF CURSOR variable, DBMS_SQL operations can access it only as the REF CURSOR variable, not as the SQL cursor number. – Using the DBMS_SQL.IS_OPEN function to see if a
converted SQL cursor number is still open causes an error.
11g_to_refcursor.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 129
DBMS_SQL.TO_CURSOR_NUMBER
• Converts a REF CURSOR variable (either strong or weak) to a SQL cursor number, which you can pass to DBMS_SQL subprograms.
• Before passing a REF CURSOR variable to the DBMS_SQL.TO_CURSOR_NUMBER function, you must OPEN it.
• After you convert a REF CURSOR variable to a SQL cursor number, native dynamic SQL operations cannot access it.
11g_to_cursorid.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 130
Improved Security
• Cursor handles generated by the DBMS_SQL.OPEN_CURSOR function are now random and not sequential.
• Pass an invalid cursor handle to many DBMS_SQL programs and DBMS_SQL is then disabled. – Have to reconnect.
• You can specify a security level for DBMS_SQL cursor management. – Minimize danger of SQL injection.
11g_random_cursor_handle.sql
11g_access_denied_1.sql
11g_effective_user_id.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 131
Oracle11g Dynamic SQL Conclusions
• Both mechanisms for dynamic SQL have been improved.
• Of especial importance is the ability to move between native dynamic SQL and DBMS_SQL, to make it even easier to implement method 4 dynamic SQL requirements.
• Make sure you take full advantage of the security-related features of DBMS_SQL for any external-facing interfaces. – If, that is, you are required to use DBMS_SQL!
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 132
Must Know Error Management Features
• DBMS_UTILITY functions
– FORMAT_CALL_STACK
– FORMAT_ERROR_STACK
– FORMAT_ERROR_BACKTRACE
• DBMS_ERRLOG and LOG ERRORS
– Suppress errors at row level in SQL layer
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 133
Oracle Built-ins For Handling Exceptions
• In addition to the application-specific information you may want to log, Oracle built-ins provide you with answers to the following questions:
– How did I get here?
– What is the error code?
– What is the error message and/or stack?
– On what line was the error raised?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 134
SQLCODE and SQLERRM
• SQLCODE returns the error code of the most recently-raised exception in your session.
• SQLERRM returns the error message associated with SQLCODE – but it also a generic error message lookup function.
• Neither SQLCODE nor SQLERRM can be called from within a SQL statement. – You must assign them to local variables to use their values
in SQL statements (like writing to an error log).
sqlcode.sql sqlcode_test.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 135
SQLERRM Details
• If you don't pass an argument to SQLERRM, it returns the error message for the SQLCODE value. – When called outside of an exception handler, always
returns "success" message – no error.
• You can also pass an error code to SQLERRM and it will return the generic error message.
• The maximum size of a string returned by SQLERRM is 512 bytes. – When there is a stack of errors, Oracle may truncate the
string returned by SQLERRM.
– Oracle recommends you use DBMS_UTILITY.FORMAT_ERROR_STACK instead.
sqlerrm.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 136
DBMS_UTILITY error functions
• Answer the question "How did I get here?" with DBMS_UTILITY.FORMAT_CALL_STACK.
• Get a more complete error message with DBMS_UTILITY.FORMAT_ERROR_STACK.
• Find line number on which error was raised with DBMS_UTILITY.FORMAT_ERROR_BACKTRACE.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 137
DBMS_UTILITY.FORMAT_CALL_STACK
• The "call stack" reveals the path taken through your application code to get to that point.
• Very useful whenever tracing or logging errors.
• The string is formatted to show line number and program unit name.
– But it does not reveal the names of subprograms in packages.
callstack.sql callstack.pkg
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 138
DBMS_UTILITY.FORMAT_ERROR_STACK
• This built-in returns the error stack in the current session.
– Possibly more than one error in stack.
• Returns NULL when there is no error.
• Returns a string of maximum size 2000 bytes (according to the documentation).
• Oracle recommends you use this instead of SQLERRM, to reduce the chance of truncation.
errorstack.sql big_error_stack.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 139
DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
• The backtrace function (new to 10.2) answers the question: "Where was my error raised?
– Prior to 10.2, you could not get this information from within PL/SQL.
• Call it whenever you are logging an error.
• When you re-raise your exception (RAISE;) or raise a different exception, subsequent BACKTRACE calls will point to that line.
– So before a re-raise, call BACKTRACE and store that information to avoid losing the original line number.
backtrace.sql bt.pkg
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 140
DBMS_ERRLOG and LOG ERRORS- agenda
• Impact of errors on DML execution
• Introduction to LOG ERRORS feature
• Creating an error log table
• Adding LOG ERRORS to your DML statement
• "Gotchas" in the LOG ERRORS feature
• The DBMS_ERRLOG helper package
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 141
Impact of errors on DML execution
• A single DML statement can result in changes to multiple rows.
• When an error occurs on a change to a row.... – All previous changes from that statement are rolled
back. – No other rows are processed. – An error is passed out to the calling block (turns into a
PL/SQL exception). – No rollback on completed DML in that session.
• Usually acceptable, but what if you want to: – Avoid losing all prior changes? – Avoid the performance penalty of exception
management in PL/SQL?
errors_and_dml.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 142
Row-level Error Suppression in DML with LOG ERRORS • Once the error propagates out to the PL/SQL
layer, it is too late; all changes to rows have been rolled back.
• The only way to preserve changes to rows is to add the LOG ERRORS clause in your DML statement. – Errors are suppressed at row level within the SQL
Layer.
• But you will first need to created an error log table with DBMS_ERRLOG.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 143
Terminology for LOG ERRORS feature
• DML table: the table on which DML operations will be performed
• Error logging table (aka, error table): the table that will contain history of errors for DML table
• Reject limit: the maximum number of errors that are acceptable for a given DML statement
– "If more than 100 errors occur, something is badly wrong, just stop."
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 144
Step 1. Create an error log table
• Call DBMS_ERRLOG.CREATE_ERROR_LOG to create the error logging table for your "DML table." – Default name: ERR$_<your_table_name>
• You can specify alternative table name, tablespace, owner. – Necessary if DML table name > 25 characters!
• The log table contains five standard error log info columns and then a column for each VARCHAR2-compatible column in the DML table.
dbms_errlog.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 145
Step 2: Add LOG ERRORS to your DML
• Specify the limit of errors after which you want the DML statement to stop – or UNLIMITED to allow it to run its course.
• Then...make sure to check the error log table after you run your DML statement! – Oracle will not raise an exception when the DML
statement ends – big difference from SAVE EXCEPTIONS.
UPDATE employees SET salary = salary_in LOG ERRORS REJECT LIMIT UNLIMITED; UPDATE employees SET salary = salary_in LOG ERRORS REJECT LIMIT 100;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 146
"Gotchas" in the LOG ERRORS feature • The default error logging table is missing some critical
information. – When the error occurred, who executed the statement,
where it occurred in my code
• Error reporting is often obscure: "Table or view does not exist."
• It’s up to you to grant the necessary privileges on the error log table. – If the “DML table” is modified from another schema,
that schema must be able to write to the log table as well.
• Use the DBMS_ERRLOG helper package to get around many of these issues.
dbms_errlog.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 147
The DBMS_ERRLOG helper package
• Creates the error log table.
• Adds three columns to keep track of user, timestamp and location in code.
• Compiles a trigger to populate the added columns.
• Creates a package to make it easier to manage the contents of the error log table.
dbms_errlog_helper.sql dbms_errlog_helper_demo.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 148
LOG ERRORS Conclusions
• When executing multiple DML statements or affecting multiple rows, decide on your error policy. – Stop at first error or continue?
• Then decide on the level of granularity of continuation: statement or row? – LOG ERRORS is the only way to perform row-level
error suppression.
• Make sure that you check and manage any error logs created by your code.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 149
PL/Scope
• A compiler-driven tool that collects information about identifiers and stores it in data dictionary views.
• Use PL/Scope to answer questions like:
– Where is a variable assigned a value in a program?
– What variables are declared inside a given program?
– Which programs call another program (that is, you can get down to a subprogram in a package)?
– Find the type of a variable from its declaration.
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 150
Getting Started with PL/Scope
• PL/Scope must be enabled; it is off by default.
• When your program is compiled, information about all identifiers are written to the ALL_IDENTIFIERS view.
• You then query the contents of the view to get information about your code.
• Check the ALL_PLSQL_OBJECT_SETTINGS view for the PL/Scope setting of a particular program unit.
ALTER SESSION SET plscope_settings='IDENTIFIERS:ALL'
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 151
Key Columns in ALL_IDENTIFIERS • TYPE
– The type of identifier (VARIABLE, CONSTANT, etc.)
• USAGE – The way the identifier is used (DECLARATION,
ASSIGNMENT, etc.)
• LINE and COL – Line and column within line in which the identifier is found
• SIGNATURE – Unique value for an identifier. Especially helpful when
distinguishing between overloadings of a subprogram or "connecting" subprogram declarations in package with definition in package body.
• USAGE_ID and USAGE_CONTEXT_ID – Reveal hierarchy of identifiers in a program unit
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 152
Start with some simple examples
• Show all the identifiers in a program unit
• Show all variables declared in a subprogram (not at package level)
• Show all variables declared in the package specifications
• Show the locations where a variable could be modified
plscope_demo_setup.sql plscope_all_idents.sql
plscope_var_declares.sql plscope_gvar_declares.sql
plscope_var_changes.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 153
More advanced examples
• Find exceptions that are defined but never raised
• Show the hierarchy of identifiers in a program unit
• Validate naming conventions with PL/Scope
plscope_unused_exceptions.sql plscope_hierarchy.sql
plscope_naming_conventions.sql
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 154
PL/Scope Helper Utilities
• Clearly, "data mining" in ALL_IDENTIFIERS can get very complicated.
• Suggestions for putting PL/Scope to use:
– Build views to hide some of the complexity.
– Build packages to provide high-level subprograms to perform specific actions.
plscope_helper_setup.sql plscope_helper.pkg
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 155
PL/Scope Summary
• PL/Scope gives you a level of visibility into your code that was never before possible.
• The ALL_IDENTIFIERS view is not straightforward.
• Use the helper package to get you started.
• Hopefully we will see PL/Scope interfaces built into the most popular IDEs.
11g
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 156
Say Goodbye to Hard-Coding - Agenda
• What is hard-coding?
• Why is it a problem?
• The opposite of hard-coding
• Where's the hard-coding?
• Specific techniques for getting rid of the many forms of hard-coding
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 157
What is Hard Coding? - 1
• Traditionally, has referred to practice of placing literal values in the main body of your code. – From Wikipedia: The term "hard-coded" was coined in
1990 by R. Galichon (then a Programmer/Analyst at Mobil). The term was used as an analogy to hardwiring circuits - and was meant to convey the inflexibility which results from its usage within software design and implementation.
• More generally, hard-coding is closely tied to the problem of repetition in our code.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 158
What is Hard Coding? - 2
• You "hard code" every time you write a piece of code that assumes an aspect of your application will not change and therefore can be explicitly referenced throughout the code base.
• Then when the change takes place, you have to locate all those repetitions and fix them.
– Sometimes that's easy, sometimes it is very difficult to do, but in all cases, it causes problems.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 159
Why is Hard Coding Bad?
• Hard-coding would be fine if nothing ever changed in our applications.
– If requirements stayed the same...
– If the definitions of our tables stayed the same....
– If rules and formulas stayed the same....
– If configuration constants stayed the same....
• Too bad!
– Whenever anything changes, you have to find all the places you explicitly coded it, and fix them.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 160
The Opposite of Hard-Coding
• If hard-coding is bad, then maybe we should do the opposite of hard-coding.
• Soft Coding – Rather than explicitly code values, rules and
algorithms, make them "soft" or dynamic – changeable and settable at runtime.
• Easy Coding – It's hard fixing hard-codings in multiple places. It'd
be easier to fix things in one place. It really does make things easier.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 161
Hard-Coding Avoidance: Principles and Concepts
• Single point of definition (no repetition)
– You should always aim for a single point of definition or SPOD for everything in your application.
• Information hiding – the name is the thing
– Avoid exposing the implementation details of formulas, rules, algorithms, data access.
– The more you hide, the more flexibility you have.
• "Never" and "Always" in software
– It's never going to stay the same.
– It's always going to change.
• Pay attention to that "voice in your head" (sometimes)
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 162
Where's the hard-coding?
1 PROCEDURE process_employee (department_id_in IN NUMBER) 2 IS 3 l_id INTEGER; l_salary NUMBER (9,2); 4 l_name VARCHAR2 (100); 5 6 /* Full name: LAST COMMA FIRST (ReqDoc 123.A.47) */ 7 CURSOR emps_in_dept_cur 8 IS 9 SELECT employee_id, salary, last_name || ',' || first_name lname 10 FROM employees 11 WHERE department_id = department_id_in; 12 BEGIN 13 OPEN emps_in_dept_cur; 14 15 LOOP 16 FETCH emps_in_dept_cur 17 INTO l_id, l_salary, l_name; 18 19 IF l_salary > 10000000 THEN adjust_comp_for_ceo (l_salary); 20 ELSE analyze_compensation (l_id, l_salary, 10000000); END IF; 21 22 EXIT WHEN emps_in_dept_cur%NOTFOUND; 23 END LOOP; 24 COMMIT; 25 EXCEPTION WHEN NO_DATA_FOUND THEN 26 RAISE_APPLICATION_ERROR (-20907, 'Invalid department ID'); 27 END;
hardcoding.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 163
Potential Hard-Codings in PL/SQL Code
• Literal values
– Especially language-specific literals
• Constrained declarations
– Especially VARCHAR2(n)
• Fetch into a list of variables
• Rules and formulas - especially the "trivial" ones
• SQL statements – ah, very scary!
• Algorithmic details
– Example: error logging mechanisms
• Transaction boundaries and dates
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 164
Dealing with Magic Values
• What are "magic values"?
• Hide behind constants
• Hide behind functions
• Soft code in tables
• Hide error codes with EXCEPTION_INIT
• Use silly values for your magic values
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 165
Magical Values (Literals)
• The most commonly recognized form of hard-coding.
• The only place a literal should appear in your code is in its SPOD.
• Hide literals behind constants or functions.
• Consider soft coding values in tables.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 166
Hide Behind Constant
• Instead of exposing the literal value, and referencing it in multiple places, declare a constant and reference that name.
• Best to put such constants in a package specification. – Can share across entire code base.
• Constants are simple and quick, but they expose the value in the package specification. – If the value needs to change, all programs that
depend on that package must be recompiled.
constant_vs_function.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 167
Hide Behind Function
• You can also define a function whose body returns the value.
– Best done in a package
• Advantages over constants include
– When the value changes, only the package body must be recompiled.
– Developers cannot "lazily" see/use value.
– You can call the function in an SQL statement
• But this is less efficient than a constant. constant_vs_function.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 168
Soft-Code Values in Table
• You can make things really flexible by putting all literals in a table, associating them with a name, and retrieving them as needed from the table.
• Downsides are:
– More complex code
– More overhead, but caching can avoid this problem.
soft_code_literals.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 169
Use Silly Values for Constants
• Sometimes you can choose the literals for your magic values, such as with status codes.
• If you use "reasonable" values for your constants, people will be tempted to "cheat." – Success = 0, Failure = 1, etc.
• You might consider using "silly" values instead. – Making it downright embarrassing to reference
the values explicitly. fileIO92.pkg
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 170
Hide error codes with EXCEPTION_INIT
• Oracle doesn't provide a name for every error code, but you can do this.
• Best place to put exception declarations is a package, so they can be shared across the application.
WHEN OTHERS THEN IF SQLCODE = -24381 THEN ... ELSIF SQLCODE = -1855 THEN ... ELSE RAISE; END;
e_forall_failure EXCEPTION; PRAGMA EXCEPTION_INIT ( e_forall_failure, -24381); BEGIN .... EXCEPTION WHEN e_forall_failure THEN ... END;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 171
Conclusions
• Magic value hard-coding is the most commonly recognized form of hard-coding.
• It is also the easiest to remove from your code.
• Use constants or functions to hide the value, define that value in one place.
– "Single point of definition" or SPOD
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 172
Avoiding Hard-Coded Declarations - Agenda
• What's the problem?
• Always fetch into records
• Use %TYPE and %ROWTYPE whenever possible
• Use SUBTYPE to define application-specific types
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 173
What's the problem?
• Hard-coded declarations are declarations that reference PL/SQL base types and/or constrain values in some way.
• Very often our variables hold data that is stored elsewhere (columns of tables).
• If the underlying column definition changes, we can get VALUE_ERROR (ORA-06502) and other errors.
• Generally, our variable types can become "out of synch" with the data.
hardcoding.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 174
Fetch into list of variables
• If your FETCH statement contains a list of individual variables, you are hard-coding the number of elements in the SELECT list.
– When the cursor changes, you must change the FETCH as well.
• Solution: always fetch into a record, defined with %ROWTYPE against the cursor.
fetch_into_record.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 175
Do not expose constrained declarations
• Every declaration requires a datatype.
• If you are not careful, the way you specify that datatype could be a hard-coding.
• Generally, any declaration that relies on a constrained datatype is a hard-coding.
– BOOLEAN and DATE are unconstrained.
• Another way to remember this is:
Consider every VARCHAR2(N) declaration to be a bug – unless it's a SPOD.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 176
"SPODification" for Datatypes
• Two problems with hard-coding the datatype:
– Constraints can lead to errors in future.
– The datatype does not explain the application significance of the element declared.
• Whenever possible, anchor the datatype of your declaration to an already-existing type.
– That way, if the existing type or SPOD ever changes, then your code will be marked INVALID and automatically recompiled to pick up the latest version of the anchoring type.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 177
%TYPE and %ROWTYPE
• Use %TYPE for declarations based on columns in tables.
• Use %ROWTYPE for records based on tables, views or cursors.
• The lookup of the datatype from these attributes occurs at compile-time.
– There is no run-time overhead.
no_more_hardcoding.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 178
SUBTYPEs
• You can't always use %TYPE or %ROWTYPE in your declaration.
• You can, however, always define a "subtype" or subset of an existing type with the SUBTYPE statement. SUBTYPE benefits:
– Avoid exposing and repeating constraints.
– Give application-specific names to types. Critical when working with complex structures like collections of records, and nested collections.
– Apply constraints, such as numeric ranges, to the variable declared with the subtype.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 179
SUBTYPE Details and Examples
• Define a subtype based on any pre-defined type or other, already-defined subtype.
• If the base type can be constrained, then you can constrain the subtype.
– (precision,scale) or RANGE
• You can also, always specify NOT NULL.
– Even if the base type could be NULL.
SUBTYPE type_name IS data_type [ constraint ] [ NOT NULL ]
subtype_examples.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 180
Applying SUBTYPEs
• Two key scenarios:
– Whenever you are about to write a VARCHAR2(N) or other constrained declaration, define a subtype instead, preferably in a package specification.
– Instead of writing a comment explaining a declaration, put the explanation into a subtype.
fullname.pks plsql_limits.pks
string_tracker3.*
DECLARE l_full_name VARCHAR2(100); l_big_string VARCHAR2(32767);
DECLARE l_full_name employees_rp.full_name_t; l_big_string plsql_limits.maxvarchar2;
Instead of this:
Write this:
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 181
Conclusions
• Declarations offer a danger of hard-coding of both datatype and constraint on that type.
• Assume that over time everything will change.
• Apply the same "single point of definition" principle to your declarations.
– Use %TYPE and %ROWTYPE whenever possible.
– Fall back on subtypes to define application specific types and PL/SQL limits.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 182
Stop Writing So Much SQL - agenda
• What's the problem with writing lots of SQL?
• Why talk about SQL in a lesson on hard-coding?
• Concepts behind and benefits of data encapsulation
• Data encapsulation recommendations
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 183 Page 183
Writing SQL in PL/SQL • The most critical aspect of our programs.
• SQL statements directly reflect our business models. – And those models are always changing.
• SQL statements cause most of the performance problems in our applications. – Tuning SQL and the way that SQL is called in
PL/SQL overwhelms all other considerations.
• Many runtime errors in applications result from integrity and check constraints on tables.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 184
The fundamental problem with SQL in PL/SQL • We take it entirely for
granted. – Why not? It's so easy to
write SQL in PL/SQL!
• We don't set rules on how, when and where SQL should be written in PL/SQL.
The Backend
Order Table Item
Table
Order Entry Application
Customer Table
The result? Slow, buggy code that is difficult to optimize and maintain.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 185
So set some SQL standards!
• At a minimum, before starting your next application, ask yourselves explicitly:
• 1. Are we taking full advantage of SQL, particularly new features in our version?
– You should do as much as possible in "pure" SQL.
• 2. Do we want standards or should we just do whatever we want, whenever we want?
– That way, you are making a conscious decision.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 186
Fully leverage SQL in your PL/SQL code
• Oracle continually adds significant new functionality to the SQL language.
• If you don't keep up with SQL capabilities, you will write slower, more complicated PL/SQL code than is necessary.
– I am actually a good example of what you don't want to do or how to be.
• So take the time to refresh your understanding of Oracle SQL in 10g and 11g.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 187
Some exciting recently added SQL features • Courtesy of Lucas Jellama of AMIS Consulting
• Analytical Functions – Especially LAG and LEAD; these allow you to look to previous and following
rows to calculate differences.
• WITH clause (subquery factoring) – Allows the definition of 'views' inside a query that can be used and reused;
they allow procedural top-down logic inside a query
• Flashback query – No more need for journal tables, history tables, etc.
• ANSI JOIN syntax – Replaces the (+) operator and introduces FULL OUTER JOIN
• SYS_CONNECT_BY_PATH and CONNECT_BY_ROOT for hierarchical queries
• Scalar subquery – Adds a subquery to a query like a function call. select d.deptno
, (select count(*) from emp e where e.deptno = d.deptno) number_staff from dept
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 188 Page 188
SQL statements as hard-codings • I suggest that every SQL statement you will ever
write is a hard-coding. Consider....
• I need to write a complex query to return HR data for a report.
SELECT . . . FROM employees, departments, locations WHERE . . . (a page full of complex conditions)
• And then the three way join turns into a four way join – and we have to find all occurrences of this query. A very tough thing to do!
• And Joe needs to use that same query in his business rule procedure. And so on...
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 189
What to do about SQL hard coding
• Of course, you have to (and should) write SQL statements in your PL/SQL code.
– PL/SQL is, in fact, the best place for SQL.
• But we should be very careful about where, when and how we write these statements.
– Follow the principles; they are your guide.
– Don't repeat anything!
• The best approach: hide SQL statements inside a data access layer.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 190
SQL as a Service
• Think of SQL as a service that is provided to you, not something you write.
– Or if you write it, you put it somewhere so that it can be easily found, reused, and maintained.
This service consists of views and programs defined in the data access layer.
– Views hide complex query construction
– Packaged APIs – for tables, transactions and business entities
Order
Table
Item
Table
Application
Code
Intermediate Layer
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 191
With a data access layer, I can...
• Change/improve my implementation with minimal impact on my application code. – The underlying data model is constantly changing.
– We can depend on Oracle to add new features.
– We learn new ways to take advantage of PL/SQL.
• Vastly improve my SQL-related error handling. – Do you handle dup_val_on_index for INSERTs,
too_many_rows for SELECT INTOs, etc?
• Greatly increase my productivity – I want to spend as much time as possible
implementing business requirements.
11g_frc_demo.sql 11g_emplu.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 192
Example: Quest Code Tester backend
• For each table, we have three generated packages:
– <table>_CP for DML
– <table>_QP for queries
– <table>_TP for types
• And usually an "extra stuff" package with custom SQL logic and related code:
– <table>_XP
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 193
How to implement data encapsulation • It must be very consistent, well-designed and
efficient - or it will not be used.
• Best solution: generate as much of the code as possible.
– This includes products like APEX and Hibernate that generate lots of their own SQL for you.
• Any custom SQL statements should be written once and placed in a shareable container (usually a package).
TAPI generator:
Quest CodeGen Utility at www.ToadWorld.com/SF
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 194
Conclusions
• SQL statements are among the most critical parts of your application.
• You should have a clearly defined set of guidelines about when, where and how to write SQL.
• Most important: Don't repeat SQL statements (a form of hard-coding).
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 195
Hiding the Mechanics
• When you hard-code a "magic value," you write that value directly into your code as a literal.
– Over and over again; the value is "exposed".
• When you "expose" the way you get things done, you are hard-coding a particular implementation.
– The more you repeat it, the more difficult it is to change that implementation in the future.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 196
Everything Changes, Hide the Details
• Even if the users don't change their minds, we (developers) and Oracle technology change. – And let's face it: sometimes the way that Oracle
implements things is not ideal.
• So assume that whatever you are working on will change – and hide it behind an API. – Logging errors and tracing execution
– Calls to Oracle built-ins, like UTL_FILE.
– Soft-code transaction boundaries
– Watch out for SYSDATE
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 197
Logging Errors
• We usually, but not always, want to write error information out to a log table. How's this?
WHEN NO_DATA_FOUND THEN l_code := SQLCODE; INSERT INTO errlog VALUES ( l_code , 'No company for id ' || TO_CHAR ( v_id ) , 'fixdebt', SYSDATE, USER ); WHEN OTHERS THEN l_code := SQLCODE; l_errm := SQLERRM; INSERT INTO errlog VALUES (l_code, l_errm, 'fixdebt', SYSDATE, USER ); RAISE; END;
It's easy to "read" but only because it exposes the logging mechanism.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 198
Hide how and what you log
• Don't call RAISE_APPLICATION_ERROR.
• Don't explicitly insert into log table or write to file.
• Don't call all the useful built-in functions in each handler.
• Do use a generic and shared error management utility. – Check out Quest Error Manager at PL/SQL Obsession for an example.
WHEN NO_DATA_FOUND THEN q$error_manager.register_error ( text_in => 'No company for id ' || TO_CHAR ( v_id )); WHEN OTHERS THEN q$error_manager.raise_unanticipated ( name1_in => 'COMPANY_ID', value1_in => v_id); END;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 199
Execution Tracing (Instrumentation)
• We often need to retrieve additional, application-specific information from our code while running.
– Especially in production.
• DBMS_OUTPUT.PUT_LINE is the "default" tracing mechanism – and should never appear in your application code.
– You are exposing the trace/display mechanism.
– Too many drawbacks, too little flexibility.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 200
Alternative Tracing Mechanisms
• The Quest Error Manager available at
www.ToadWorld.com/SF offers built-in tracing.
• The demo.zip watch.pkg offers lots of flexibility.
• OTN samplecode logger utility, designed
primarily for use with APEX
• The log4plsql open source utility
• DBMS_APPLICATION_INFO
– Writes to V$ views
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 201
Hiding the Mechanics - Conclusions • Repeat after me: Everything is going to
change.
• When you hide the mechanics, how you get things done, behind a procedure or function, you are "liberated."
– Change the implementation, and you don't need to change all the places in which it is used.
• Back to that same principle:
Never Repeat Anything.
Aim for Single Point of Definition.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 202 202
Hiding stuff – a great career move!
• By hiding all forms of hard-coding behind subprograms, I am in a good position to....
• Hide my mistakes – Does the query have a bug in it? OK, fix the one instance of
the query inside my function. I don't have to tell everyone about it....
• Get a promotion – I can improve my application code much more quickly than
those who hard-code SQL.....
– The Result Cache is a great example of this.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 203
Say goodbye to hard coding!
• It's not all that difficult to do, once you recognize all the different ways that hard coding can manifest itself in your code.
• Repeat nothing: become allergic to redundant repetition.
• Aim for a "single point of definition" in everything you write.
• Hide, hide, hide: values, implementations, workarounds
Copyright 2010 Steven Feuerstein - Page 203
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 204
Your Reward
• Elegant, functional code that you and others can maintain easily
• The respect of your peers
• A deep sense of satisfaction with a job well done
• The opportunity to continue making a very fine living, mostly from just thinking about abstractions.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 205
Extreme Modularization
• Spaghetti code is the bane of a programmer's existence.
• It is impossible to understand and therefore debug or maintain code that has long, twisted executable sections.
• Fortunately, it is really easy to make spaghetti code a thing of the past.
Organize your code so that the
executable section has no more than fifty lines of code.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 206
Fifty lines of code? That's ridiculous!
• Of course you write lots more than 50 lines of code in your applications.
• The question is: how will you organize all that code?
• Turns out, it is actually quite straightforward to organize your code so that it is transparent in meaning, with a minimal need for comments.
• Key technique: local or nested subprograms.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 207
Let’s write some code!
• My team is building a support application. Customers call with problems, and we put their call in a queue if it cannot be handled immediately.
– I must now write a program that distributes unhandled calls out to members of the support team.
• Fifty pages of doc, complicated program!
While there are still unhandled calls in the queue, assign them to employees who are under-utilized (have fewer calls assigned to
them then the average for their department).
But there is an "executive
summary"
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 208
First: Translate the summary into code.
• A more or less direct translation. No need for comments, the subprogram names "tell the story" – but those subprograms don't yet exist!
PROCEDURE distribute_calls ( department_id_in IN departments.department_id%TYPE) IS BEGIN WHILE ( calls_are_unhandled ( ) ) LOOP FOR emp_rec IN emps_in_dept_cur (department_id_in) LOOP IF current_caseload (emp_rec.employee_id) < avg_caseload_for_dept (department_id_in) THEN assign_next_open_call (emp_rec.employee_id); END IF; END LOOP; END LOOP; END distribute_calls;
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 209
Explanation of Subprograms
• Function calls_are_unhandled: takes no arguments, returns TRUE if there is still at least one unhandled call, FALSE otherwise.
• Function current_caseload: returns the number of calls (case load) assigned to that employee.
• Function avg_caseload_for_dept: returns the average number of calls assigned to employees in that department.
• Procedure assign_next_open_call: assigns the employee to the call, making it handled, as opposed to unhandled.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 210
Next: Implement stubs for subprograms
• These are all defined locally in the procedure.
PROCEDURE call_manager.distribute_calls ( department_id_in IN departments.department_id%TYPE) IS FUNCTION calls_are_handled RETURN BOOLEAN IS BEGIN ... END calls_are_handled; FUNCTION current_caseload ( employee_id_in IN employees.employee_id%TYPE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; FUNCTION avg_caseload_for_dept ( employee_id_in IN employees.employee_id%TYPE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; PROCEDURE assign_next_open_call ( employee_id_in IN employees.employee_id%TYPE) IS BEGIN ... END assign_next_open_call; BEGIN
locmod_step_by_step.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 211
About local or nested subprograms
• They can be called only from within the block in which they are defined. – They can reference any variables defined in the parent
block.
– Watch out for "global" references.
• Only procedures and functions can be nested. – No packages within packages
– No object types
– No triggers
• Use these instead of nested blocks. – You replace code with a name – tell the story!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 212
Next: Think about implementation of just this level.
• Think about what the programs need to do.
• Think about if you or someone has already done it. Don’t reinvent the wheel!
Hey! Just last week I wrote another function that is very similar to
current_caseload. It is now "buried" inside a procedure named
show_caseload. I can’t call it from distribute_calls, though. It is local,
private, hidden.
Should I copy and paste? No! I should extract the program and
expand its scope.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 213
Next: Isolate and refactor common code.
• Now current_caseload is at the package level and can be called by any program in the package.
CREATE OR REPLACE PACKAGE BODY call_manager IS FUNCTION current_caseload ( employee_id_in IN employees.employee_id%TYPE , use_in_show_in IN BOOLEAN DEFAULT TRUE) RETURN PLS_INTEGER IS BEGIN ... END current_caseload; PROCEDURE show_caseload ( department_id_in IN departments.department_id%TYPE) IS BEGIN ... END show_caseload; PROCEDURE distribute_calls ( department_id_in IN departments.department_id%TYPE ) IS BEGIN ... END distribute_calls; END;
current_ caseload
distribute _calls
show_ caseload
Note the increased complexity,
needed to ensure backward compatibility.
locmod_step_by_step.sql
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 214
Next: Reuse existing code whenever possible.
• Just last week, Sally emailed all of us with news of her call_util package. – Returns average workload of employee and much more.
– Just what I need! Don’t have to build it myself, just call it.
BEGIN WHILE ( calls_are_unhandled ( ) ) LOOP FOR emp_rec IN emps_in_dept_cur (department_id_in) LOOP IF current_caseload (emp_rec. employee_id) < call_util.dept_avg_caseload (department_id_in) THEN assign_next_open_call (emp_rec.employee_id); END IF; END LOOP; END LOOP; END distribute_calls;
This program has the widest scope possible: it can be executed by any schema with execute authority on the call_util package, and by any program within the owning schema.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 215
Next: Implement what’s left.
• Now I am left only with program-specific, nested subprograms.
• So I move down to the next level of detail and apply the same process. – Write the “executive summary” first.
– Keep the executable section small.
– Use local modules to hide the details.
• Eventually, you get down to the “real code” and can deal with the actual data structures and algorithms without being overwhelmed.
locmod_step_by_step.sql
topdown*.*
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 216
Challenges of Nested Subprograms
• Requires discipline: always be on the lookout for opportunities to refactor.
• Need to read from the bottom, up. – Takes some getting used to.
• Sometimes can feel like a "wild goose chase". – Where is the darned thing actually implemented? – Your IDE should help you understand the internal
structure of the program.
• You cannot directly test nested subprogams. • But how do you decide when a module should be
local or defined at a “higher” level?
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 217
Rule: Define subprograms close to usage.
• When should the program be nested? Private to the package? Publicly accessible?
• The best rule to follow is: Define your subprograms as close as possible to their usage(s).
• The shorter the distance from usage to definition, the easier it is to find, understand and maintain that code.
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 218
Conclusions – Nested Subprograms • Write tiny chunks of code.
– Your programs will be transparent and readable, to you and everyone else.
– They will contain many fewer bugs.
• The quality of your code will be instantly transformed!
• "All" it takes is discipline.
– No special tools required (but a top-notch IDE to will make your job much easier).
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 219
Make the Most of the Best of Oracle PL/SQL!
• This language is not evolving very rapidly these days (less change than in SQL).
• Make sure that you are aware of key new (and existing) features, and put them to use.
• Always prioritize the maintainability of your code.
– It's going to be around for YEARS to come!
Oracle PL/SQL Programming
Copyright 2013 Feuerstein and Associates Page 220
Websites for PL/SQL Developers
www.plsqlchallenge.com Daily PL/SQL quiz with weekly and monthly prizes
www.plsqlchannel.com 27+ hours of detailed video training on Oracle PL/SQL
www.stevenfeuerstein.com Monthly PL/SQL newsletter
www.toadworld.com/SF Quest Software-sponsored portal for PL/SQL developers