How to avoid usage of Cursors in PL/SQL?

You can avoid usage of Cursor by directly using the underlying SQL in a for loop like shown
below:

BEGIN
FOR i IN
(SELECT ename,empno FROM emp
)
LOOP
BEGIN
--
dbms_output.put_line('Ename: '||i.ename||' Empno: '||empno);
--
END;
END LOOP;
END;

What is Global Temporary Tables and What

is it Purpose?
Global temporary table is also a table like a normal Oracle table but the data in this table is
session restricted. Such that data inserted by a session can only be accessed by that session, the
data is flushed at the end of the session.
Note that only table data is session specific, but physically table is available in all sessions.
Purpose of Global Temporary Tables (GTT)

1. GTTs are used in situations where data fetch and passage is not possible in single stretch.
2. GTTs are used in situations where we want to store data temporarily in a table for
calculating/pulling out some resultant data.

Syntax

CREATE GLOBAL TEMPORARY TABLE

(
[COLUMN DEFINTION]
) ON COMMIT [DELETE | PRESERVE] ROWS;

ON COMMIT DELETE ROWS

ON COMMIT DELETE ROWS sets the life of the data contained by the table to a single
TRANSACTION. The data is automatically flushed away after each COMMIT/ROLLBACK is
executed. These are Transaction-specific Temporary tables.
ON COMMIT PRESERVE ROWS
ON COMMIT PRESERVE ROWS restricts the life of the data to a single SESSION. Data is
preserved in the table for a SESSION only. These are Session-specific Temporary tables.

Why can’t we use SQLERRM and

SQLCODE in INSERT Statement?
Yes! We all know that cannot use SQLCODE, SQLERRM in INSERT Statement! but why??

SQLCODE, SQLERRM are predefined functions which are useful in Exception handling. We
can capture error code and error message from these functions in exceptions for a some piece of
PL/SQL code.

Let’s see what happens if we try to use SQLCODE, SQLERRM in INSERT Statement.

Create EMP and EMP_EXCEPTIONS tables using below table creation scripts:

--Creation of EMP Table

CREATE TABLE emp
(
empno NUMBER(4) NOT NULL,
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7, 2),
comm NUMBER(7, 2),
deptno NUMBER(2)
);

--Creation of EMP_EXCEPTIONS table

CREATE TABLE emp_exceptions
(
err_code VARCHAR2(20),
err_msg VARCHAR2(2000)
);

Run the below PL/SQL block of code which tries to find information of empno 1000, but here
EMP table is empty since we dint insert any records, so ideally this block of code should through
no data found error.

DECLARE
name emp.ename%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT ename INTO name FROM emp WHERE empno = 1000;
EXCEPTION
WHEN OTHERS THEN
INSERT INTO emp_exceptions
 (err_code,err_msg
) VALUES
(SQLCODE,sqlerrm
);
COMMIT;
END;
/

After running the above pl/sql code we can observer below error message since we are directly
using SQLCODE and SQLERRM in INSERT statement.

Error report:
ORA-06550: line 14, column 14:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 11, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 – “line %s, column %s:\n%s”
*Cause: Usually a PL/SQL compilation error.
*Action:

Why is the error coming

The purpose of above PL/SQL code is to capture the error information which occurred in a
previous SQL statement into some table using SQLCODE and SQLERRM in INSERT
statement. But what if the INSERT statement itself FAILS? then how will SQLCODE and
SQLERRM information will be captured for both previous SQL statement and current INSERT
statement? food for thought?? Yes! Ideally we can capture only one set of Error information
using SQLCODE and SQLERRM, its not possible to capture both the error information that is
the reason we CANNOT use SQLCODE and SQLERRM in INSERT statement.

Solution

we have to assign them to a variable and use the variables in INSERT statement as shown below.

DECLARE
name emp.ename%TYPE;
v_code NUMBER;
v_errm VARCHAR2(64);
BEGIN
SELECT ename INTO name FROM emp WHERE empno = 1000;
EXCEPTION
WHEN OTHERS THEN
v_code := SQLCODE;
v_errm := SUBSTR(sqlerrm, 1 , 64);
INSERT INTO emp_exceptions
(err_code,err_msg
) VALUES
(v_code,v_errm
);
COMMIT;
END;
/

What are the Advantages of Using Database

Triggers??
A PL/SQL program unit associated with a particular database table is called a database trigger. It
is used for :

1. Audit data modifications

2. Log events transparent
3. Enforce complex business rules
4. Maintain replica tables
5. Derive column values
6. Implement Complex security authorizations
7. Triggers can be used as an alternative method for implementing referential integrity
constraints
8. By using triggers, business rules and transactions are easy to store in database and can be
used consistently even if there are future updates to the database
9. It controls on which updates are allowed in a database
10. When a change happens in a database a trigger can adjust the change to the entire
database
11. Triggers are used for calling stored procedures.

What is Public and Private Procedures in

PL/SQL?
Public Procedures:
Any procedures declared inside the package specification are visible outside the package and can
be accessed in any objects.
Private Procedures:
The procedures that are declared inside the package body, you are restricted to use within that
package.

Therefore, PL/SQL code outside the package cannot reference any of the procedures that were
privately declared within the package.

What is difference between a PROCEDURE

& a FUNCTION ?
Below are the differences between FUNCTION and PROCEDURE

1. Function is mainly used in the case where it must return a value. Where as a procedure
may or may not return a value or may return more than one value using the OUT
parameter.
2. Function can be called from SQL statements where as procedure can not be called from
the sql statements
3. Functions are normally used for computations where as procedures are normally used for
executing business logic.
4. You can have DML (insert,update, delete) statements in a function. But, you cannot call
such a function in a SQL query.
5. Function returns 1 value only. Procedure can return multiple values (max 1024).
6. Stored Procedure: supports deferred name resolution. Example while writing a stored
procedure that uses table named tabl1 and tabl2 etc..but actually not exists in database is
allowed only in during creation but runtime throws error Function wont support deferred
name resolution.
7. Stored procedure returns always integer value by default zero. where as function return
type could be scalar or table or table values
8. Stored procedure is pre-compiled execution plan where as functions are not.
9. A procedure may modify an object where a function can only return a value The
RETURN statement immediately completes the execution of a subprogram and returns
control to the caller.
10. We can use DDL in Procedure using Execute Immediate statement while that is not
possible in functions

How to Generate Debug Logging for Custom

Extensions
It would be tiresome for most of us to debug issues especially in extensions when we have no
clue of flow of process execution and also the values of variables in intermittent stages.
Debugging becomes more complex when most of custom logic is in huge pl/sql scripts.
Today in this article I will show how to implement debugging feature which I regularly use in
the custom extensions I develop.

To generate debugging mechanism we need

1. A table to store log information

2. A sequence to generate unique ID value to each record in the table
3. A trigger to assign unique value to ID column
4. A PL/SQL procedure with a logic to insert debug messages to debug table
5. A PL/SQL statement to call the PL/SQL Debug procedure

Create Table to store Debug Messages

CREATE TABLE XX_DEBUG_LOG
(
id NUMBER,
component_name VARCHAR2(220),
log_text VARCHAR2(220),
creation_date VARCHAR2(220) DEFAULT TO_CHAR(SYSDATE,'DD-MON-YYYY
HH:MM:SS')
);

creation_date column has default value as sysdate which acts like a time stamp.

Create a Sequence to generate unique ID

CREATE SEQUENCE XX_DEBUG_LOG_SEQ
START WITH 1
INCREMENT BY 1
NOCYCLE
NOCACHE;

Create a Trigger to assign unique value to ID column

CREATE OR REPLACE TRIGGER XX_DEBUG_LOG_t1 BEFORE
INSERT ON XX_DEBUG_LOG FOR EACH ROW BEGIN IF :NEW.id IS NULL THEN
SELECT XX_DEBUG_LOG_seq.nextval INTO :NEW.id FROM dual;
END IF;
END;
/

Create a Procedure to insert Debug Message

CREATE OR REPLACE
PROCEDURE XX_INSERT_LOG_P(
p_component_name VARCHAR2 ,
p_log_text VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT
INTO XX_DEBUG_LOG
(
component_name,
log_text
 )
VALUES
(
p_component_name,
p_log_text
);
COMMIT;
END;
/

This procedure is run as an PRAGMA AUTONOMOUS_TRANSACTION so that it will run

independently of current session and the COMMIT statement in this procedure would not impact
the parent session which calls this procedure. Since this is an AUTONOMOUS transaction
debug messages will be captured even if there is any ROLLBACK statement in the parent
session.

Create PL/SQL statement to call the PL/SQL Debug procedure

XX_INSERT_LOG_P('comp_name','log message');

Example
DECLARE
CURSOR EMP_CUR
IS
SELECT * FROM EMP;
--
c_comp_name VARCHAR2(20) := 'LIST EMP';
--
BEGIN
--
XX_INSERT_LOG_P(c_comp_name,'in begin block');
--
FOR I IN EMP_CUR
LOOP
BEGIN
--
XX_INSERT_LOG_P(c_comp_name,'in loop '||i.ename);
dbms_output.put_line(i.ename);
--
EXCEPTION
WHEN OTHERS THEN
XX_INSERT_LOG_P(C_COMP_NAME,'in Exception - '||I.ENAME||' Err -
'||SQLERRM);
END;
END LOOP;
END;

Content in Debug Log table:

In this was you can use this debugging mechanism in extensions like workflows, forms/OA
pages, Forms Personalizations, Business Event Subscriptions, what not.

Hope you liked this article; do leave a comment if you have questions.

What is BULK COLLECT? How and why do

we need to use it?
Before understanding about BULK COLLECT, let’s see how a PL/SQL code is executed. Oracle
uses two engines to process PL/SQL code. All procedural code is handled by the PL/SQL engine
while all SQL is handled by the SQL engine. Whenever there is a need to process an SQL
statement, a context switch happens between PL/SQL and SQL engines.
Imagine a cursor with a SELECT statement which retrieves 1000 rows, in such scenario a
context switch will happen for 1000 times which consumes lot of CPU resources and leads to a
performance issue. BULK COLLECT is one of the way to solve this problem.

BULK COLLECT is one of the way of fetching bulk collection of data. With Oracle bulk collect,
the PL/SQL engine tells the SQL engine to collect many rows at once and place them into a
collection. During an Oracle bulk collect, the SQL engine retrieves all the rows and loads them
into the collection and switches back to the PL/SQL engine. When rows are retrieved using
Oracle bulk collect, context switch happens only once. The larger the number of rows you
would like to collect with Oracle bulk collect, the more performance improvement you will
see using an Oracle bulk collect.

Example

In this example, lets use BULK COLLECT to fetch information of all the applications present in
an EBS instance.

1 DECLARE
2 TYPE xx_rec IS RECORD (
3 col1 fnd_application.APPLICATION_ID%TYPE,
4 col2 fnd_application.APPLICATION_SHORT_NAME%TYPE,
5 col3 fnd_application.PRODUCT_CODE%TYPE,
6 col4 fnd_application.BASEPATH%TYPE
7 ); --Record Type
8
9 TYPE xx_tab IS TABLE OF xx_rec
10 INDEX BY BINARY_INTEGER; --Table Type
11
12 v_tab xx_tab; --This is a type of Collection
13
14 --
15 --Cursor to list all applications in EBS instance
16 --
17 CURSOR cur
18 IS
19 SELECT application_id, application_short_name, product_code, basepath
20 FROM fnd_application;
21 BEGIN
22
23 OPEN cur;
24
25 FETCH cur
26 BULK COLLECT INTO v_tab; --BULK COLLECT usage
27
28 CLOSE cur;
29
30 FOR l_index IN v_tab.FIRST .. v_tab.COUNT
31 LOOP
32 DBMS_OUTPUT.put_line ( v_tab (l_index).col1
33 || ' '
34 || v_tab (l_index).col2
35 || ' '
36 || v_tab (l_index).col3
37 || ' '
38 || v_tab (l_index).col4
39 );
40 END LOOP;
41 END;

Note: Remember that collections are held in memory, so doing a bulk collect from a large query
could occupy most of your memory considerably leading to performance problem. Instead you
can limit the rows returned using the LIMIT clause and move through the data processing
smaller chunks. Below is an example of usage of LIMIT clause

OPEN cur;

FETCH cur
BULK COLLECT INTO v_tab LIMIT 100; --limiting BULK COLLECT to 100 records
each per a context switch

CLOSE cur;

More in detail of how memory consumption happens when Collections are used:
Memory for collections is stored in the program global area (PGA), not the system global area
(SGA). SGA memory is shared by all sessions connected to Oracle Database, but PGA memory
is allocated for each session. Thus, if a program requires 5MB of memory to populate a
collection and there are 100 simultaneous connections, that program causes the consumption of
500MB of PGA memory, in addition to the memory allocated to the SGA.