PL/SQL Programming Tips

(1)

--- By Yu, Fang (franky.yu@163.com)

PL/SQL Programming Tips

Naming Conventions

Why need naming convention

Good and consistent naming manner, obviously, will make the PL/SQL code easier to read and maintain. The result is that the code is most likely to be more robust and with higher quality. What’s more, it will win higher reputation for the programmer.

Some facts that matter

Since PL/SQL language is closely associated with Oracle, the variable names used in the PL/SQL code are limited to the restriction of Oracle.

- The length of the names cannot be longer than 30 characters. - The name is case insensitive.

General Guidelines

There are some general guidelines for the naming manner for the PL/SQL code. - Use meaningful and specific names, abbreviations and table/column alias.

- Do not use reserved words as variable names. Use the following SQL statement to see which words are reserved by Oracle.

SELECT keyword FROM v$reserved_words;

- Avoid redundant or meaningless prefixes and suffixes. Remember there is 30 characters length restriction. For example, there is no need to add “_table” as the suffix of the table name. - Do not quote name with double quotes (“”). Following example illustrates that if the table name

is double quoted, it will causes some troubles when querying the table. SQL>createtable"HelloWorld"(id number);

Table created.

SQL>select * from helloworld; select * from helloworld *

ERRORat line 1:

ORA-00942:tableor view does not exist SQL>select * from HelloWorld;

select * from HelloWorld *

ERRORat line 1:

ORA-00942:tableor view does not exist SQL>select * from"HelloWorld"; norows selected

(4)

Naming Conventions

The following tables will illustrate the naming conventions for pl/sql variables, cursors, collections and oracle objects.

- Naming Conventions for Variables

Type of Element Naming Convention Example

Variable declared in a PL/SQL block l_<xxx> l_emp_no

Constant declared in a PL/SQL block c_<xxx> c_max_str_length Variable declared at the package level g_<xxx> g_analysis_plan Constant declared at the package level gc_<xxx> gc_max_str_length IN parameter p_<xxx>_in or p_<xxx>_i p_emp_no_in OUT parameter p_<xxx>_out or p_<xxx>_o p_emp_no_out IN OUT parameter p_<xxx>_inout or p_<xxx>_io p_emp_no_inout

Exception variable e_<xxx> e_name_not_found

- Naming Conventions for Cursors

Explicit cursor [l|g]_<xxx>_cur l_employees_cur g_employees_cur Cursor variables [l|g]_<xxx>_cv l_employees_cv

g_employees_cv

- Naming Conventions for Records and Collections

Record types [l|g]_<xxx>_rt l_emp_info_rt

Record variables The same as general variable, in singular form l_employee_info

Associate Array type [l|g]_<xxx>_aat l_employees_aat

Nested Table type [l|g]_<xxx>_ntt l_employees_ntt

Varray type [l|g]_<xxx>_vat l_employees_vat

Collection varaibles The same as general variable, in plural form l_employees - Naming Conventions for Database Objects

Types – Objects t_<xxx>[_o] t_user_objects

Types – Nested Table t_<xxx>_t[able] t_user_objects_table

Packages pack_<xxx> pack_fermat

Triggers tri_<xxx> tri_lo_entity

Sequences seq_<xxx> seq_lo_entity

Views v_<xxx> or mv_<xxx> v_lo_entity Indexes i[n]_<xxx> or ui[n]_<xxx> i1_lo_entity

Synonyms s_<xxx> s_entity

(5)

Coding Style

General Coding Style

- Keywords are written in uppercase while variable names are written in lowercase.

- Use 3 white spaces as the code line indention, don’t use tab keystroke, as different platforms treat the tab keystroke differently.

- Specify table columns explicitly in SELECT and INSERT statements. - Keep SQL statement keywords right-aligned or left-aligned. The table below shows the general PL/SQL coding style.

PROCEDURE set_salary(p_empno_in IN emp.empno%TYPE) IS

CURSOR l_emp_cur(p_empno emp.empno%TYPE) IS

SELECT ename , sal FROM emp

WHERE empno = p_empno ORDERBY ename;

l_emp l_emp_cur%ROWTYPE; l_new_sal emp.sal%TYPE;

BEGIN

OPEN l_emp_cur(p_empno_in); FETCH l_emp_cur INTO l_emp; CLOSE l_emp_cur;

--

get_new_salary (p_empno_in => in_empno , p_sal_out => l_new_sal); -- Check whether salary has changed

IF l_emp.sal <> l_new_sal THEN UPDATE emp

SET sal = l_new_sal WHERE empno = p_empno_in; ENDIF;

END set_salary;

Some Examples of SQL Statement Formatting

- Write the whole SQL statement in one line

SELECTe.job,e.deptno FROM emp eWHEREe.name='SCOTT'ANDe.sal >100000 - Keep the SQL statement keywords right-aligned

SELECTe.JOB, e.deptno FROM emp e

WHERE e.name='SCOTT' ANDe.sal >100000

(6)

- Keep the SQL statement keywords left-aligned SELECT e.JOB,

e.deptno FROM emp e

WHERE e.name='SCOTT' AND e.sal >100000

Tools that can format SQL Statement

- Instant SQL Formatter (http://www.dpriver.com/pp/sqlformat.htm)

- Oracle SQL Developer

(7)

Code Commenting

Obviously, the comment for the code is very important. It can not only make the code easier to understand, but also can be used by some tools to generate code documentation.

- PLDOC

Pldoc is an open-source utility for generating HTML documentation of code written in Oracle PL/SQL. Please refer to pldoc official site (http://pldoc.sourceforge.net/maven-site) for details. Below is a code example which follows the pldoc convention.

PACKAGE pack_syspar IS /**

* Project: Test Project (<a href="http://pldoc.sourceforge.net">PLDoc</a>) * Description: System Parameters Management

* DB impact: YES * Commit inside: NO * Rollback inside: NO * @headcom

*/

/** Gets system parameter value. * @param p_name parameter name * @return parameter value

* @throws no_data_found if no parameter with such name found */

FUNCTION get_char (p_name_in VARCHAR2)RETURNVARCHAR2; END pack_syspar ;

/

- For those standalone (i.e. at schema level) PL/SQL code units, the comments for the units should not put in front of the code unit, otherwise the comments will not kept in the data dictionary. Below is an example of trigger comments.

Instead of putting the comments in front of the trigger TRI_MYTAB_BI…

/******************************************************************* Copyright YYYY by <Company Name>

*******************************************************************/ CREATEORREPLACETRIGGER tri_mytab_bi

BEFOREINSERTON mytab FOREACHROW

DECLARE

-- local variables here BEGIN

NULL;

(8)

The comments should be put “inside” the trigger definition, please note the comma behind the trigger comments.

CREATEORREPLACETRIGGER tri_mytab_bi BEFOREINSERTON mytab

FOREACHROW DECLARE

-- local variables here BEGIN

NULL;

END tri_mytab_bi

/******************************************************************* Copyright YYYY by <Company Name>

*******************************************************************/;

Best Practices

Literals, constants and variables

- Avoid using literals directly in the code, abstract literals behind package constants. For example, instead of using the literal ‘LEADER’ in the code directly like blow…

-- Bad DECLARE

l_function player.function_name%TYPE; BEGIN SELECTp.function INTO l_function FROM player p WHERE … -- IF l_function = ‘LEADER’ THEN …

Try to extract the literal ‘LEADER’ inside one package, like PACK_CONSTANTS. And then reference this constant in the code unit…

-- Good

CREATEORREPLACEPACKAGE pack_constants IS

gc_leader CONSTANT player.function_name%TYPE:= ‘LEADER’; END pack_constants ;

/

(9)

l_function player.function_name%TYPE; BEGIN

SELECTp.functionINTO l_function FROM player p WHERE … --

IF l_function = pack_constants.gc_leader THEN

- Use a function to return a constant if the “constant” value may change at times. This way, the code units that depend on this function will not become invalid and need recompile if the value returned by the function is changed.

- Do not use public global variables, use getter/setter or parameters-passing.

- Never put all of a system’s constants in a single package. Try to put the constants in separate packages according to the constants usage.

- Avoid initializing variables using functions in the declaration section. Instead, assign the

variables using functions in the body section. This way, if the function raises the exception, such exception can be caught.

For example, don’t write the code like this…

-- Bad DECLARE

l_code_section VARCHAR2(30):= ‘TEST_PCK’;

l_company_name VARCHAR2(30):= util_pck.get_company_name(in_id =>47); BEGIN

… END;

Write it like this…

-- Good DECLARE

l_code_section VARCHAR2(30):= ‘TEST_PCK’; l_company_name VARCHAR2(30);

BEGIN <<init>> BEGIN

l_companyName := util_pck.get_company_name(inId =>47); EXCEPTION

WHENVALUE_ERRORTHEN …; END;

END;

Data Types

- Anchor parameters and variables using %TYPE (or %ROWTYPE for cursor or entire table). When the table structure or cursor definition changes, the variables don’t need to update.

- Use SUBTYPE to avoid hard-coded variable length declarations. See below for an example. Since Oracle restricts the length of the object name to be no longer than 30, so defining the string

(10)

(varchar2) variables with the length of 30 is a common practice. Instead of hard coding 30 in the variable length declarations, try to define a subtype in a separate package.

Instead of declaring the variable l_code_section with the length of 30…

-- Bad

l_code_section VARCHAR2(30):= ‘TEST_PCK’;

… Try to create a package PACK_TYPES to define the subtypes and use these subtypes to define the variables.

-- Good

CREATEORREPLACEPACKAGE PACK_TYPES AS

SUBTYPE t_oracle_name ISVARCHAR2(30);

SUBTYPE t_oracle_max_varchar ISVARCHAR2(4000); subtype t_plsql_max_varchar ISVARCHAR2(32767); END PACK_TYPES;

/

l_code_section PACK_TYPES.t_oracle_name := ‘TEST_PCK’;

- Avoid using CHAR data type, stick to VARCHAR2 data type. Use VARCHAR2(1) to replace CHAR. - Never use zero-length strings to substitute NULL.

-- Bad

l_char := ‘’; -- Good

l_char :=NULL;

- Use CLOB, NCLOB, BLOB or BFILE for unstructured content in binary or character format. Do not use LONG or LONG RAW. LOB data types provide efficient, random, piece-wise access to the data. Some differences between LOB data types and LONG (LONG RAW) are listed below…

o A table can contain multiple LOB columns but only one LONG column.

o A table containing one or more LOB columns can be partitioned, but a table containing a LONG column cannot be partitioned.

o The maximum size of a LOB is 8 terabytes, and the maximum size of a LONG is only 2 gigabytes.

o LOBs support random access to data, but LONGs support only sequential access.

o LOB data types (except NCLOB) can be attributes of a user-defined object type but LONG data types cannot.

- NUMBER vs. PLS_INTEGER vs. SIMPLE_INTEGER (New in 11g, always not null).

When declaring an integer variable, PLS_INTEGER is the most efficient numeric data type because its value requires less storage than INTEGER or NUMBER values, which are represented internally as 22-byte Oracle numbers. Also, PLS_INTEGER operations use machine arithmetic, so they are faster than NUMBER or INTEGER (integer is subtype of NUMBER) operations, which use

(11)

library arithmetic. SIMPLE_INTEGER is introduced in 11g, if the integer will always not null use this data type. SIMPLE_INTEGER is a subtype of the PLS_INTEGER data type and can dramatically increase the speed of integer arithmetic in natively compiled code, but only shows marginal performance improvements in interpreted code.

- Avoid using ROWID or UROWID (Universal ROWID). It is a dangerous practice to store ROWIDs in a table, except for some very limited scenarios of runtime duration. Any manually explicit or system generated implicit table reorganization will reassign the row’s ROWID and break the data consistency.

- Minimize data type conversion as it takes time. DECLARE

n NUMBER:=0; c CHAR(2); BEGIN

n := n +15; -- converted implicitly; slow

n := n +15.0; -- not converted; fast

c :=25; -- converted implicitly; slow

c :=TO_CHAR(25);-- converted explicitly; still slow

c :='25'; -- not converted; fast END;

/

- Bigger is better for VARCHAR2 variables. VARCHAR2 variables below than 4001 bytes (2000 bytes in 9i) have their memory allocated in full at declaration time. VARCHAR2 variables larger than 4000 bytes (1999 bytes in 9i) have their memory allocated at assignment time, based on the data assigned to them. As a result, defining large variables that contain little data wastes memory, unless you move above the optimization threshold at which point no memory is wasted. If in doubt, define VARCHAR2 (32767) variables.

Flow Control

- Always label the loops. The example below labels the FOR loop with the name of “process_employees”.

BEGIN

<<process_employees>>

FOR r_employee IN(SELECT * FROM emp) LOOP

…

ENDLOOP process_employees; END;

- Always use a FOR loop to process the complete cursor results unless use the bulk operation - Always use a WHILE loop to process a loose array. The collection “t_employees” in the example

below is a loose array, so use the WHILE loop. DECLARE

(12)

BEGIN

l_index := t_employees.FIRST();

<<ProcessEmployees>> WHILE l_index ISNOTNULL LOOP

…

l_index := t_employees.NEXT(l_index); ENDLOOP process_employees;

END;

- Always use FIRST..LAST when iterating through collections with a loop BEGIN

<<process_employees>>

FOR idx IN t_employees.FIRST()..t_employees.LAST() LOOP

…

ENDLOOPprocess_employees; END;

- Always use EXIT WHEN instead of an IF statement to exit from a loop

-- Bad BEGIN <<process_employees>> LOOP ... IF... THEN EXIT process_employees; ENDIF; ...

ENDLOOP process_employees; END; -- Good BEGIN <<process_employees>> LOOP ...

EXIT process_employees WHEN(...); ENDLOOP process_employees;

END;

- Never EXIT from within any FOR loop, use LOOP or WHILE loop instead - Avoid hard-coded upper or lower bound values with FOR loops

(13)

Exception Handling

- Never handle unnamed exceptions using the error number, instead using “EXCEPTION_INIT” to name the exception. The example below illustrates this idea.

-- Bad

-- ORA-00001: unique constraint violated BEGIN

... EXCEPTION

WHENOTHERSTHEN IFSQLCODE=-1 THEN ... ENDIF; END; -- Good DECLARE e_employee_exists EXCEPTION;

PRAGMAEXCEPTION_INIT(-1, e_employee_exists); ...

BEGIN ... EXCEPTION

WHEN e_employee_exists THEN ...

END;

- Never assign predefined exception names to user defined exceptions

- Avoid use of WHEN OTHERS clause in an exception section without any other specific handlers.

-- Good EXCEPTION WHENDUP_VAL_ON_INDEX THEN update_instead (...); WHENOTHERS THEN err.log; RAISE;

- Avoid use of EXCEPTION_INIT pragma for -20, NNN error (-20,999 ~ -20,000)

(14)

Dynamic SQL

- Always use a string variable to execute dynamic SQL, don’t use the string literal behind the “EXECUTE IMMEDIATE” directly. The reason is that using string variable is easier for debug. See the example below for the illustration.

-- Bad DECLARE

l_empno emp.empno%TYPE:=4711; BEGIN

EXECUTEIMMEDIATE ‘DELETEFROM emp WHERE epno =:p_empno’ USING l_empno; END;

-- Good DECLARE

l_empno emp.empno%TYPE:=4711; l_sql VARCHAR2(32767);

BEGIN

l_sql := ‘DELETEFROM emp WHERE epno =:p_empno’; EXECUTEIMMEDIATE l_sql USING l_empno;

EXCEPTION WHENothers THEN

DBMS_OUTPUT.PUT_LINE(l_sql); END;

- Use bind variables. Do not concatenate strings unless needed to identify schema or table/view. - Format dynamic SQL strings as nicely as in static SQL.

Packages, Functions and Procedures

- Try to keep packages small. Include only few procedures and functions that are used in the same context.

- Always use forward declaration for private functions and procedures in packages. - Avoid standalone procedures or functions – put them in packages.

- Avoid using RETURN statements in a procedure (one way in one way out). - Try to use no more than one RETURN statement within a function. - Never use OUT parameters to return values from a function.

CASE/IF/DECODE/NVL/NVL2/COALESCE

- Try to use CASE rather than an IF statement with multiple ELSIF paths.

-- Bad

IF l_color = ′red′ THEN

...

ELSIF l_color = ′blue′ THEN

(15)

...

ELSIF l_color = ′black′ THEN

... -- Good CASE l_color

WHEN ′red′ THEN... WHEN ′blue′ THEN... WHEN ′black′ THEN... END

- Use CASE rather than DECODE. DECODE is SQL function which cannot be used in PL/SQL. - Always use COALESCE instead of NVL if the second parameter of the NVL function is a function

call or a SELECT statement. The NVL function always evaluates both parameters before deciding which one to use. This can be harmful if parameter 2 is either a function call or a select

statement, as it will be executed regardless of whether parameter 1 contains a NULL value or not.

-- Bad

SELECTNVL(dummy, function_call()) FROM dual; -- Good

SELECTCOALESCE(dummy, function_call()) FROM dual;

- Always use CASE instead of NVL2 if parameter 2 or 3 of NVL2 is either a function call or a SELECT statement.

Some Built-in Utilities

PLSQL_OPTIMIZE_LEVEL

The PLSQL_OPTIMIZE_LEVEL parameter was introduced in 10g to control how much optimization the compiler performs. There are 4 levels:

- 0: Code will compile and run in a similar way to 9i and earlier. New actions of BINARY_INTEGER and PLS_INTEGER lost.

- 1: Performs a variety of optimizations, including elimination of unnecessary computations and exceptions. Does not alter source order.

- 2: Performs additional optimizations, including reordering source code if necessary. This is the default setting in 10g and 11g. The optimizer may inline code automatically.

- 3: New in 11g.

The optimization level associated with the library unit (plsql code) is visible using the views

(16)

select name,type,plsql_optimize_levelfrom user_plsql_object_settings;

To change the value of the PLSQL_OPTIMIZE_LEVEL, issue the following statement… ALTERSESSIONSETplsql_optimize_level=0;

ALTERSESSIONSETplsql_optimize_level=1; ALTERSESSIONSETplsql_optimize_level=2; ALTERSESSIONSETplsql_optimize_level=3;

PLSQL_WARNINGS

Compiler warnings were introduced in 10g to provide programmers with indications of possible code improvements. The PLSQL_WARNING parameter can be set using ALTER SYSTEM or ALTER SESSION commands or the DBMS_WARNING package.

-- Disable warnings in current session

ALTERSESSIONSETPLSQL_WARNINGS='DISABLE:ALL';

EXECDBMS_WARNING.set_warning_setting_string('DISABLE:ALL','SESSION'); -- Enable warnings in current session

ALTERSESSIONSETPLSQL_WARNINGS='ENABLE:ALL';

EXECDBMS_WARNING.set_warning_setting_string('ENABLE:ALL','SESSION');

The warning level associated with the library unit is visible using the

ALL/USER/DBA_PLSQL_OBJECT_SETTINGS and ALL/USER/DBA_WARNING_SETTINGS views. SELECTname,plsql_warningsFROM user_plsql_object_settings;

SELECT object_name, warning, setting FROM user_warning_settings;

PLSQL_DEBUG

The parameter PLSQL_DEBUG specifies whether or not PL/SQL library units will be compiled for debugging. Its value can be true or false, and can be modified by ALTER SESSION or ALTER SYSTEM command.

Please note that when PLSQL_DEBUG is set to true, PL/SQL library units will always compiled INTEPRETED in order to be debuggable.

Native Compilation

PL/SQL code is interpreted by default. Prior to 11g, native compilation converts PL/SQL to Proc*C, which is then compiled in shared libraries. As a result, the parameter plsql_native_library_dir should be set to store the C libraries. In Oracle 11g, PL/SQL native compilation requires no C compiler. By setting the PLSQL_CODE_TYPE to the value of NATIVE, the PL/SQL code is compiled directly to machine code and

(17)

stored in the SYSTEM tablespace. When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The (USER/ALL/DBA)_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.

Please note that native compilation will only improve the speed of procedure code, but has little effect on the performance of SQL. When code performs lots of mathematical operations, native compilation can produce considerable performance improves.

Set the parameter PLSQL_NATIVE_LIBRARY_DIR in the database with version earlier than 11g…

-- Prior to 11g, set the parameter PLSQL_NATIVE_LIBRARY_DIR first -- No need in 11g

CONN system/passwordASSYSDBA

ALTERSYSTEMSET plsql_native_library_dir ='/u01/app/oracle/admin/DB10G/native';

Set the parameter PLSQL_CODE_TYPE in session level… ALTERSESSIONSETplsql_code_type='INTERPRETED'; ALTERSESSIONSETplsql_code_type='NATIVE';

Query the code compilation type…

selectname,plsql_code_type from user_plsql_object_settings;

Conditional Compilation

Conditional compilation was introduced in 10g to allow PL/SQL source code to be tailored to specific environments using compilation directives. The PLSQL_CCFLAGS clauses is used to set the compiler flags, and the compiler flags are identified by the “$$” prefix in the PL/SQL code. Conditional control is

provided by the $IF-$THEN-$ELSE syntax. The database source (can be viewed through the view user_source) contains all the directives, but the post-processed source is displayed using the DBMS_PREPROCSSOR package.

Set the parameter PLSQL_CCFGAS at code unit level, session level and system level…

ALTERPROCEDUREdebugCOMPILEPLSQL_CCFLAGS='debug_on:TRUE, show_date:TRUE'REUSE SETTINGS;

ALTERSESSIONSETPLSQL_CCFLAGS='max_sentence:100';

ALTERSYSTEMSETPLSQL_CCFLAGS='VARCHAR2_SIZE:100, DEF_APP_ERR:-20001';

Treat different Oracle versions…

(18)

--- By Yu, Fang (franky.yu@163.com) -- version 10 and earlier code

$ELSIF dbms_db_version.ver_le_11 $THEN -- version 11 code

$ELSE

-- version 12 and later code

$END

Print the post-processed source code… BEGIN DBMS_PREPROCESSOR.print_post_processed_source( object_type =>'PROCEDURE', schema_name =>USER, object_name =>'DEBUG'); END; /

DML Error Logging

By default, when a DML (update, delete, insert, merge) statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected. Before Oracle 10.2, the only way around this issue was to process each row individually, preferably with a bulk operation using a FORALL loop with the SAVE EXCEPTIONS clause. In Oracle 10.2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors. Exceptional rows are added to a specially-created error table for further investigation. There are two components to DML error logging as follows:

- LOG ERRORS clause to DML statements; and - DBMS_ERRLOG package for managing error tables Create error table for the “DML” table…

BEGIN

DBMS_ERRLOG.CREATE_ERROR_LOG(

dml_table_name =>'TGT', --<-- required

err_log_table_name =>'TGT_ERRORS' --<-- optional

); END;

/

Specify the LOG ERRORS clause in the DML statement… INSERTINTO tgt

SELECT * FROM src

(19)

--- By Yu, Fang (franky.yu@163.com) REJECTLIMITUNLIMITED;

Use contexts to store global data

A context is a set of application-defined attributes associated with a namespace. Context attributes can be read by the SYS_CONTEXT function, but must be set by the package specified when the context is created. Consider using the “ACCESSED GLOBALLY” clause on the context to make the context information available to all session. The context values set in the session can be viewed in the table SESSION_CONTEXT.

Create a context “GLOBAL_CONTEXT” and the context attribute values should be set in the package “GLOBAL_CONTEXT_API”…

CREATE ORREPLACECONTEXT global_context USING global_context_api;

The package that used to set the context value…

CREATEORREPLACEPACKAGEBODY global_context_api IS -- ---PROCEDURE set_parameter (p_name IN VARCHAR2, p_value IN VARCHAR2)IS

-- ---BEGIN

DBMS_SESSION.set_context('global_context', p_name, p_value); END set_parameter;

-- ---...

Set the context value and retrieve the context value… global_context_api.set_parameter('audit_on','N');

DBMS_OUTPUT.put_line('audit_on: ' || SYS_CONTEXT('global_context','audit_on'));

DBMS_APPLICATION_INFO

The DBMS_APPLICATION_INFO package allows programs to add information to the V$SESSION and V$SESSION_LONGOPS views. It can also be used to register long operations which useful for monitoring the progress of jobs.

Monitor the PL/SQL block execution via V$SESSION… BEGIN

DBMS_APPLICATION_INFO.set_module( module_name =>'module_action.sql',

(20)

--- By Yu, Fang (franky.yu@163.com) action_name =>'Starting');

DBMS_APPLICATION_INFO.set_client_info(client_info =>'Part of PL/SQL tuning demo.'); FOR i IN1..60LOOP

-- Update the current action.

DBMS_APPLICATION_INFO.set_action(action_name =>'Processing row ' || i);

-- Do some work...

DBMS_LOCK.sleep(1); END LOOP;

DBMS_APPLICATION_INFO.set_action(action_name =>'Finished'); END;

/

-- query in another session

SELECTsid, serial#, module, action, client_info FROM v$session

WHERE module ='module_action.sql';

Monitor time consuming operation via V$SESSION_LONGOPS… DECLARE l_rindex PLS_INTEGER; l_slno PLS_INTEGER; l_totalwork NUMBER; l_sofar NUMBER; l_obj PLS_INTEGER; BEGIN l_rindex :=DBMS_APPLICATION_INFO.set_session_longops_nohint; l_sofar :=0; l_totalwork :=100;

WHILE l_sofar <100LOOP -- Do some work DBMS_LOCK.sleep(0.5); l_sofar := l_sofar +1; DBMS_APPLICATION_INFO.set_session_longops( rindex => l_rindex, slno => l_slno, op_name =>'BATCH_LOAD', target => l_obj, context =>0, sofar => l_sofar, totalwork => l_totalwork, target_desc =>'BATCH_LOAD_TABLE', units =>'rows'); END LOOP; END;

(21)

--- By Yu, Fang (franky.yu@163.com) /

-- query in another session SELECT opname, target_desc, sofar, totalwork, units FROM v$session_longops

WHERE opname ='BATCH_LOAD';

DBMS_SESSION

Many applications manage user access internally and log into the database using a single user, which makes identifying who is doing what difficult. The DBMS_SESSION package allows to set the

CLIENT_IDENTIFIER column of the V$SESSION view. The client identifier is also included in the audit trail.

-- in session 1

EXECDBMS_SESSION.set_identifier(client_id =>'fang_test'); -- in session 2

EXECDBMS_SESSION.set_identifier(client_id =>'fang_test'); -- query the v$session

select * from v$session where client_identifier='fang_test';

DBMS_PROFILER

The DBMS_PROFILER package was introduced in Oracle 8i to make identification of performance bottlenecks in PL/SQL easier. It records the number of executions and the elapsed time for each line of code, not the execution order of the code.

DBMS_TRACE

The DBMS_TRACE package records the execution order of PL/SQL, which may help to detect unusual behavior not visible in the profiler results captured by DBMS_PROFILER.

Use DBMS_TRACE.set_plsql_trace for tracing…

BEGIN DBMS_TRACE.set_plsql_trace(DBMS_TRACE.trace_all_calls); trace_test(p_loops =>100); DBMS_TRACE.clear_plsql_trace; DBMS_TRACE.set_plsql_trace(DBMS_TRACE.trace_all_sql); trace_test(p_loops =>100); DBMS_TRACE.clear_plsql_trace;

(22)

DBMS_TRACE.set_plsql_trace(DBMS_TRACE.trace_all_lines); trace_test(p_loops =>100);

DBMS_TRACE.clear_plsql_trace; END;

/

Query table PLSQL_TRACE_RUNS and PLSQL_TRACE_EVENTS to view trace results… SELECT r.runid,

TO_CHAR(r.run_date,'DD-MON-YYYY HH24:MI:SS')AS run_date, r.run_owner

FROM plsql_trace_runs r ORDERBY r.runid; SELECTe.runid, e.event_seq,

TO_CHAR(e.event_time,'DD-MON-YYYY HH24:MI:SS')AS event_time, e.event_unit_owner, e.event_unit, e.event_unit_kind, e.proc_line, e.event_comment FROM plsql_trace_events e WHERE e.runid =3

ORDERBYe.runid,e.event_seq;

Some Traps

Pipelined table function and cardinality

By default, the cardinality of pipelined table functions is based on the block size, so joining operations can lead to performance issues.

Create a pipelined table function “GET_TAB”… CREATETYPE t_tf_row ASOBJECT(

id NUMBER,

description VARCHAR2(50) );

/

CREATETYPE t_tf_tab ISTABLEOF t_tf_row; /

CREATEORREPLACEFUNCTION get_tab RETURN t_tf_tab PIPELINED AS BEGIN

FOR i IN1..100LOOP

(23)

--- By Yu, Fang (franky.yu@163.com) END LOOP;

RETURN; END; /

Query the execution plan… set autotrace traceexplain select * fromtable(get_tab); Execution Plan

---Planhashvalue:3023735445

---| Id | Operation | Name | Rows | Bytes | Cost(%CPU)| Time |

---| 0 | SELECTSTATEMENT | | 8168 | 16336 | 24 (0)| 00:00:01 | | 1 | COLLECTION ITERATOR PICKLER FETCH| GET_TAB | | | | |

---set autotrace off

Please note that the ROWS returned shown in the execution plan is 8168 while the actual row returned by the function GET_TAB is 100! This will lead to performance issue if join the rows returned by this table function with other tables.

There are several ways to resolve this issue; the first one is to use the undocumented hint “cardinality” (available since Oracle 9i) as follows,

SQL>select/*+cardinality(100)*/ * fromTABLE(get_tab); Execution Plan

---In Oracle 10g and above, a new undocumented hint “opt_estimate” can be used. This hint seems doesn’t work that well as “cardinality”. See the example below,

(24)

--- By Yu, Fang (franky.yu@163.com) Execution Plan

---To use this hint, the scale_rows needs to be set correctly, which is calculated by the function of

round(actual rows/8168, 2) which is 0.01 in this case. However, seen from the execution plan the ROWS returned is still 8168! So use this hint with caution.

In Oracle 11.1, another new hint “dynamic_sampling” was introduced. The difference between this one and the two mentioned above is that this new hint is documented. That’s to say, this hint can be used safely.

Besides taking advantage of Oracle hints, Oracle 10g also introduced a new feature called Extensible Optimizer which can be used to tell the optimizer wheat the cardinality should be in a supported manner. The disadvantage of using extensible optimizer is that it is more complex than hints. First, need to change the definition of the function “GET_TAB” to add one parameter… CREATEORREPLACEFUNCTIONget_tab(p_cardinality ININTEGERDEFAULT1) RETURN t_tf_tab PIPELINED AS

BEGIN

FOR i IN1..100LOOP

PIPE ROW(t_tf_row(i,'Description for ' || i)); END LOOP;

RETURN; END; /

Create one type “T_PTF_STATS” to implement some ODCI interface functions… CREATEORREPLACETYPE t_ptf_stats ASOBJECT(

dummy INTEGER,

STATICFUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )RETURNNUMBER,

STATICFUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo,

(25)

--- By Yu, Fang (franky.yu@163.com) p_args IN SYS.ODCIArgDescList, p_cardinality ININTEGER

)RETURNNUMBER

); /

CREATEORREPLACETYPEBODY t_ptf_stats AS STATICFUNCTION ODCIGetInterfaces ( p_interfaces OUT SYS.ODCIObjectList )RETURNNUMBERIS

BEGIN

p_interfaces := SYS.ODCIObjectList(

SYS.ODCIObject ('SYS','ODCISTATS2') );

RETURN ODCIConst.success; END ODCIGetInterfaces;

STATICFUNCTION ODCIStatsTableFunction ( p_function IN SYS.ODCIFuncInfo, p_stats OUT SYS.ODCITabFuncStats, p_args IN SYS.ODCIArgDescList, p_cardinality ININTEGER

)RETURNNUMBERIS BEGIN

p_stats := SYS.ODCITabFuncStats(p_cardinality); RETURN ODCIConst.success;

END ODCIStatsTableFunction; END;

/

Associate the function “GET_TAB” with the type “T_PTF_STATS”… ASSOCIATESTATISTICSWITHFUNCTIONSget_tab USING t_ptf_stats;

Then the SQL explain plan shows the row count returned by the function is right… SQL>select * fromtable(get_tab(100));

Execution Plan

(26)

DBMS_RANDOM

DBMS_RANDOM is usually used to generate a random value. However, if don’t use this package with care, the values generated by this package may be not random. Below is an example…

Suppose there is one table name T which has one column of data type VARCHAR2. What’s more, there is one unique constraint on this column. Since the system doesn’t care the value of the string values as long as they are unique in the table, package DBMS_RANDOM can be used. Below is one PL/SQL function to generate the random string value,

CREATEORREPLACEFUNCTION GET_NEXT_STR RETURNVARCHAR2

IS

V_SEED VARCHAR2(50); BEGIN

SELECTTO_CHAR(SYSTIMESTAMP,'ddmmyyyyHH24MMSSFF')INTO V_SEED FROM DUAL; DBMS_RANDOM.SEED(V_SEED);

RETURN DBMS_RANDOM.STRING('A',20); END GET_NEXT_STR;

Before getting down to using this function in the table insertion SQL statement, perform a simple test to see what the values are generated…

set serveroutput on BEGIN FOR I IN1..10 LOOP DBMS_OUTPUT.PUT_LINE(GET_NEXT_STR); END LOOP; END;

And the results are like below…

AwPUykNKErDexsElUnwC snKBvcxsTUiMSLWuMCgo snKBvcxsTUiMSLWuMCgo snKBvcxsTUiMSLWuMCgo snKBvcxsTUiMSLWuMCgo snKBvcxsTUiMSLWuMCgo zNuLksgAvpBiSqXGqCeA zNuLksgAvpBiSqXGqCeA zNuLksgAvpBiSqXGqCeA zNuLksgAvpBiSqXGqCeA

What a surprise! The values generated by the function are not unique. The problem lies in the function GET_NEXT_STR uses the current timestamp as the SEED for the random function. Due to the precise of the timestamp and the fast execution speed of CPU, the seed value may be the same for several loops. That’s why the “random” values returned by the DBMS_RANDOM are not random. To resolve this issue, don’t use the timestamp as the seed, use the seed generated by the system automatically.

(27)

The literal string length limit in SQL statement

The maximum string length can be used in SQL statement is 4000 bytes while this value can be increased up to 32767 in PL/SQL.

See below for some tests about the maximum string length supported in SQL select clause… SQL>selectlength(rpad('A',3999,'*')) from dual; LENGTH(RPAD('A',3999,'*'))

---

3999

SQL>selectlength(rpad('A',4000,'*')) from dual; LENGTH(RPAD('A',4000,'*'))

---

4000

SQL>selectlength(rpad('A',4001,'*')) from dual; LENGTH(RPAD('A',4001,'*'))

---

4000

SQL>selectlength(rpad('A',40001,'*'))from dual; LENGTH(RPAD('A',40001,'*'))

--- 4000

The test result shows that the maximum string length can be supported in SQL select statement is 4000. Even use the RPAD function to construct a string with length larger than 4000, the function LENGTH still return 4000.

If reference one function which returns a string whose length larger than 4000, there will be an error raised. See the example below for illustration…

SQL>CREATEORREPLACEFUNCTION test_varchar2_max_length RETURNvarchar2AS 2 BEGIN

3 returnRPAD('A',4001,'*'); 4 END;

5 /

Function created.

SQL>select test_varchar2_max_length from dual; select test_varchar2_max_length from dual *

ERRORat line 1:

ORA-06502: PL/SQL:numericorvalueerror:characterstringbuffer too small ORA-06512:at"FRANK.TEST_VARCHAR2_MAX_LENGTH", line 3

SQL>

(28)

To resolve this issue, the return type of the function “test_varchar2_max_length” needs to be changed to CLOB or some collection type.

PL/SQL Programming Tips