Oracle PL/SQL

 

1. Cursors
2. Cursor Types
3. Pseudo Structure of a Cursor
4. Cursor Components
5. Cursor Attributes
6. Cursor Loops
7. Cursor FOR loop using sub query
8. Cursors with FOR UPDATE clause
9. Cursors with WHERE CURRENT OF clause
10. Cursor Variables
11. Context Switching
12. Bulk-Binds and Bulk Collect
13. LIMIT clause
14. FORALL
15. PL/SQL block to create user and assign responsibility to user
16. Dynamic SQL
17. PL/SQL block to add Concurrent Program to a Request Group
18. fnd_request.submit_request
19. Difference between View and Materialized View
20. PL/SQL Control structures
21. Operators in PL/SQL
22. Anonymous Block (API Call in Toad)
23. Data Types
24. Anchor Data Type
25. Record Data Types
i) %ROWTYPE
ii) PLSQL Record Type

26. Collections
  i) PLSQL Table Type or Index By Tables or Associative Arrays
  ii) Nested Tables
  iii) VARRAYS(Variable size Arrays)
27. Temporal data types in Oracle

28. Object Tables
29. API for Party Creation
30. REF CURSOR
31. SYS_REFCURSOR
32. Aggregate (or) Group Functions
33. Analytic Functions and Partition By Clause
34. Difference between Procedure and Function
35. TRIGGERS
36. EXCEPTIONS
37. Pragma
38. PRAGMA SERIALLY_REUSABLE
39. PRAGMA EXCEPTION_INIT
40. PRAGMA INLINE
41. RAISE_APPLICATION_ERROR
42. Convert Columns in Rows
43. Procedure for comparing two rows in a table
44. Procedure to Insert a new record on top of picking latest/ max(record_id)
45. API to Create, Update and Delete Approved Supplier List (ASL)
46. Display VARRAY in dbms_output.put_line:
47. Regular Expressions
48. PLSQL Wrapper
49. Package to send output of Standard Concurrent program via Email
50. API to fetch lookup meaning
51. ROLLBACK


1. Cursors
SQL statements get processed in a context area and the pointer to this Context area is called ‘Cursor’.

Every SQL query contains an active set. In short, the information returned by query is an active set.
–> SELECT   * FROM emp; —> An active set
–> SELECT empno, ename, job, sal
FROM emp —> An active set
WHERE deptno = 20;

Cursor is an object which always contains SELECT statement to manipulate data. The structure of VIEW and structure of CURSOR are similar. Cursor fetches one record at a time.


2. Cursor Types


3. Pseudo Structure of a Cursor

DECLARE
 variable_name1 tablename.attributename%TYPE;
 variable_name2 tablename.attributename%TYPE;
 CURSOR cursor_name IS
 SELECT query;
 BEGIN
 OPEN cursor_name;
 FETCH cursor_name INTO variable_name1, variable_name2;
 CLOSE cursor_name;
END;

4. Cursor Components

Cursor declaration A cursor is declared in the declarative section of PL/SQL block, associated with a name and always contains SELECT statement.
Syntax: CURSOR cursor_name {parameter list}{RETURN return type} IS SELECT query
[FOR UPDATE [OF (ColumnList)][NOWAIT]];
— The parameter list and return clause are optional.
— When the cursor is opened the FOR UPDATE clause locks the records and maintains the status of cursor as READ only.
— NOWAIT is specified from preventing the program to terminate when the exclusive lock is not obtained.
Syntax:
DECLARE
CURSOR SampleCursor (V_Temp NUMBER)
IS SELECT ENAME, JOB FROM EMP WHERE DeptNo = V_Temp;
Opening a Cursor After opening a cursor, PGA enables. Cursor opens only in the execution or exception part of PL/SQL block. Cursor can be opened only once in PL/SQL module and only opened cursors can be processed.
Syntax: OPEN CursorName[(parameter values)];
Illustration: OPEN SampleCursor
Fetching a Cursor We can’t use data in Cursor directly. Transfer data to local variables from there we need to fetch data and this fetches 1 record at a time.
Syntax: FETCH CursorName INTO VariableName(s) or PL/SQL RECORD;
Illustration: FETCH SampleCursor INTO V_Temp;
Closing a Cursor The explicit cursor has to be closed to eradicate memory leak and until the cursor is closed, the memory wont gets released.
Syntax: CLOSE cursor Name
Illustration: CLOSE SampleCursor


Cursor Illustration: To demonstrate cursor fetches the first record from EMP table and gets processed.


5. Cursor Attributes


6. Cursor Loops
Use SIMPLE loop when we don’t know how many times the loop should execute and to run the loop body at-least once.
Use WHILE loop when we don’t know how many times the loop should execute, to conditionally terminate the loop and do not have to execute the loop body even one time.
Use FOR loop when we want to execute the body of loop for a fixed number of times and do not want to halt the looping prematurely. The body of the loop executes once for every integer value between the lower_limit_values (FIRST) and upper_limit_values (LAST).





Simple Loop

DECLARE
 CURSOR cursor_name IS SELECT * FROM dba_objects WHERE ROWNUM < 20;
 TYPE l_typ_name IS TABLE OF cursor_name%rowtype;
 l_tbl_typ l_typ_name := l_typ_name ();
BEGIN
 OPEN cursor_name;
 LOOP
 FETCH cursor_name BULK COLLECT INTO l_tbl_typ LIMIT 10;
 EXIT WHEN l_tbl_typ.count = 0;
 dbms_output.put_line(cursor_name%rowcount);
 END LOOP;
 CLOSE cursor_name;
END; 

SELECT query returns 19 records, due to LIMIT of 10 the LOOP iterates 2 times with output 10 and 9. In case we do not specify LIMIT clause then all records will be fetched and we see output as 19. The default value of LIMIT is 100. 

Without Loop

DECLARE
 CURSOR cursor_name IS SELECT * FROM dba_objects WHERE ROWNUM < 20;
 TYPE l_typ_name IS TABLE OF cursor_name%rowtype;
 l_tbl_typ l_typ_name := l_typ_name ();
BEGIN
 OPEN cursor_name;
 --LOOP
 FETCH cursor_name BULK COLLECT INTO l_tbl_typ LIMIT 10;
 --EXIT WHEN l_tbl_typ.count = 0;
 dbms_output.put_line(cursor_name%rowcount);
 --END LOOP;
 CLOSE cursor_name;
END;

Since no LOOP, only 10 records will be fetched due to LIMIT 10. In case we remove the LIMIT then we see output as 19.

7. Cursor FOR loop using sub query
When a sub query is incorporated into a PL/SQL block then a CURSOR declaration is not required. The sub query is embedded into FOR loop. This methodology improves the performance of the CURSOR.


8. Cursors with FOR UPDATE clause
The FOR UPDATE clause is used to lock the affected rows during data updation and data deletions. This is mandatory in real time data updations and data deletions in client server architecture. The FOR UPDATE clause is the last clause in the SELECT statement. If NOWAIT clause is not specified the Oracle server needs to wait till locks on rows available.

Syntax:
CURSOR curname IS
SELECT…FROM…FOR UPDATE [OF ColumnReference][NOWAIT];

DECLARE
 CURSOR curname
 IS
 SELECT ENAME, EMPNO, SAL
 FROM EMP
 WHERE DEPTNO = 10
 FOR UPDATE OF Sal NOWAIT;
 BEGIN
 FOR curvar IN curname
 LOOP
 UPDATE emp
 SET sal = (curvar.sal * 2);
 END LOOP;
 END;

9. Cursors with WHERE CURRENT OF clause
The WHERE CURRENT OF clause is used for referencing the CURRENT ROW from an FOR UPDATE cursor. This is applied for updation and deletion without the need to explicitly reference the ROWID.
Syntax:
[UPDATE OR DELETE statement] WHERE CURRENT OF CursorName;

DECLARE
 CURSOR curname
 IS
 SELECT sal FROM emp
 WHERE deptno = 10
 FOR UPDATE OF sal NOWAIT;
 BEGIN
 FOR curvar IN curname
 LOOP
 UPDATE emp
 SET sal = (curvar.sal * 10)
 WHERE CURRENT OF curname;
 END LOOP;
 END;

10. Cursor Variables

REF cursor is a data type in Oracle PL/SQL. It represents a cursor or a result set in Oracle DB.


11. Context Switching
PL/SQL code consists of procedural statements as well as sql statements. Oracle uses two different engines to process the PL/SQL code. All the procedural code executes in PL/SQL engine and SQL code executes in SQL engine. While executing a PL/SQL block, when the PL/SQL engine finds a SQL statement then the PL/SQL engine stops and passes control to SQL engine for process and wait till it listens from SQL engine. This transfer control process is called Context Switch. The context switching should be minimal to improve the performance.


12. Bulk-Binds and Bulk Collect
We use bulk collect to reduce the context switching. Here the PL/SQL engine tells the SQL engine to collect maximum rows at once and place them in collection. SQL engine process the maximum rows and loads them into collection and switches back to PL/SQL engine. The BULK COLLECT retrieve multiple rows of data with a single roundtrip. This reduces the number of I/O cycles between SQL & PL/SQL engine.
We use BULK COLLECT when we have to fetch large volumes of data.
• Advantage — Programs run faster
• Disadvantage — Consumes too much memory
The trade off* with BULK COLLECT implementation is, “Run faster but consume more memory”
Trade off* = a situation in which you accept something bad in order to have something good
→ Collections (Any scalar, Anchor (%TYPE), Record (%ROWTYPE), Collections (PLSQL TABLE Type) data types) are part of SQL hence memory for collections are stored in PGA (Program global area)  not in SGA (System Global Area)
→ SGA memory will be shared by all sessions connected to Oracle DB whereas PGA memory is allocated for each PLSQL connection taking place. 10 PLSQL sessions = 10 PGA’s. Each DB has one SGA.
→ PGA Memory Allocation: Total Memory Load on particular program = (memory of PGA * no. of PLSQL connections) + Total SGA
Example: Memory required by a particular program (PGA) = 10MB
Number of PLSQL connections = 20
Total amount of PGA memory = 10*20 = 200
Total memory load on instance server = 200 + Total SGA

Collection Types:
Dense collection: Memory is continuously filled with data without any gaps between the records.
Sparse collection: Memory is non-continuous and gaps between the records can be identified.

CREATE TABLE BULK_COLLECT
 AS
 SELECT owner, object_name, object_id, object_type FROM all_objects;
 SELECT COUNT(*) FROM bulk_collect; -- 497203 records exist
DECLARE
 TYPE bct_tbl_typ IS TABLE OF bulk_collect%ROWTYPE;
 lv_bct_tbl_typ bct_tbl_typ:=bct_tbl_typ();
 ln_load_time NUMBER;
 BEGIN
 ln_load_time := DBMS_UTILITY.GET_TIME; --regular load start time
 dbms_output.put_line('Example to fetch all records load_time');
 FOR i IN (SELECT * FROM bulk_collect) -- loading data record by record
 LOOP
 lv_bct_tbl_typ.EXTEND;
 lv_bct_tbl_typ(lv_bct_tbl_typ.LAST) := i;
 END LOOP;
 dbms_output.put_line('Starting index is: '||lv_bct_tbl_typ.first);
 dbms_output.put_line('Ending index is: '||lv_bct_tbl_typ.last);
 dbms_output.put_line('Total number of records fetched: '||lv_bct_tbl_typ.count);
 dbms_output.put_line('Total time taken to fetch '||lv_bct_tbl_typ.count||' records is '||(DBMS_UTILITY.GET_TIME - ln_load_time)||' milli seconds');
 END;
/*-------------------dbms_output------------------------------*/
 Example to fetch all records load_time
 Starting index is: 1
 Ending index is: 497203
 Total number of records fetched: 497203
 Total time taken to fetch 497203 records is 294 milli seconds

Again insert another set of records into table.

INSERT INTO BULK_COLLECT (SELECT owner, object_name, object_id, object_type FROM all_objects); --497203 rows inserted.
COMMIT;
SELECT COUNT(*) FROM BULK_COLLECT; -- 994406

Execute the above block again and see the output as below:

/*-------------------dbms_output------------------------------*/ 
 Example to fetch all records load_time
 Starting index is: 1
 Ending index is: 994406
 Total number of records fetched: 994406
 Total time taken to fetch 994406 records is 464 milli seconds

Using BULK COLLECT:

DECLARE
 TYPE emptabletype IS TABLE OF emp%ROWTYPE;
 v_emptabletype emptabletype := emptabletype();
 BEGIN
 SELECT * BULK COLLECT INTO v_emptabletype FROM emp ORDER BY sal DESC;
 FOR curvar IN v_emptabletype.FIRST .. v_emptabletype.LAST
 LOOP
 DBMS_OUTPUT.put_line (v_emptabletype (curvar).ename|| ' salary is '|| v_emptabletype (curvar).sal);
 END LOOP;
 END;
DECLARE
 TYPE bct_tbl_typ IS TABLE OF bulk_collect%ROWTYPE;
 lv_bct_tbl_typ bct_tbl_typ:=bct_tbl_typ();
 ln_load_time NUMBER;
 BEGIN
 ln_load_time := DBMS_UTILITY.GET_TIME; --regular load start time
 dbms_output.put_line('Example to fetch all records load_time');
 SELECT * BULK COLLECT INTO lv_bct_tbl_typ FROM bulk_collect; -- collects all records at a time
 dbms_output.put_line('Total time taken to fetch all records is '||(DBMS_UTILITY.GET_TIME - ln_load_time)||' milli seconds');
 END;

/*-------------------dbms_output------------------------------*/
 Example to fetch all records load_time
 Total time taken to fetch all records is 392 milli seconds

13. LIMIT clause
We use LIMIT clause to limit the number of rows to be fetched during BULK COLLECT. The default LIMIT is 100 but there is no standard value for this. Try testing with different values and pick best optimized value.
FETCH cursor_name BULK COLLECT INTO collection_name LIMIT number;

Example 1:

DECLARE
CURSOR cur_bulk_collect IS SELECT * FROM bulk_collect;
TYPE bct_tbl_typ IS TABLE OF cur_bulk_collect%ROWTYPE;
lv_bct_tbl_typ bct_tbl_typ:=bct_tbl_typ();
ln_start_time NUMBER;
ln_end_time NUMBER;
BEGIN
ln_start_time := DBMS_UTILITY.GET_TIME; –regular load start time
dbms_output.put_line(‘Load start time ‘||ln_start_time);
OPEN cur_bulk_collect;
LOOP
FETCH cur_bulk_collect BULK COLLECT INTO lv_bct_tbl_typ LIMIT 100000;
EXIT WHEN lv_bct_tbl_typ.COUNT = 0;
dbms_output.put_line(‘Total number of rows processed in this load are ‘||lv_bct_tbl_typ.COUNT);
END LOOP;
ln_end_time := DBMS_UTILITY.GET_TIME; –regular load end time
dbms_output.put_line(‘Load end time ‘||ln_end_time);
dbms_output.put_line(‘Total time taken for entire process is ‘||(ln_end_time – ln_start_time)|| ‘ milli seconds’);
END;

/*-------------------dbms_output------------------------------*/
Load start time -1917528354
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 100000
Total number of rows processed in this load are 94406
Load end time -1917527946
Total time taken for entire process is 408 milli seconds

Example 2: For testing purpose I kept only 1001 records in bulk_collect table

DECLARE
CURSOR cur_bulk_collect IS SELECT * FROM bulk_collect;
TYPE bct_tbl_typ IS TABLE OF cur_bulk_collect%ROWTYPE;
lv_bct_tbl_typ bct_tbl_typ:=bct_tbl_typ();
ln_start_time NUMBER;
ln_end_time NUMBER;
BEGIN
dbms_output.put_line(‘——————————————-‘);
ln_start_time := DBMS_UTILITY.GET_TIME; –regular load start time
dbms_output.put_line(‘Load start time without BULK COLLECT ‘||ln_start_time);
FOR i IN cur_bulk_collect — Each record gets picked due to no BULK COLLECT
LOOP
lv_bct_tbl_typ.EXTEND;
lv_bct_tbl_typ(lv_bct_tbl_typ.LAST) := i;
dbms_output.put_line(‘Number of row processed without BULK COLLECT are ‘||lv_bct_tbl_typ.COUNT);
END LOOP;
ln_end_time := DBMS_UTILITY.GET_TIME; –regular load end time
dbms_output.put_line(‘Load end time without BULK COLLECT ‘||ln_end_time);
dbms_output.put_line(‘Total time taken for entire process without BULK COLLECT ‘||(ln_end_time – ln_start_time)|| ‘ milli seconds’);
dbms_output.put_line(‘——————————————-‘);
ln_start_time := DBMS_UTILITY.GET_TIME; –regular load start time
dbms_output.put_line(‘Load start time with BULK COLLECT of 100 ‘||ln_start_time);
OPEN cur_bulk_collect;
LOOP
FETCH cur_bulk_collect BULK COLLECT INTO lv_bct_tbl_typ LIMIT 100;
EXIT WHEN lv_bct_tbl_typ.COUNT = 0;
dbms_output.put_line(‘Total number of rows processed with BULK COLLECT of 100 are ‘||lv_bct_tbl_typ.COUNT);
END LOOP;
ln_end_time := DBMS_UTILITY.GET_TIME; –regular load end time
dbms_output.put_line(‘Load end time with BULK COLLECT of 100 ‘||ln_end_time);
dbms_output.put_line(‘Total time taken for entire process with BULK COLLECT of 100 ‘||(ln_end_time – ln_start_time)|| ‘ milli seconds’);
CLOSE cur_bulk_collect;
dbms_output.put_line(‘——————————————-‘);
ln_start_time := DBMS_UTILITY.GET_TIME; –regular load start time
dbms_output.put_line(‘Load start time with BULK COLLECT of 200 ‘||ln_start_time);
OPEN cur_bulk_collect;
LOOP
FETCH cur_bulk_collect BULK COLLECT INTO lv_bct_tbl_typ LIMIT 200;
EXIT WHEN lv_bct_tbl_typ.COUNT = 0;
dbms_output.put_line(‘Total number of rows processed with BULK COLLECT of 200 are ‘||lv_bct_tbl_typ.COUNT);
END LOOP;
ln_end_time := DBMS_UTILITY.GET_TIME; –regular load end time
dbms_output.put_line(‘Load end time with BULK COLLECT of 200 ‘||ln_end_time);
dbms_output.put_line(‘Total time taken for entire process is with BULK COLLECT of 200 ‘||(ln_end_time – ln_start_time)|| ‘ milli seconds’);
CLOSE cur_bulk_collect;
dbms_output.put_line(‘——————————————-‘);
ln_start_time := DBMS_UTILITY.GET_TIME; –regular load start time
dbms_output.put_line(‘Load start time with BULK COLLECT of 500’||ln_start_time);
OPEN cur_bulk_collect;
LOOP
FETCH cur_bulk_collect BULK COLLECT INTO lv_bct_tbl_typ LIMIT 500;
EXIT WHEN lv_bct_tbl_typ.COUNT = 0;
dbms_output.put_line(‘Total number of rows processed with BULK COLLECT of 500 are ‘||lv_bct_tbl_typ.COUNT);
END LOOP;
ln_end_time := DBMS_UTILITY.GET_TIME; –regular load end time
dbms_output.put_line(‘Load end time with BULK COLLECT of 500 ‘||ln_end_time);
dbms_output.put_line(‘Total time taken for entire process with BULK COLLECT of 500 ‘||(ln_end_time – ln_start_time)|| ‘ milli seconds’);
CLOSE cur_bulk_collect;
END;

/*-------------------dbms_output------------------------------*/
Load start time without BULK COLLECT -1908939113
Number of row processed without BULK COLLECT are 1
Number of row processed without BULK COLLECT are 2
…………………………………………………………………………………………
…………………………………………………………………………………………
Number of row processed without BULK COLLECT are 1000
Number of row processed without BULK COLLECT are 1001
Load end time without BULK COLLECT -1908938864
Total time taken for entire process without BULK COLLECT 249 milli seconds
——————————————-
Load start time with BULK COLLECT of 100 -1908938864
Total number of rows processed with BULK COLLECT of 100 are 100
Total number of rows processed with BULK COLLECT of 100 are 100
……………………………………………………………………………………………………..
……………………………………………………………………………………………………..
Total number of rows processed with BULK COLLECT of 100 are 100
Total number of rows processed with BULK COLLECT of 100 are 1
Load end time with BULK COLLECT of 100 -1908938863
Total time taken for entire process with BULK COLLECT of 100 1 milli seconds
——————————————-
Load start time with BULK COLLECT of 200 -1908938863
Total number of rows processed with BULK COLLECT of 200 are 200
Total number of rows processed with BULK COLLECT of 200 are 200
Total number of rows processed with BULK COLLECT of 200 are 200
Total number of rows processed with BULK COLLECT of 200 are 200
Total number of rows processed with BULK COLLECT of 200 are 200
Total number of rows processed with BULK COLLECT of 200 are 1
Load end time with BULK COLLECT of 200 -1908938862
Total time taken for entire process is with BULK COLLECT of 200 1 milli seconds
——————————————-
Load start time with BULK COLLECT of 500-1908938862
Total number of rows processed with BULK COLLECT of 500 are 500
Total number of rows processed with BULK COLLECT of 500 are 500
Total number of rows processed with BULK COLLECT of 500 are 1
Load end time with BULK COLLECT of 500 -1908938860
Total time taken for entire process with BULK COLLECT of 500 2 milli seconds
From above results it is clear that BULK COLLECT with 100 or 200 having optimized results.

14. FORALL
Used with DML (INSERT/ UPDATE/DELETE) statements to improve performance. The PL/SQL engine sends DML statements to SQL engine in batches rather than one at a time which leads to less context switch and improves performance.

Instead of using below
FOR i IN 1..table_type.COUNT
Use
FORALL i IN 1..table_type.COUNT

—> %BULK_EXCEPTIONS: Used for the FORALL statement. Provides info regarding exception
—> %BULK_ROWCOUNT: Used for the FORALL statement. Provides info regarding the number of rows changed.


15. PL/SQL block to create user and assign responsibility to user

CLEAR SCREEN;
SET SERVEROUTPUT ON;
DECLARE
 lc_user_name VARCHAR2(100) := 'SRIKANTH.G';
 lc_user_password VARCHAR2(100) := 'Oracle123';
 ld_user_start_date DATE := SYSDATE;
 ld_user_end_date VARCHAR2(100) := NULL;
 ld_password_date VARCHAR2(100) := SYSDATE;
 ld_password_lifespan_days NUMBER := NULL;
 ln_person_id NUMBER := NULL;
 lc_email_address VARCHAR2(100) := NULL;
 lv_resp_appl_short_name VARCHAR2(100);
 lv_responsibility_key VARCHAR2(100) := 'SYSTEM_ADMINISTRATOR';
 lv_security_group_key VARCHAR2(100) := 'STANDARD'; -- SELECT * FROM fnd_security_groups
 ld_resp_start_date DATE := SYSDATE;
 ld_resp_end_date DATE := NULL;
BEGIN
/*User Creation*/
 BEGIN
 fnd_user_pkg.createuser(
 x_user_name => lc_user_name
 ,x_owner => NULL
 ,x_unencrypted_password => lc_user_password
 ,x_start_date => ld_user_start_date
 ,x_end_date => ld_user_end_date
 ,x_password_date => ld_password_date
 ,x_password_lifespan_days => ld_password_lifespan_days
 ,x_employee_id => ln_person_id
 ,x_email_address => lc_email_address
 );
 dbms_output.put_line('User created successfully - ' || lc_user_name);
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error in user creation - ' || sqlerrm);
 RAISE;
 END;
/*System Administrator Responsibility Assignment*/
 BEGIN
 SELECT application_short_name INTO
 lv_resp_appl_short_name
 FROM fnd_application
 WHERE application_id = ( SELECT application_id FROM fnd_application_tl WHERE application_name = 'System Administration'
 );
 SELECT responsibility_key INTO
 lv_responsibility_key
 FROM fnd_responsibility
 WHERE responsibility_id = ( SELECT responsibility_id FROM fnd_responsibility_tl WHERE responsibility_name = 'System Administrator'
 );
 fnd_user_pkg.addresp(
 username => lc_user_name
 ,resp_app => lv_resp_appl_short_name
 ,resp_key => lv_responsibility_key
 ,security_group => lv_security_group_key
 ,description => NULL
 ,start_date => ld_resp_start_date
 ,end_date => ld_resp_end_date
 );
 dbms_output.put_line('Responsibility assigned to user successfully - ' || lv_resp_appl_short_name);
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error in responsibility assignment - ' || sqlerrm);
 RAISE;
 END;
 COMMIT;
EXCEPTION
 WHEN OTHERS THEN
 ROLLBACK;
 dbms_output.put_line('User not created..!!');
END;

Password change

CLEAR SCREEN;
SET SERVEROUTPUT ON;
DECLARE
 lc_user_name VARCHAR2(100) := 'SRIKANTH.G';
 lc_user_password VARCHAR2(100) := 'Oracle123';
 lv_status BOOLEAN := NULL;
BEGIN
 lv_status := fnd_user_pkg.changepassword(
 lc_user_name
 ,lc_user_password
 );
 dbms_output.put_line('Password changed successfully of - ' || lc_user_name);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error in Password change - ' || sqlerrm);
 RAISE;
END;

 


16. Dynamic SQL
Static SQL statements compiles at compilation time and executes at run time. (Compilation = Checks Syntax).
Dynamic SQL statements compiles and executes at run time. We go with dynamic sql when we dont have information about column names/ table names, like we know that we have to use either CREATE or ALTER or DROP or TRUNCATE but do not know on which table or on which column we need to perform this activity..  We use late binding for dynamic sql and early binding for static sql.
Executes dynamic sql statements in PL/SQL block. We use DDL commands in dynamic sql.

Two methods of Dynamic SQL:
1. Native Dynamic SQL (EXECUTE IMMEDIATE)

EXECUTE IMMEDIATE 'TRUNCATE TABLE table_name';
EXECUTE IMMEDIATE 'CREATE TABLE table_name (column1 NUMBER, column2 VARCHAR2(100))';
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';
CLEAR SCREEN
SET SERVEROUTPUT ON
DECLARE
lv_sql VARCHAR2 (1000);
lv_local emp%ROWTYPE;
BEGIN
lv_sql:= 'SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE EMPNO = :p_empno';
lv_local.empno := 7698; --7698 is EMPNO
EXECUTE IMMEDIATE lv_sql INTO Lv_Local.ename, Lv_Local.job, Lv_Local.sal, Lv_Local.deptno USING Lv_Local.empno;
DBMS_OUTPUT.PUT_LINE (lv_local.ename|| ' '|| lv_local.job|| ' '|| lv_local.sal|| ' '|| lv_local.deptno);
END;
CLEAR SCREEN
DECLARE
lv_sql VARCHAR2 (1000);
lv_local emp%ROWTYPE;
lv_empno emp.empno%TYPE := &empno;
BEGIN
lv_sql:= 'SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE EMPNO = :p_empno';
EXECUTE IMMEDIATE lv_sql INTO Lv_Local.ename, Lv_Local.job, Lv_Local.sal, Lv_Local.deptno USING lv_empno;
DBMS_OUTPUT.PUT_LINE (lv_local.ename|| ' '|| lv_local.job|| ' '|| lv_local.sal|| ' '|| lv_local.deptno);
END;

Passing values during run time:
CREATE TABLE emp_table AS SELECT * FROM emp ;
CREATE OR REPLACE PROCEDURE exec_imm (p_tab IN VARCHAR)
AS
BEGIN
EXECUTE IMMEDIATE (‘DROP TABLE’ || p_tab);
END;
EXEC  exec_imm(emp_table);

2. DBMS_SQL package

CLEAR SCREEN
SET SERVEROUTPUT ON
DECLARE
lv_sql VARCHAR2 (2001) := 'SELECT ENAME, JOB, SAL, DEPTNO FROM EMP WHERE EMPNO = :p_empno';
ln_open NUMBER;
lv_local emp%ROWTYPE;
ln_rows NUMBER;
BEGIN
ln_open := DBMS_SQL.open_cursor;
DBMS_SQL.parse (ln_open, lv_sql, DBMS_SQL.native);
DBMS_SQL.define_column (ln_open,1,lv_local.ename,10);
DBMS_SQL.define_column (ln_open,2,lv_local.job,10);
DBMS_SQL.define_column (ln_open, 3, lv_local.sal);
DBMS_SQL.define_column (ln_open, 4, lv_local.deptno);
DBMS_SQL.bind_variable (ln_open, 'p_empno', 7698); -- 7698 is EMPNO
ln_rows := DBMS_SQL.execute (ln_open);
LOOP
IF DBMS_SQL.FETCH_ROWS (ln_open) > 0
THEN
DBMS_SQL.COLUMN_VALUE (ln_open, 1, lv_local.ename);
DBMS_SQL.COLUMN_VALUE (ln_open, 2, lv_local.job);
DBMS_SQL.COLUMN_VALUE (ln_open, 3, lv_local.sal);
DBMS_SQL.COLUMN_VALUE (ln_open, 4, lv_local.deptno);
ELSE
EXIT;
END IF;
END LOOP;
DBMS_SQL.close_cursor (ln_open);
DBMS_OUTPUT.PUT_LINE (lv_local.ename|| ' '|| lv_local.job|| ' '|| lv_local.sal|| ' '|| lv_local.deptno);
END;

17. PL/SQL block to add Concurrent Program to a Request Group

DECLARE
 lv_program_short_name VARCHAR2(200) := NULL;
 lv_program_application VARCHAR2(200) := NULL;
 lv_request_group VARCHAR2(200) := NULL;
 lv_group_application VARCHAR2(200) := NULL;
 lv_check VARCHAR2(2) := NULL;
BEGIN
 lv_program_short_name := 'Program Short Name';
 lv_program_application := 'Application Short Name';
 lv_request_group := 'Request Group Name';
 lv_group_application := 'Group Application';
 fnd_program.add_to_group(
 program_short_name => lv_program_short_name
 ,program_application => lv_program_application
 ,request_group => lv_request_group
 ,group_application => lv_group_application
 );
 COMMIT;
EXCEPTION
 WHEN no_data_found THEN
 dbms_output.put_line('Adding Concurrent Program to Request Group Failed');
END;

18. fnd_request.submit_request — Submit a concurrent program from back end.

--First we need to initialize the following parameters:
--User_id
--Responsibility_id
--Responsibility_application_id
--Use below query to attain the values:
SELECT user_id
 ,responsibility_id
 ,responsibility_application_id
FROM fnd_user_resp_groups
WHERE user_id = ( SELECT user_id FROM fnd_user WHERE user_name = '&user_name'
)
 AND responsibility_id = ( SELECT responsibility_id FROM fnd_responsibility_tl WHERE responsibility_name = '&RESPONSIBILITY_NAME'
);

DECLARE
 lv_request_id VARCHAR2(100);
BEGIN
 fnd_global.apps_initilaize(
 user_id
 ,resp_id
 ,resp_appl_id
 );
 lv_request_id := fnd_request.submit_request(
 application => 'Application_Name'
 ,program => 'Program Short Name'
 ,description => NULL --Optional; Concurrent program description
 ,start_time => SYSDATE --Optional; Start time of Concurrent program
 ,sub_request => false
 ,argument1 => 1
 ,argument2 => 2
 ,argument3 => 3
 ,argumentn => n
 );
END;

19. Difference between View and Materialized View
View:
1) View is considered as a Logical table and it does not contain any data physically but behaves as if it contains data.
2) View always contains a query hence we can consider that a view is a schema object that stores the SELECT statement permanently in the DB.
3) Because of View we can reuse SELECT statement  multiple times within the application development increasing productivity and reducing the production cost and maintenance cost.
4) We cannot create INDEX’s on View since they are not actual tables.
5) Views will create a logical layer providing security for the physical location of the DB objects and the actual structure of the actual DB object
6) Views can be either relational views or object views by creation and implementation.
7) Views can be FORCE or NOFORCE views by creation
8) Views can be READONLY views as well as WITH CHECK OPTION views
9) Even though a view contains only a SELECT statement, we can conduct transactions through views upon the original tables by following certain rules.

Materialized Views:
1) MV also contains a SELECT statement but once created it manages a physical table with replication of the queried data.
2) MV can hold the query and data with REFRESH cycles on the data.
3) We can create index’s on MV
4) MV increases the performance of the data querying process in complicated aggregations, joins and filters
5) MV are mostly used in managing OLAP operations, to keep pre calculated and pre aggregated patterns of the data.
6) They cannot be replaced. They have to be dropped and recreated once again.

MV is a DB object which contains the results of query. When we create a MV, Oracle DB creates an internal table and at least one index. We go with MV when the SELECT query has summaries and multiple joins. The basic feature of MV is REFRESH. Here the data gets refreshed based on ON COMMIT or DEMAND.
SYNTAX:
CREATE MATERIALIZED VIEW <schema.name>
TABLESPACE <tablespace_name>
BUILD IMMEDIATE
REFRESH <FAST | COMPLETE | FORCE > ON <COMMIT | DEMAND>
< USING INDEX | USING NO INDEX>
INITRANS <integer>
STORAGE CLAUSE
AS (<SQL statement>);

Build Methods:
BUILD IMMEDIATE: Create the materialized view and then populate it with data.
BUILD DEFFERED: Create the materialized view definition but do not populate it with data.
ON PREBUILT TABLE: use an existing table as view source

Refresh Types:
FAST: Apply the changes made since the last refresh.
COMPLETE: Truncate the existing data and re-insert new data.
FORCE: First tries to refresh with fast mechanism if possible or else will use a complete refresh. This is default refresh type.
NEVER: Suppresses all refreshes on materialized views.

Refresh Modes:
Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)
Automatic Refresh: Can be performed in two ways:
a) ON COMMIT – MV gets updated whenever changes to one of these tables are committed.
b) ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Index Modes:
USING INDEX – Uses to create values for INITRANS (number of entries) and STORAGE (storage space) parameters. If USING INDEX is not specified, then default values are used for the index
USING NO INDEX Clause
Specify USING NO INDEX to suppress the creation of the default index.


20. PL/SQL Control structures
—> Branching logic: If true what kind of logic/ If false what kind of logic
—> Selection logic: Conditions w.r.t
—> Looping logic: Again & again relative to condition
Here Branching and Selection are Conditional statements.


21. Operators in PL/SQL
Concatenation  ||

Assignment   :=
Arithmetic  +, -, *, /
Relational  =, !=, <, >, <=, >=
Logical  AND, OR, NOT


22. Anonymous Block (API Call in Toad)

DECLARE
--no need to declare i/p parameters
 l_err_buf VARCHAR2(100);
 l_ret_code NUMBER;
BEGIN
 pkg_name.proc_name(
 p_err_buf => l_err_buf
 ,p_ret_code => l_ret_code
 ,parameter_1 =>  pass_some_value
 ,parameter_2 =>  pass_some_value
 ,parameter_3 =>  pass_some_value
 ,parameter_n =>  pass_some_value
 );
END;

23. Data Types

Scalar types are used to store single value with no internal components.
PLS_INTEGER — Used to store signed integers
NUMBER(p, s) — Used to store decimal values but can also be used for integers. P is precision and specifies total number of digits whereas S is scale which specifies the number of digits to the right of decimal point.
CHAR(n) — Fixed length character. n represents the number of bytes
VARCHAR2(n) — Variable length character
DATE — Used to store date and time. It stores Century, Year, Month, Day, Hour, Minute and Second. Fractional seconds are not available in this datatype
TIMESTAMP — Similar to DATE datatype and in addition it provides fractional seconds upto 6 digits
BOOLEAN — Used to store TRUE, FALSE or NULL. Do not enclose in quotes when assigning these values to variables. It is mainly used to store comparison result. We wont be able to display the value stored in BOOLEAN variable.


24. Anchor Data Type
%TYPE
Variable_Name  Table_Name.Column_Name%TYPE;
Single Column — %TYPE;  This is most suitable when the variable in pl/sql program gets mapped directly to a column in DB table. Even when the db column data type changes, the pl/sql program doesn’t alter. We can use := (assignment) operator like Variable_Name  Table_Name.Column_Name%TYPE := SomeValue;
Note: Only the datatype and size of column is referred but not the constraints.

DECLARE
 v_empno emp.empno%TYPE := &empno;
 v_name emp.ename%TYPE;
 v_sal emp.sal%TYPE;
BEGIN
 SELECT ename
 ,sal INTO
 v_name,v_sal
 FROM emp
 WHERE empno = v_empno;
 dbms_output.put_line('The Employee name is ' || initcap(v_name) || ' and his Salary is ' || v_sal);
EXCEPTION
 WHEN too_many_rows THEN
 dbms_output.put_line('Too many rows');
 WHEN no_data_found THEN
 dbms_output.put_line('No such data found');
 WHEN OTHERS THEN
 dbms_output.put_line('Not a valid data');
END;
DECLARE
 v_max_marks NUMBER(3) := 100;
 v_english_marks v_max_marks%TYPE;
 v_science_marks v_max_marks%TYPE;
BEGIN
 v_english_marks   := 85;
 v_science_marks   := 90;
 dbms_output.put_line(v_english_marks);
 dbms_output.put_line(v_science_marks);
END;

25. Record Data Types

The internal components can have different data types and are called Fields. We can access each field of a record variable by its name using syntax variable_name.field_name.

i) %ROWTYPE
Variable_Name  TableName%ROWTYPE;
Collection of columns — %ROWTYPE;  This is most suitable when we retrieve data for the entire row with SELECT statement using * operator. We cannot use := (assignment) operator here, have to use in WHERE clause.

DECLARE
V_EmpNo EMP%ROWTYPE;
V_Sal EMP.SAL%TYPE := &Sal;
BEGIN
SELECT * INTO V_EmpNo FROM EMP WHERE Sal = V_Sal;
DBMS_OUTPUT.PUT_LINE('The Employee name is '||INITCAP(V_Empno.EName)|| ' and his Salary is '||V_EmpNo.Sal);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many rows');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Not a valid data');
END;
DECLARE
V_EmpNo EMP%ROWTYPE;
BEGIN
SELECT * INTO V_EmpNo FROM EMP WHERE Sal = &Sal;
DBMS_OUTPUT.PUT_LINE('The Employee name is '||INITCAP(V_Empno.EName)|| ' and his Salary is '||V_EmpNo.Sal);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE('Too many rows');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such data found');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Not a valid data');
END;
CREATE TABLE emp_rowtype_insert AS SELECT * FROM emp WHERE 1=2;
SELECT * FROM emp_rowtype_insert;
SET SERVEROUTPUT ON
DECLARE
lv_emp emp%ROWTYPE;
BEGIN
SELECT * INTO lv_emp FROM emp WHERE empno = 7839;
INSERT INTO emp_rowtype_insert VALUES lv_emp;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line('Error is '||SQLERRM);
END;
SET SERVEROUTPUT ON
DECLARE
lv_emp emp%ROWTYPE;
BEGIN
SELECT * INTO lv_emp FROM emp WHERE empno = 7839;
lv_emp.sal := 6000;
lv_emp.deptno := 20;
lv_emp.comm := 100;
UPDATE emp_rowtype_insert SET row = lv_emp WHERE empno = 7839;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line('Error is '||SQLERRM);
END;
DECLARE
CURSOR C1 IS SELECT * FROM emp;
lv_emp C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO lv_emp;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('The Employee name is '||INITCAP(lv_emp.EName)|| ' and his Salary is '||lv_emp.Sal);
END LOOP;
DBMS_OUTPUT.PUT_LINE('The total number of records are '||C1%ROWCOUNT);
CLOSE C1;
EXCEPTION
WHEN OTHERS THEN dbms_output.put_line('Error is '||SQLERRM);
END;

ii) PLSQL Record Type
—> Few columns from 1 table; few columns from other table; few columns from another table. i.e.: few columns from one table and few columns from another table will get merge. Has to return only one row.

TYPE record_type_name IS RECORD
(first_col_name column_datatype,
second_col_name column_datatype, …);

Type Name –> Field Name –> Field Type –> Expression

TYPE TypeName IS RECORD
(FieldName1 FieldType, FieldName2 FieldType := [DEFAULT] Expr,…);

First define a Record type and then declare the records of that record type.

DECLARE
v_empno emp.empno%TYPE := :empno;
TYPE emprecordtype IS RECORD (
lv_ename VARCHAR2(30),
ln_deptno NUMBER(2),
lv_job VARCHAR2(30),
lv_loc VARCHAR2(30)
);
emprecord emprecordtype;
BEGIN
SELECT e.ename, e.deptno, e.job, d.loc --should be same number & sequence as defined in record type
INTO emprecord
FROM emp e, dept d
WHERE e.deptno = d.deptno AND e.empno = v_empno;
DBMS_OUTPUT.put_line ('The Employee Name: ' || emprecord.lv_ename);
DBMS_OUTPUT.put_line ('The Employee deptno: ' || emprecord.ln_deptno);
DBMS_OUTPUT.put_line ('The Employee designation: ' || emprecord.lv_job);
DBMS_OUTPUT.put_line ('The Employee location: ' || emprecord.lv_loc);
EXCEPTION
WHEN OTHERS
THEN
dbms_output.put_line('Error '||SQLERRM);
END;

26. Collections

A collection is an order group of logically related elements. The internal components always have same data types and are called Elements. We can access each element of a collection variable by its unique index using syntax variable_name(index).

i) PLSQL Table Type or Index By Tables or Associative Arrays
PL/SQL Table should contain two components:
1. A “Primary Key”  — that indexes the pl/sql table
2. A column of scalar or record data type which stores the pl/sql table elements.
The Primary key must belong to type BINARY_INTEGER only whereas the data column can be any scalar or record data type. These are sets of key-value pairs, where each key is unique and is used to locate a corresponding value in the array. The key can be an integer or string. Associative arrays are intended for temporary storage of data. These are used for fast lookup of an individual element w/o knowing its position within the array. We cannot use INSERT/ SELECT statements.

TYPE type_name IS TABLE OF element_type [NOT NULL] INDEX BY BINARY_INTEGER;
TYPE enametabletype IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

Collection Methods:
1. EXISTS(N) – Returns True if the Nth element in the collection exists
2. COUNT – Returns the total number of elements that a collection contains
3. FIRST and LAST – Returns First and Last Index numbers in collection and returns NULL if a collection is empty
4. PRIOR(N) – Returns the Index number that precedes Index N in a collection
5. NEXT(N) – Returns the Index number that succeeds Index N in a collection
6. EXTEND –
EXTEND(N.I) – It increases the size of collection and appends one null element into collection.
EXTEND(N) – Appends N null element into collection.
EXTEND(N, i) – Appends N copies of ith element into collection.
7. TRIM – Removes one element from the end of collection
TRIM(N) – Removes N elements from the end of collection
8. DELETE – Removes all the elements from the collection
9. LIMIT – Returns the maximum number of elements that a collection contains

DECLARE
TYPE associative_type IS TABLE OF NUMBER INDEX BY VARCHAR2(20);--Here NUMBER refers to data (700, 800) and VARCHAR2 refers to index (India, China)
v_associative_type associative_type;
BEGIN
v_associative_type('India') := 700;
v_associative_type('China') := 800;
v_associative_type('US') := 900;
dbms_output.put_line(v_associative_type('China'));
dbms_output.put_line(v_associative_type('India'));
EXCEPTION
 WHEN OTHERS THEN dbms_output.put_line('Error '|| sqlerrm);
END;
DECLARE
TYPE associative_type IS TABLE OF dept.dname%TYPE INDEX BY BINARY_INTEGER;
my_dept_type associative_type;
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM dept;
FOR i IN 1..v_count
LOOP
SELECT dname INTO my_dept_type(i) FROM dept
WHERE deptno = i*10;
dbms_output.put_line(my_dept_type(i));
END LOOP;
EXCEPTION
 WHEN OTHERS THEN dbms_output.put_line('Error '|| sqlerrm);
END;
DECLARE
  CURSOR cur_obj
  IS
  SELECT * FROM all_objects;
  TYPE obj_table_type IS TABLE OF cur_obj%ROWTYPE;
  obj_table_var obj_table_type := obj_table_type ();
  BEGIN
  OPEN cur_obj;
  FETCH cur_obj
  BULK COLLECT INTO obj_table_var LIMIT 500;
  CLOSE cur_obj;
  FOR i IN obj_table_var.FIRST .. obj_table_var.LAST
  LOOP
  DBMS_OUTPUT.put_line (obj_table_var (i).object_name);
  END LOOP;
  EXCEPTION
  WHEN OTHERS THEN NULL;
  END;

ii) Nested Tables

One column DB tables. Unbounded/ limitless/ no restrictions. Nested tables can be stored in DB columns but associative arrays cannot.  Hence nested tables are used when data selection/ updation takes place continuously.
TYPE type_name IS TABLE OF element_type [NOT NULL];
element_type is any PL/SQL data type except REF CURSOR.

DECLARE
TYPE nested_type IS TABLE OF VARCHAR2(20);
v_nested_type nested_type:= nested_type();
BEGIN
v_nested_type := nested_type('A', 'B', NULL, 'D', 'E', 'F');
dbms_output.put_line('Name is '|| v_nested_type(2));
dbms_output.put_line('Name is '|| v_nested_type(6));
EXCEPTION
 WHEN OTHERS THEN dbms_output.put_line('Error '|| sqlerrm);
END;

iii) VARRAYS(Variable size Arrays)

Bounded. Fixed number of elements hence good choice when the number of elements are known in advance. Varray is stored as an Object.  Use Varrays when number of elements is known & the elements are accessed in sequence. We can delete last element but not first or in between elements.
TYPE type_name IS VARRAY(size_limit) OF element_type[NOT NULL];
element type is any PL/SQL data type except REF CURSOR

DECLARE
 TYPE varray_type IS VARRAY (4) OF NUMBER;
 v_varray_type varray_type:=varray_type();
BEGIN
 v_varray_type := varray_type(1,2,3,4);
 FOR i IN v_varray_type.first..v_varray_type.last LOOP
 dbms_output.put_line(v_varray_type(i));
 END LOOP;
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error '|| sqlerrm);
END;

Differences:

VARRAY Nested Table Associative Array
 Example
SET SERVEROUTPUT ON 
CLEAR SCREEN;
DECLARE
 TYPE varray_type IS VARRAY (10) OF VARCHAR2(20);
 v_day varray_type := varray_type ();
BEGIN
 v_day.extend(7);--Give 7 to extend till upper bound 
 v_day(1) := 'SUNDAY';
 v_day(2) := 'MONDAY';
 v_day(3) := 'TUESDAY';
 v_day(4) := 'WEDDAY';
 v_day(5) := 'THURSDAY';
 v_day(6) := 'FRIDAY';
 v_day(7) := 'SATURDAY';
 dbms_output.put_line(v_day(1));
 dbms_output.put_line(v_day(7));
 dbms_output.put_line('v_day.LIMIT : '||v_day.LIMIT);
 dbms_output.put_line('v_day.COUNT : '||v_day.COUNT);
 dbms_output.put_line('v_day.FIRST : '||v_day.FIRST);
 dbms_output.put_line('v_day.LAST : '||v_day.LAST);
 dbms_output.put_line('v_day.PRIOR(3) : '||v_day.PRIOR(3));
 dbms_output.put_line('v_day.NEXT(4) : '||v_day.NEXT(4));
 v_day.TRIM();
 dbms_output.put_line('v_day.COUNT after TRIM : '||v_day.COUNT);
 v_day.TRIM(2);
 dbms_output.put_line('v_day.COUNT after 2 TRIMs : '||v_day.COUNT);
 v_day.DELETE;
 dbms_output.put_line('v_day.COUNT after DELETE : '||v_day.COUNT);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error '|| sqlerrm);
END;

/***************dbms_output***************/
SUNDAY
SATURDAY
v_day.LIMIT : 10
v_day.COUNT : 7
v_day.FIRST : 1
v_day.LAST : 7
v_day.PRIOR(3) : 2
v_day.NEXT(4) : 5
v_day.COUNT after TRIM : 6
v_day.COUNT after 2 TRIMs : 4
v_day.COUNT after DELETE : 0
CLEAR SCREEN;
DECLARE
 TYPE nested_type IS TABLE OF VARCHAR2(20);
 v_day nested_type := nested_type();
BEGIN
 v_day.extend(10);
 v_day(1) := 'SUNDAY';
 v_day(2) := 'MONDAY';
 v_day(3) := 'TUESDAY';
 v_day(4) := 'WEDDAY';
 v_day(5) := 'THURSDAY';
 v_day(6) := 'FRIDAY';
 v_day(7) := 'SATURDAY';
 v_day(8) := 'JANUARY';
 v_day(9) := 'FEBRUARY';
 dbms_output.put_line(v_day(1));
 dbms_output.put_line(v_day(9));
 dbms_output.put_line('v_day.LIMIT : '||v_day.LIMIT);
 dbms_output.put_line('v_day.COUNT : '||v_day.COUNT);
 dbms_output.put_line('v_day.FIRST : '||v_day.FIRST);
 dbms_output.put_line('v_day.LAST : '||v_day.LAST);
 dbms_output.put_line('v_day.PRIOR(3) : '||v_day.PRIOR(3));
 dbms_output.put_line('v_day.NEXT(4) : '||v_day.NEXT(4));
 v_day.TRIM();
 dbms_output.put_line('v_day.COUNT after TRIM : '||v_day.COUNT);
 v_day.TRIM(2);
 dbms_output.put_line('v_day.COUNT after 2 elements TRIM : '||v_day.COUNT);
 v_day.DELETE(3);
 dbms_output.put_line('v_day.COUNT after 3rd element DELETE : '||v_day.COUNT);
 IF v_day.EXISTS(3)
 THEN dbms_output.put_line(v_day(3));
 ELSE dbms_output.put_line('Element 3 do not exist');
 END IF;
 v_day.DELETE;
 dbms_output.put_line('v_day.COUNT after DELETE : '||v_day.COUNT);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error '|| sqlerrm);
END;

/***************dbms_output***************/
SUNDAY
FEBRUARY
v_day.LIMIT : 
v_day.COUNT : 10
v_day.FIRST : 1
v_day.LAST : 10
v_day.PRIOR(3) : 2
v_day.NEXT(4) : 5
v_day.COUNT after TRIM : 9
v_day.COUNT after 2 elements TRIM : 7
v_day.COUNT after 3rd element DELETE : 6
Element 3 do not exist
v_day.COUNT after DELETE : 0
CLEAR SCREEN;
DECLARE
 TYPE associative_type IS TABLE OF VARCHAR2(30) INDEX BY VARCHAR2(20);
 v_day associative_type;
BEGIN
 v_day('I') := 'SUNDAY';
 v_day('H') := 'MONDAY';
 v_day('G') := 'TUESDAY';
 v_day('F') := 'WEDDAY';
 v_day('E') := 'THURSDAY';
 v_day('D') := 'FRIDAY';
 v_day('C') := 'SATURDAY';
 v_day('B') := 'JANUARY';
 v_day('A') := 'FEBRUARY';
 dbms_output.put_line('v_day(A) '||v_day('A'));
 dbms_output.put_line('v_day(I) '||v_day('I'));
 dbms_output.put_line('v_day.COUNT : '||v_day.COUNT);
 dbms_output.put_line('v_day.FIRST : '||v_day.FIRST);
 dbms_output.put_line('v_day.LAST : '||v_day.LAST);
 dbms_output.put_line('v_day.PRIOR(C) : '||v_day.PRIOR('C'));
 dbms_output.put_line('v_day.NEXT(E) : '||v_day.NEXT('E'));
 v_day.DELETE('C');
 dbms_output.put_line('v_day.COUNT after C element DELETE : '||v_day.COUNT);
 IF v_day.EXISTS('B')
 THEN dbms_output.put_line(v_day('B'));
 ELSE dbms_output.put_line('Element B do not exist');
 END IF;
 v_day.DELETE;
 dbms_output.put_line('v_day.COUNT after DELETE : '||v_day.COUNT);
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error '|| sqlerrm);
END;

/***************dbms_output***************/
v_day(A) FEBRUARY v_day(I) SUNDAY v_day.COUNT : 9 v_day.FIRST : A v_day.LAST : I v_day.PRIOR(C) : B v_day.NEXT(E) : F v_day.COUNT after C element DELETE : 8 JANUARY v_day.COUNT after DELETE : 0
Number of Elements Fixed/ Bounded Unbounded Unbounded
 Subscript/ Index Type  Integer – Maintained by Oracle. Index values should be in sequence  Integer – Maintained by Oracle. Index values may not be in sequence. VARCHAR or Integer – User defined
Dense (Continuous) or Sparse Always continuous. We cannot delete middle/ in between element. Starts with continuous but can become sparse later. Means we can delete elements later. We can update or delete some elements if required. Both

Collection Methods

COUNT
FIRST
LAST
DELETE
DELETE(n)
PRIOR(n)
NEXT(n)
EXISTS

Yes Yes Yes
LIMIT Yes Yes but gives no value No
TRIM Yes Yes No
EXTEND Yes Yes No

27. Temporal datatypes in Oracle
i) DATE datatype
ii) TIMESTAMP datatype
iii) INTERVAL datatype


28. Object Tables

Object tables are created by using user defined data types. In an object table each row or record is treated as an Object. Each row in an object table has Object Identifier (OID) which is unique. The rows or objects of an object table can be accessed by other objects within the DB. All object tables automatically inherit the data types from user defined data types.
Syntax: CREATE OR REPLACE TYPE <type_name> AS OBJECT
(column_name1 datatype(size), column_name2 datatype(size));
All user defined data types and objects are stored permanently in data dictionaries: USER_TYPES and USER_OBJECTS.
SELECT type_name, typecode, ATTRIBUTES, methods FROM user_types;
SELECT object_name, object_type FROM user_objects;

Creating user defined object type:
CREATE OR REPLACE TYPE object_name AS OBJECT (

studid           NUMBER (6)
, studname   VARCHAR2 (30)
, dob                DATE
, doa                DATE
, fees               NUMBER (5, 2));

Creating an object table:
CREATE TABLE object_table OF object_name;
SELECT * FROM object_table;–here you see all the above columns

Relational INSERT into an object table:
INSERT INTO object_table VALUES(1234, ‘Object1′, ’01-JAN-1985’, NULL, 500);
INSERT INTO object_table VALUES(5678, ‘Object2′, ’01-JAN-1985′, ’01-JAN-2005’, 500);
INSERT records into specified columns  into an object table:
INSERT INTO object_table(studid, studname) VALUES (8912, ‘Object3’);

INSERT using CONSTRUCTOR method into an object table:
INSERT INTO object_table VALUES(OBJECT_NAME(3456, ‘Object4’, ‘9-Jan-1985’, ‘9-jAN-2005’, 200));

Creating table using user defined data type:
CREATE TABLE user_defined_table(

studdetails object_name
, semester VARCHAR2(10)
, semstartdate DATE
, semenddate DATE
, specialization VARCHAR2(10));

SELECT * FROM user_defined_table;
INSERT INTO user_defined_table VALUES (OBJECT_NAME (3456, ‘Object4’, ‘9-Jan-1985’, ‘9-jAN-2005′, 200), 1, ’10-DEC-2010′, ’10-DEC-2013’, ‘CSC’);


29. API for Party Creation

CREATE OR REPLACE PACKAGE BODY APPS.CUST_CONV_PKG
AS
PROCEDURE CUST_CONV_PROC (p_errbuf OUT VARCHAR2, p_retcode OUT NUMBER)
IS
lv_organization_rec apps.hz_party_v2pub.organization_rec_type;
x_return_status VARCHAR2 (10);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (2000);
l_party_id NUMBER;
l_party_number VARCHAR2 (30);
l_party_profile_id NUMBER;
BEGIN
mo_global.init (‘AR’);
FOR cust_cur IN (SELECT party_name FROM CUST_STG_TAB)
LOOP
lv_organization_rec.created_by_module := ‘TCA_V1_API’;
lv_organization_rec.organization_name := cust_cur.party_name;
apps.hz_party_v2pub.create_organization (‘T’,
lv_organization_rec,
x_return_status,
x_msg_count,
x_msg_data,
l_party_id,
l_party_number,
l_party_profile_id);
END LOOP;
END;
END CUST_CONV_PKG;
/


30. REF CURSOR
General static cursor will be defined under DECLARE with an sql qeury. In REF cursor under declaration just define ref cursor type and the actual select query executes dynamically at BEGIN block or run time. Means we dont define the query at DECLARE, we get this only at run time (BEGIN) based on some conditions.
REF CURSOR is basically a data type.

A cursor variable can be associated with different queries at run-time.

A REF CURSOR with RETURN data type is strong REF CURSOR type. (As below)

DECLARE
TYPE ref_cursor IS REF CURSOR
RETURN emp%ROWTYPE;
ref_cur_var ref_cursor;
rec_emp emp%ROWTYPE;
BEGIN
OPEN ref_cur_var FOR SELECT * FROM emp;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
DBMS_OUTPUT.put_line ('----------');
OPEN ref_cur_var FOR
SELECT * FROM emp WHERE DEPTNO = 10;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
END;

A REF CURSOR without RETURN data type is weak REF CURSOR type. (As below)

DECLARE
TYPE ref_cursor IS REF CURSOR;
ref_cur_var ref_cursor;
rec_emp emp%ROWTYPE;
BEGIN
OPEN ref_cur_var FOR SELECT * FROM emp;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
DBMS_OUTPUT.put_line (‘———-‘);
OPEN ref_cur_var FOR SELECT * FROM emp WHERE DEPTNO = 10;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
END;

Opening the cursor variable that contains the query

DECLARE
TYPE ref_cursor IS REF CURSOR;
ref_cur_var ref_cursor;
rec_emp emp%ROWTYPE;
lv_query VARCHAR2(50);
BEGIN
lv_query := ‘SELECT * FROM EMP’;
OPEN ref_cur_var FOR lv_query;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
DBMS_OUTPUT.put_line (‘———-‘);
OPEN ref_cur_var FOR SELECT * FROM emp WHERE DEPTNO = 10;
FETCH ref_cur_var INTO rec_emp;
WHILE ref_cur_var%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename);
FETCH ref_cur_var INTO rec_emp;
END LOOP;
CLOSE ref_cur_var;
END;


31. SYS_REFCURSOR

When we return more than one value from function we can use SYS_REFCURSOR. Oracle 9i introduced the pre-defined SYS_REFCURSOR. Here no need to define own REF CURSOR types. SYS_REFCURSOR can be used to pass cursors from and to a stored procedure.
We dont need to open the cursor returned by function.

Example to return multiple rows from the stored procedure.

Define a function:

CREATE OR REPLACE FUNCTION emp_func (p_deptno NUMBER)
RETURN SYS_REFCURSOR
IS
ref_cur_var SYS_REFCURSOR;
lv_query VARCHAR2 (100);
BEGIN
lv_query := 'SELECT * FROM EMP WHERE DEPTNO =' || p_deptno;
OPEN ref_cur_var FOR lv_query;
RETURN ref_cur_var;
END;
SET SERVEROUTPUT ON;
DECLARE
cur_emp SYS_REFCURSOR;
rec_emp emp%ROWTYPE;
BEGIN
cur_emp := emp_func (20);
FETCH cur_emp INTO rec_emp;
WHILE cur_emp%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename || ' Department is ' || rec_emp.deptno);
FETCH cur_emp INTO rec_emp;
END LOOP;
CLOSE cur_emp;
DBMS_OUTPUT.put_line ('--------');
cur_emp := emp_func (10);
FETCH cur_emp INTO rec_emp;
WHILE cur_emp%FOUND
LOOP
DBMS_OUTPUT.put_line (rec_emp.ename || ' Department is ' || rec_emp.deptno);
FETCH cur_emp INTO rec_emp;
END LOOP;
CLOSE cur_emp;
END; /***********Output************/ JONES Department is 20 SCOTT Department is 20 FORD Department is 20 SMITH Department is 20 ADAMS Department is 20 -------- KING Department is 10 CLARK Department is 10 MILLER Department is 10

32. Aggregate (or) Group Functions
SUM, COUNT, AVG, MAX, MIN
SELECT deptno, AVG (sal) FROM emp
GROUP BY deptno
ORDER BY deptno;
We should not use aggregate functions in WHERE clause. Instead use in sub query to fetch the result.
Valid — SELECT MAX (effective_start_date), MIN (person_id) FROM hr.per_all_people_f;
Invalid — SELECT MAX (AVG(effective_start_date-SYSDATE)) FROM hr.per_all_people_f; — nested group function without GROUP BY
Invalid — SELECT AVG(effective_start_date) FROM hr.per_all_people_f;
Valid — SELECT AVG(effective_start_date-SYSDATE), AVG(person_id) FROM hr.per_all_people_f;


33. Analytic Functions and Partition By Clause
1. function(arg1…argn) OVER ([PARTITION BY <..>] [ORDER BY <..>] [<window_clause>])
Here <window_clause> = Range = Between values
– SELECT e.*, AVG (e.sal) OVER (PARTITION BY e.deptno ORDER BY e.sal) avg_sal FROM emp e
– SELECT e.*, SUM (e.sal) OVER (PARTITION BY e.deptno ORDER BY e.sal) avg_sal FROM emp e
– SELECT e.*, COUNT (e.sal) OVER (PARTITION BY e.deptno ORDER BY e.ename) avg_sal FROM emp e
– SELECT e.*, COUNT (e.empno) OVER (PARTITION BY e.deptno) avg_sal FROM emp e
2. RANK () OVER ([PARTITION BY <..>] [ORDER BY <..>] [<window_clause>])
3. DENSE_RANK () OVER ([PARTITION BY <..>] [ORDER BY <..>] [<window_clause>])
4. ROW_NUMBER() OVER ([PARTITION BY <..>] [ORDER BY <..>] [<window_clause>])
5. LEAD and LAG – LEAD/LAG(<sql_expr>, <offset>, <default>) OVER (<analytic_clause>)
LEAD – Computes an expression on the next row and return the value to current row.
LAG – Computes an expression on the previous row and return the value to current row.
6. FIRST and LAST – function(arg1…argn) KEEP (DENSE_RANK FIRST ORDER BY <expr>) OVER (PARTITION BY <…>)
FIRST and LAST always uses DENSE_RANK. They don’t have ORDER BY and don’t support <window_clause>
7.  FIRST_VALUE and LAST_VALUE – FIRST_VALUE/LAST_VALUE(<sql_expr>) OVER (<analytic_clause>)
FIRST_VALUE – Computes on the column of first record and return the value to all columns of that group
LAST_VALUE – Computes on the column of last record and return the value to all columns of that group
8. LISTAGG and NTH VALUE
LISTAGG – Concatenates data into single column. Means returns single column data into one row
NTH_VALUE – Returns particular row in a query

The aggregate functions groups the columns where as the analytic functions executes for each and every record and displays in result set. The analytic function depends on the order records where as the aggregate function doesn’t depend on the order of records. They give same result irrespective of order. Execute below two queries to know the difference
1. SELECT deptno, AVG (sal) FROM emp GROUP BY deptno ORDER BY deptno;
2. SELECT deptno, AVG (sal) OVER (PARTITION BY DEPTNO) FROM emp;

Sample Query
SELECT ROW_NUMBER () OVER (ORDER BY sal DESC) ROW_NUMBER, ROWNUM, RANK () OVER (ORDER BY sal DESC) RANK, DENSE_RANK () OVER (ORDER BY sal DESC) DENSE_RANK,
SUM (a.sal) OVER (PARTITION BY deptno ORDER BY sal DESC) salary_sum, a.*
FROM emp a;

ROW_NUMBER – Numbers in sequential order
ROWNUM – Pseudo column. Sequence number in which rows are retrieved from DB. Hence the numbers are not aligned here. Observe the difference between ROW_NUMBER and ROWNUM
RANK – Here 2nd and 3rd record has salary as 3000. So RANK 2 has been assigned to both the columns. But for next column we get RANK 4 excluding 3. Similar is the case with RANK 10 and 12
DENSE_RANK – This is similar to RANK but here the previous numbers doesn’t get eliminated. Like 2, 2 and 3, also 9, 9 and 10
SALARY_SUM – It sums up salary by record wise group by deptno. Means for deptno = 10, we have sal as 5000. Now to next deptno = 10, this 5000 sums up with 2450 and gives value as 7450 and so on…
LEAD
SELECT e.*,  LEAD (e.sal, 1, 0) OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) lead_sal FROM emp e

LAG
SELECT e.*,  LAG(e.sal, 1, 0) OVER (PARTITION BY e.deptno ORDER BY e.sal DESC) lead_sal FROM emp e

FIRST_VALUE
SELECT e.*, FIRST_VALUE (e.sal) OVER (PARTITION BY e.deptno) FIRST_VALUE FROM emp e

LAST_VALUE
SELECT e.*, LAST_VALUE (e.sal) OVER (PARTITION BY e.deptno) LAST_VALUE FROM emp e

FIRST
SELECT e.*, AVG (e.sal) KEEP (DENSE_RANK FIRST ORDER BY e.sal) OVER (PARTITION BY e.deptno) FIRST FROM emp e

LAST
SELECT e.*, AVG (e.sal) KEEP (DENSE_RANK LAST ORDER BY e.sal) OVER (PARTITION BY e.deptno) LAST FROM emp e

LISTAGG
SELECT deptno, LISTAGG (ename, ‘||’) WITHIN GROUP (ORDER BY ename) emp_name FROM emp GROUP BY deptno

Few more examples

In traditional sql statement, we have to use a sub query to get sum of all salaries.
Here the salary_sum (36369) is the sum of all salaries in emp table.

Using analytic functions, we get the same result without the use of subquery.

The ORDER BY clause changes the way of calculating the sum.
Here we get accumulative sum for the same job.

Calculation of employee salary as portion of the total company salaries

Sum of salaries per department

Partition identifies rows to aggregate. Rows within the partition must have the same DEPTNO value. Though analytic functions give aggregate result as above, they do not group the result set. They return the group value multiple times each record.

Partition can be defined by multiple columns/ expressions. PARTITION BY can take any traditional sql expression. Analytic functions are computed after all joins, WHERE clause, GROUP BY and HAVING are computed on the query. The main ORDER BY clause of the query operates after the analytic functions. So analytic functions can only appear in the select list and in the main ORDER BY clause of the query.

Traditional sql expression returning the same result set, is less intuitive

To specify the order of the reocrds in the partition, we should use the ORDER BY clause inside the OVER() clause. This is different from the ORDER BY cluase of the main query

Syntax of ORDER BY in analytic function
ORDER BY <col/s> [ASC][DESC][NULLS FIRST][NULLS LAST]

The whole result set can be sorted

The ROW_NUMBER function assigns a unique number to each row to which it is applied, in the ordered sequence of rows specified in the order by clause, beginning with 1

Partition clause is optional. If omitted, entire result set is the partition


34. Difference between Procedure and Function

PROCEDURE FUNCTION
Procedure may return or may not return a value Function has to return a single value and should have RETURN clause in its definition
It may return one or more values using OUT & INOUT parameters  
Procedure can be called only in PL/SQL block Function can be called in SELECT statements
Procedure can have IN, OUT, INOUT parameters Function can have only IN parameter
IN – Read only;
OUT – Write only;
IN OUT – Read/ write;

35. TRIGGERS
We use triggers when we perform DML operations on table or view.
We have two types of triggers:
1. Statement level trigger – Trigger executes only once for DML statements.
2. Row level trigger – Trigger executes for each and every DML statement (for each and every record).
Syntax
CREATE OR REPLACE TRIGGER trigger_name
BEFORE/ AFTER
trigger_event INSERT/ UPDATE/ DELETE
ON table_name
for_each_row
where_condition
DECLARE


END;

Execution order:
1. Before Statement level
2. Before Row level
3. After Row level
4. After Statement level


36. EXCEPTIONS

Exception: Abnormal termination of program. Run time errors are Exceptions. The compilation errors can be checked in user_errors table.

CREATE OR REPLACE FUNCTION sample_exception_1 (
 p_input1 NUMBER
 ,p_input2 NUMBER
) RETURN NUMBER IS
 ln_output NUMBER;
BEGIN
 RETURN p_input1 / p_input2;
END;

BEGIN
 dbms_output.put_line(sample_exception_1(10,0));
END;

/*****************Run Time Error*****************/
Error report -
ORA-01476: divisor is equal to zero
ORA-06512: at "APPS.SAMPLE_EXCEPTION_1", line 7
ORA-06512: at line 2
01476. 00000 - "divisor is equal to zero"
*Cause: 
*Action:

Exception Handling

CREATE OR REPLACE FUNCTION sample_exception_1 (
 p_input1 NUMBER
 ,p_input2 NUMBER
) RETURN NUMBER IS
 ln_output NUMBER;
BEGIN
 RETURN p_input1 / p_input2;
EXCEPTION
 WHEN zero_divide THEN
 dbms_output.put_line('Do not divide with zero');
 RETURN NULL;
END;

BEGIN
 dbms_output.put_line(sample_exception_1(10,0) );
END;

/*****************dbms_output.put_line*****************/
Do not divide with zero
DECLARE
 v_emp_details emp%rowtype;
BEGIN
 SELECT * INTO
 v_emp_details
 FROM emp
 WHERE empno = 9999;
EXCEPTION
 WHEN no_data_found THEN
 dbms_output.put_line('Invalid employee number..!!');
 WHEN OTHERS THEN
 dbms_output.put_line('Others exception!!');
END;

/*****************dbms_output.put_line*****************/
Invalid employee number..!!

Others should always be placed as last handler else we get compilation error.

Types

Pre-Defined Exceptions User-Defined Exception
Named Exception Unnamed Exception
TOO_MANY_ROWS; NO_DATA_FOUND; INVALID_NUMBER; OTHERS and so on PRAGMA EXCEPTION_INIT
RAISE RAISE_APPLICATION_ERROR

Pre-Defined Exceptions are TOO_MANY_ROWS; NO_DATA_FOUND; INVALID_NUMBER; OTHERS…& so on

User-Defined Exception – We have to raise the exception explicitly using RAISE keyword.

DECLARE
 lv_user EXCEPTION;
BEGIN
 IF SYSDATE = SYSDATE
 THEN
 RAISE lv_user;
 END IF;
EXCEPTION
 WHEN lv_user THEN DBMS_OUTPUT.PUT_LINE ('User defined exception');
 WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE ('Error');
END;
DECLARE
 salary_too_high EXCEPTION;
 current_salary NUMBER := 20000;
 max_salary NUMBER := 10000;
 erroneous_salary NUMBER;
BEGIN
 NULL;
 BEGIN
 NULL;
 BEGIN
 NULL;
 BEGIN
 BEGIN ---------- sub-block begins
 IF
 current_salary > max_salary
 THEN
 RAISE salary_too_high; -- raise the exception
 END IF;
 EXCEPTION
 WHEN salary_too_high THEN
 -- first step in handling the error
 dbms_output.put_line('Salary ' || erroneous_salary || ' is out of range.');
 dbms_output.put_line('Maximum salary is ' || max_salary || '.');
 RAISE; -- reraise the current exception
 WHEN OTHERS THEN
 -- first step in handling the error
 dbms_output.put_line('Salary 1' || erroneous_salary || ' is out of range.');
 dbms_output.put_line('Maximum salary is 1' || max_salary || '.');
 RAISE;
 END; ------------ sub-block ends
 EXCEPTION
 WHEN salary_too_high THEN
 -- first step in handling the error
 dbms_output.put_line('Salary 2' || erroneous_salary || ' is out of range.');
 dbms_output.put_line('Maximum salary is 2' || max_salary || '.');
 RAISE; -- reraise the current exception
 WHEN OTHERS THEN
 -- handle the error more thoroughly
 erroneous_salary := current_salary;
 current_salary := max_salary;
 dbms_output.put_line('Revising salary from ' || erroneous_salary || ' to ' || current_salary || '.');
 RAISE;
 END;
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Outside of block.');
 RAISE;
 END;
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Outside of final block.');
 END;
EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Outside of last block.');
END;
CREATE OR REPLACE PACKAGE rollbacktest AUTHID current_user --code (PL/SQL) to execute with the privileges of the current user
 IS
 PROCEDURE main (
 x_errbuf OUT NOCOPY VARCHAR2
 , --NOCOPY is a hint to the PL/SQL compiler to pass OUT and IN OUT parameters by reference instead of by value
 x_retcode OUT NOCOPY NUMBER
 );
END rollbacktest;
CREATE OR REPLACE PACKAGE BODY rollbacktest IS
 PROCEDURE a IS
 CURSOR c1 IS SELECT * FROM emp;
 TYPE nested_tbl IS TABLE OF c1%rowtype;
 nested_typ nested_tbl := nested_tbl ();
 BEGIN
 OPEN c1;
 LOOP
 nested_typ.extend;
 FETCH c1 BULK COLLECT INTO nested_typ;
 EXIT WHEN nested_typ.count = 0;
 FOR i IN nested_typ.first..nested_typ.last LOOP
 nested_typ(i).empno := 1234;
 nested_typ(i).ename := NULL;
 nested_typ(i).job := NULL;
 nested_typ(i).mgr := NULL;
 nested_typ(i).hiredate := NULL;
 nested_typ(i).sal := NULL;
 nested_typ(i).comm := NULL;
 nested_typ(i).deptno := NULL;
 INSERT /*+ ignore_row_on_dupkey_index(emp,pk_emp) */ INTO emp VALUES nested_typ ( i );
 END LOOP;
 END LOOP;
 CLOSE c1;
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error in first block ' || sqlerrm);
 RAISE;
 END a;
 PROCEDURE b IS
 CURSOR c2 IS SELECT * FROM emp;
 TYPE plsql_tbl IS TABLE OF c2%rowtype INDEX BY BINARY_INTEGER;
 plsql_typ plsql_tbl;
 BEGIN
 OPEN c2;
 LOOP
 FETCH c2 BULK COLLECT INTO plsql_typ;
 EXIT WHEN plsql_typ.count = 0;
 FOR i IN plsql_typ.first..plsql_typ.last LOOP
 plsql_typ(i).empno := 7839;
 plsql_typ(i).ename := NULL;
 plsql_typ(i).job := NULL;
 plsql_typ(i).mgr := NULL;
 plsql_typ(i).hiredate := NULL;
 plsql_typ(i).sal := NULL;
 plsql_typ(i).comm := NULL;
 plsql_typ(i).deptno := NULL;
 INSERT INTO emp VALUES plsql_typ ( i );
 END LOOP;
 END LOOP;
 CLOSE c2;
 EXCEPTION
 WHEN OTHERS THEN
 dbms_output.put_line('Error in second block ' || sqlerrm);
 RAISE;
 END b;
 PROCEDURE main (
 x_errbuf OUT NOCOPY VARCHAR2
 ,x_retcode OUT NOCOPY NUMBER
 )
 IS
 BEGIN
 a;
 b;
 COMMIT;
 EXCEPTION
 WHEN OTHERS THEN dbms_output.put_line('Error in main block ' || sqlerrm);
 ROLLBACK;
 END main;
END rollbacktest;
SET Serveroutput On;
DECLARE
 l_err_buf VARCHAR2(100);
 l_ret_code NUMBER;
BEGIN
 rollbacktest.main(
 x_errbuf => l_err_buf
 ,x_retcode => l_ret_code
 );
END;

37. Pragma

‎‏→ Pragma refers to compiler directive or “Hint”
→ Pragmas are processed at compile time not at run time
Types

PRAGMA AUTONOMOUS_TRANSACTION PRAGMA SERIALLY_REUSABLE PRAGMA RESTRICT_REFERENCES PRAGMA EXCEPTION_INIT PRAGMA INLINE
Session contains only one transaction. Any COMMIT performed will COMMIT whole session.
Say a PLSQL block which contains multiple blocks and when we want to commit any particular block then we use AUTONOMOUS_TRANSACTION. If we do not use PRAGMA AUTONOMOUS_TRANSACTION then the whole session gets commited rather than that particular block/ transaction. Always keep PRAGMA AUTONOMOUS_TRANSACTION in one procedure and business logic in another procedure.
Whenever we call package/ procedure they gets loaded into memory and we use SERIALLY_REUSABLE to delete that memory. This is no longer being supported after Oracle 8i. Used defined exception with a particular error number. User defined message and error number Subprogram call either is OR is not to be in-lined. Increases performance and should be used with small length Functions. Whenever we call a function, program jumps to that function, complete and comeback. This jumping and coming back is a time consuming task. This case we use INLINE. Here the code will come and places itself in the location where we are writing the call. Do not go with this for lengthy functions.

Syntax
CREATE OR REPLACE [FUNCTION/ PROCEDURE] [NAME]
IS
[PRAGMA];
BEGIN


END;

Example 1:

CREATE TABLE log_tbl
  (
  user_name VARCHAR(30),
  error_date DATE,
  object_name VARCHAR(50),
  object_type VARCHAR2(50),
  line_number NUMBER,
  error_code NUMBER,
  error_message VARCHAR(200)
  );
CREATE OR REPLACE PROCEDURE p_error_log
  (p_called_from VARCHAR2,
  p_object_type VARCHAR2,
  p_line_number NUMBER,
  p_sqlcode NUMBER,
  p_sqlmsg VARCHAR2
  )
  IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
  INSERT INTO log_tbl (user_name, error_date, object_name, object_type, line_number, error_code, error_message)
  VALUES (user, SYSDATE, p_called_from, p_object_type, p_line_number, p_sqlcode, p_sqlmsg);
  COMMIT; --This COMMIT is localized to this particular block since we used PRAGMA AUTONOMOUS_TRANSACTION
  END;
CREATE TABLE department
  (
  dept_number NUMBER,
  dept_name VARCHAR2(100),
  dept_location VARCHAR2(100),
  CONSTRAINT departments_pk PRIMARY KEY (dept_number)
  );

SELECT * FROM department; — no rows returned
SELECT * FROM log_tbl; — no rows returned

Now execute below Anonymous block by passing values as 10, MATHS, BANGLORE and 20, PHYSICS, HYDERABAD

DECLARE
  ln_dept_number DEPARTMENT.DEPT_NUMBER%TYPE := :givedeptno;
  lv_dept_name DEPARTMENT.DEPT_NAME%TYPE := :givedeptname;
  ln_dept_location DEPARTMENT.DEPT_LOCATION%TYPE := :givedeptlocation;
  BEGIN
  INSERT INTO department VALUES (ln_dept_number, lv_dept_name, ln_dept_location);
  EXCEPTION
  WHEN OTHERS THEN
  p_error_log(COALESCE($$plsql_unit, 'anonymous block'), $$plsql_type, $$plsql_line, SQLCODE, SQLERRM);
  END;

→ $$plsql_unit — We get procedure name. Since this is anonymous block this will be NULL so it returns value ‘anonymous block’.
→ $$plsql_type – Type of the PLSQL code (package/ procedure/ function)
→ $$plsql_line – Line number where the exception has taken place

SELECT * FROM department;
10 MATHS BANGLORE
20 PHYSICS HYDERABAD
SELECT * FROM log_tbl; — no rows returned

Now execute above block by inserting 10, CHEMISTRY, CHENNAI
SELECT * FROM department;
10 MATHS BANGLORE
20 PHYSICS HYDERABAD
SELECT * FROM log_tbl;
APPS|24-JUL-2018 07:44:51|anonymous block|9|-1|ORA-00001: unique constraint (APPS.DEPARTMENTS_PK) violated|
Below are the output details
user_name = APPS
error_date = 24-JUL-2018 07:44:51
object_name = anonymous block
object_type = NULL
line_number = 9
error_code = -1
error_message = ORA-00001: unique constraint (APPS.DEPARTMENTS_PK) violated

Now try to ROLLBACK — ROLLBACK; Since there is no COMMIT after INSERT above records (10 & 20) should be ROLLBACK’ed

SELECT * FROM department; — no rows returned
SELECT * FROM log_tbl; — Above one record exists since we have COMMIT in that particular block which is acting as localized COMMIT rather than session level. We use PRAGMA AUTONOMOUS_TRANSACTION mainly for auditing purpose.

Example 2:

CREATE TABLE transaction_audit
( audit_id NUMBER NOT NULL,
transaction VARCHAR2(50) NOT NULL,
creation_time TIMESTAMP,
user_name VARCHAR(50)
);

CREATE SEQUENCE tran_audit_id_seq;

CREATE OR REPLACE TRIGGER audit_on_emp
AFTER INSERT OR UPDATE OR DELETE ON department
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
IF INSERTING THEN INSERT INTO transaction_audit VALUES (tran_audit_id_seq.nextval, ‘INSERT’, SYSTIMESTAMP, user);
ELSIF UPDATING THEN INSERT INTO transaction_audit VALUES (tran_audit_id_seq.nextval, ‘UPDATE’, SYSTIMESTAMP, user);
ELSE INSERT INTO transaction_audit VALUES (tran_audit_id_seq.nextval, ‘DELETE’, SYSTIMESTAMP, user);
END IF;
COMMIT;
END;

How to avoid mutations: In triggers mutations can happen and this can be avoided by declaring trigger code with PRAGMA AUTONOMOUS_TRANSACTION.

CREATE TABLE mutation_test
AS (SELECT * FROM user_objects);

CREATE OR REPLACE TRIGGER mutation_test_trig
AFTER UPDATE OF STATUS ON mutation_test
FOR EACH ROW
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM mutation_test WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total number of invalid objects are ‘||v_count);
END;

UPDATE mutation_test
SET status = ‘INVALID’
WHERE object_name = ‘DEPARTMENT’;
Error report –
ORA-04091: table APPS.MUTATION_TEST is mutating, trigger/function may not see it
ORA-06512: at “APPS.MUTATION_TEST_TRIG”, line 4
ORA-04088: error during execution of trigger ‘APPS.MUTATION_TEST_TRIG’

Write the trigger with PRAGMA AUTONOMOUS_TRANSACTION and then execute UPDATE command:
CREATE OR REPLACE TRIGGER mutation_test_trig
AFTER UPDATE OF STATUS ON mutation_test
FOR EACH ROW
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count FROM mutation_test WHERE status = ‘INVALID’;
dbms_output.put_line(‘Total number of invalid objects are ‘||v_count);
END;

UPDATE mutation_test
SET status = ‘INVALID’
WHERE object_name = ‘DEPARTMENT’; –1 row updated.

Mainly we use PRAGMA AUTONOMOUS_TRANSACTION
1) To avoid mutation
2) Auditing purpose – Whenever we have to audit the transaction do not audit in the same code where we are writing business logic. Write in separate Trigger/ Procedure.


38. PRAGMA SERIALLY_REUSABLE

Example 1:
CREATE OR REPLACE package my_global_variables
IS
global_var NUMBER;
END;

BEGIN
my_global_variables.global_var := 100; — Variable assignment is completed and block is also terminated
END;

EXEC dbms_output.put_line(‘The global variable values is ‘||my_global_variables.global_var);
The global variable values is 100

Here the variable is global and memory wont be released even after transaction completed. If we want to release the variable from memory (if no body is using the variable then it should be released), then use PRAGMA SERIALLY_REUSABLE. We are giving instructions to PLSQL code that any variables which are there in package, they should be occupying the memory only when they are in use, once the use is completed release them from memory.

CREATE OR REPLACE package my_global_variables
IS
PRAGMA SERIALLY_REUSABLE;
global_var NUMBER;
END;
— Here global_var is designed in such way that when I am using this variable this will occupy memory. The moment operation is completed the variable will be cleared from memory immediately.

BEGIN
my_global_variables.global_var := 100; — Variable assignment is completed and block is also terminated
END;

EXEC dbms_output.put_line(‘The global variable values is ‘||my_global_variables.global_var);
The global variable values is
Memory is restricted only to that particular block and once it comes out of block, memory will be released.

CREATE OR REPLACE package my_package_nsr
IS
ns_number NUMBER := 100;
END;

CREATE OR REPLACE package my_package_sr
IS
PRAGMA SERIALLY_REUSABLE;
sr_number NUMBER := 200;
END;

BEGIN
dbms_output.put_line(‘The value in Non SERIALLY_REUSABLE package is ‘||my_package_nsr.ns_number); — 100
dbms_output.put_line(‘The value in SERIALLY_REUSABLE package is ‘||my_package_sr.sr_number); — 200
my_package_nsr.ns_number := 50;
dbms_output.put_line(‘The value in Non SERIALLY_REUSABLE package is ‘||my_package_nsr.ns_number); –50
my_package_sr.sr_number := 75;
dbms_output.put_line(‘The value in SERIALLY_REUSABLE package is ‘||my_package_sr.sr_number); –75
END;

The value in Non SERIALLY_REUSABLE package is 100
The value in SERIALLY_REUSABLE package is 200
The value in Non SERIALLY_REUSABLE package is 50
The value in SERIALLY_REUSABLE package is 75

I have terminated from block and executing another block:
BEGIN
dbms_output.put_line(‘The value in Non SERIALLY_REUSABLE package is ‘||my_package_nsr.ns_number); — 50
dbms_output.put_line(‘The value in SERIALLY_REUSABLE package is ‘||my_package_sr.sr_number); — 200
END;

The value in Non SERIALLY_REUSABLE package is 50
The value in SERIALLY_REUSABLE package is 200

Here SERIALLY_REUSABLE  value is 200. Whenever we have a global variable and you reassign the global variable in any program, to that particular program the global variable will get reassigned, it give you the latest value the block is terminated the global variable becomes the old initialized value automatically.


39. PRAGMA EXCEPTION_INIT

DECLARE
 lv_user EXCEPTION;
 PRAGMA EXCEPTION_INIT(lv_user, -20001);
BEGIN
 IF SYSDATE = SYSDATE THEN
 RAISE lv_user;
 END IF;
EXCEPTION
WHEN lv_user THEN
 dbms_output.put_line('User defined exception..!! '||'SQLCODE ' ||SQLCODE||' and '|| 'SQLERRM ' || SQLERRM);
WHEN OTHERS THEN
 dbms_output.put_line('Error..!!');
END;

/**dbms_output**/
User defined exception..!! SQLCODE -20001 and SQLERRM ORA-20001: 

DECLARE
TYPE  my_emp_id IS TABLE OF emp.emp_number%TYPE;
my_emp_id_typ my_emp_id;
ln_error NUMBER;
lv_dml_errors EXCEPTION;
PRAGMA EXCEPTION_INT(lv_dml_errors, -24381); — -24381 is the standard number for DML operations in BULK_COLLECT
BEGIN
SELECT emp_number BULK_COLLECT INTO my_emp_id_type FROM emp WHERE hire_date < ’31-DEC-02′;
–Appending ‘_SR’ to all the senior employees
FORALL i IN my_emp_id_typ.FIRST .. my_emp_id_typ.LAST
UPDATE emp SET job_id = job_id||’_SR’
WHERE my_emp_id_typ(i) = emp.emp_number;
EXCEPTION
WHEN lv_dml_errors THEN
ln_error := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of failed records ‘|| ln_error);
FOR i IN 1..ln_error LOOP
dbms_output.put_line(‘Error number  ‘|| i);
dbms_output.put_line(‘Error message ‘|| SQLERRM(-SQL%BULK_EXCEPTIONS));
END LOOP;
END;


40. PRAGMA INLINE
Example 1:
DECLARE
v_niterations NUMBER := 10000000;
v_start NUMBER;
v_return NUMBER;

FUNCTION sum_numbers (p_value01 IN NUMBER, p_value02 IN NUMBER)
RETURN NUMBER
AS
BEGIN
RETURN p_value01 + p_value02;
END sum_numbers;

BEGIN
v_start := dbms_utility.get_time;
FOR myindex IN 1..v_niterations
LOOP
PRAGMA INLINE (sum_numbers, ‘YES’);
v_return := sum_numbers(1, myindex);
END LOOP;
DBMS_OUTPUT.PUT_LINE(‘Total time: ‘||(dbms_utility.get_time – v_start));
END;


41. RAISE_APPLICATION_ERROR
raise_application_error(custom_error_code, custom_error_message);

The custom_error_code gets printed in SQLCODE and custom_error_message gets printed in SQLERRM.
The Error number must be between -20000 and -20999
DECLARE
BEGIN
IF SYSDATE = SYSDATE
THEN
raise_application_error (-20102, ‘Raise An Application Error’);
END IF;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (SQLERRM);
END;


42. Convert Columns in Rows
CREATE TABLE item_cat_stg(
item_name            VARCHAR2 (100),
cat_set_id1            NUMBER,
cat_id1                    NUMBER,
cat_set_id2           NUMBER,
cat_id2                   NUMBER,
cat_set_id3           NUMBER,
cat_id3                  NUMBER);

INSERT INTO item_cat_stg
VALUES (‘ABC’, 123, 111, 456, 222, 789, 333);

SELECT * FROM item_cat_stg;

Now this data has to be displayed as below.

Use the below query to get the result:
SELECT item_name,
CASE pivot
WHEN 1 THEN cat_set_id1
WHEN 2 THEN cat_set_id2
WHEN 3 THEN cat_set_id3
ELSE NULL
END
cat_set_id,
CASE pivot
WHEN 1 THEN cat_id1
WHEN 2 THEN cat_id2
WHEN 3 THEN cat_id3
ELSE NULL
END
cat_id
FROM item_cat_stg,
( SELECT ROWNUM pivot
FROM DUAL
CONNECT BY LEVEL <= 3);

Now inserting these rows into some interface table.
CREATE TABLE item_cat_int
(item_name VARCHAR2 (100), cat_set_id NUMBER, cat_id NUMBER);

The following ways can be used for INSERTION.
1) Using simple LOOP
DECLARE
CURSOR C1
IS
SELECT item_name,
CASE pivot
WHEN 1 THEN cat_set_id1
WHEN 2 THEN cat_set_id2
WHEN 3 THEN cat_set_id3
ELSE NULL
END
cat_set_id,
CASE pivot
WHEN 1 THEN cat_id1
WHEN 2 THEN cat_id2
WHEN 3 THEN cat_id3
ELSE NULL
END
cat_id
FROM item_cat_stg,
( SELECT ROWNUM pivot
FROM DUAL
CONNECT BY LEVEL <= 3);
l_var C1%ROWTYPE;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO l_var;
EXIT WHEN C1%NOTFOUND;
INSERT INTO item_cat_int
VALUES (l_var.item_name, l_var.cat_set_id, l_var.cat_id);
END LOOP;
CLOSE C1;
END;

2) Using FOR LOOP
DECLARE
CURSOR C1
IS
SELECT item_name,
CASE pivot
WHEN 1 THEN cat_set_id1
WHEN 2 THEN cat_set_id2
WHEN 3 THEN cat_set_id3
ELSE NULL
END
cat_set_id,
CASE pivot
WHEN 1 THEN cat_id1
WHEN 2 THEN cat_id2
WHEN 3 THEN cat_id3
ELSE NULL
END
cat_id
FROM item_cat_stg,
( SELECT ROWNUM pivot
FROM DUAL
CONNECT BY LEVEL <= 3);
l_var C1%ROWTYPE;
BEGIN
FOR i IN C1
LOOP
INSERT INTO item_cat_int
VALUES (i.item_name, i.cat_set_id, i.cat_id);
END LOOP;
END;

3) Using TABLE TYPE
DECLARE
CURSOR C1
IS
SELECT item_name,
CASE pivot
WHEN 1 THEN cat_set_id1
WHEN 2 THEN cat_set_id2
WHEN 3 THEN cat_set_id3
ELSE NULL
END
cat_set_id,
CASE pivot
WHEN 1 THEN cat_id1
WHEN 2 THEN cat_id2
WHEN 3 THEN cat_id3
ELSE NULL
END
cat_id
FROM item_cat_stg,
( SELECT ROWNUM pivot
FROM DUAL
CONNECT BY LEVEL <= 3);
TYPE l_type IS TABLE OF C1%ROWTYPE
INDEX BY BINARY_INTEGER;
l_var_type l_type;
l_counter NUMBER;
TYPE l_type1 IS TABLE OF item_cat_int%ROWTYPE
INDEX BY BINARY_INTEGER;
l_var_type1 l_type1;
BEGIN
EXECUTE IMMEDIATE ‘TRUNCATE TABLE item_cat_int’;
OPEN C1;
LOOP
l_var_type.DELETE;
l_counter := 0;
FETCH C1
BULK COLLECT INTO l_var_type
LIMIT 100;
FOR i IN 1 .. l_var_type.COUNT
LOOP
l_counter := l_counter + 1;
–l_var_type1.EXTEND;
l_var_type1 (l_counter).item_name := l_var_type (i).item_name;
l_var_type1 (l_counter).cat_set_id := l_var_type (i).cat_set_id;
l_var_type1 (l_counter).cat_id := l_var_type (i).cat_id;
END LOOP;
/*Its good practise to use FORALL for DML statements as it enhances performance*/
FORALL i IN 1 .. l_var_type1.COUNT
INSERT INTO item_cat_int
VALUES l_var_type1 (i);
COMMIT;
EXIT WHEN l_var_type.COUNT < 100;
END LOOP;
END;

Final o/p is shown below:


43. Procedure for comparing two rows in a table
Here the group separator is ~
PROCEDURE compare_rows (p_column_list IN VARCHAR2,
p_old_values IN VARCHAR2,
p_new_values IN VARCHAR2,
p_chg_cols OUT VARCHAR2)
IS
CURSOR c_chg_columns
IS
SELECT LISTAGG (colname, ‘~’) WITHIN GROUP (ORDER BY sequence)
diff_cols
FROM ( SELECT LEVEL sequence,
REGEXP_SUBSTR (p_column_list, ‘[^~]+’, 1, LEVEL) colname,
DECODE (NVL (REGEXP_SUBSTR (REPLACE (p_old_values, ‘~’, ‘~ ‘),'[^~]+’,1,LEVEL),”),
NVL (REGEXP_SUBSTR (REPLACE (p_new_values, ‘~’, ‘~ ‘),'[^~]+’,1,LEVEL),”), ‘MATCH’,’MISMATCH’)
compare FROM DUAL
CONNECT BY LEVEL <= REGEXP_COUNT (p_column_list, ‘[^~]+’))
WHERE compare = ‘MISMATCH’;

l_chg_cols c_chg_columns%ROWTYPE;
BEGIN

OPEN c_chg_columns;
FETCH c_chg_columns INTO l_chg_cols;
CLOSE c_chg_columns;

p_chg_cols := REPLACE (l_chg_cols.diff_cols, ‘~’, ‘|’);

END compare_rows;

Calling the above procedure:
compare_rows ( lv_column_list, lv_old_values, lv_new_values, lv_chg_columns);
Here lv_column_list is the list of table columns. lv_column_list := ‘ENAME~EMPNO~DEPTNO’;
lv_old_values — Get the old values from record type cursor.
lv_new_values — Get the new values from record type cursor.
lv_chg_columns — This is out parameter and we get list of columns which have differences between lv_old_values and lv_new_values.


44. Procedure to Insert a new record on top of picking latest/ max(record_id)
We are using PL/SQL Table Type:
DECLARE

CURSOR C2
IS
SELECT MAX (record_id) a, person_id
FROM item_cat_int
GROUP BY person_id;
l_c2 C2%ROWTYPE;
CURSOR C1 (p_person_id NUMBER)
IS
SELECT *
FROM item_cat_int
WHERE 1 = 1
AND record_id = (SELECT MAX (record_id)
FROM item_cat_int
WHERE person_id = p_person_id)
ORDER BY person_id;

TYPE l_type IS TABLE OF C1%ROWTYPE INDEX BY BINARY_INTEGER;

l_var_type l_type;
l_counter NUMBER;

TYPE l_type1 IS TABLE OF item_cat_int%ROWTYPE INDEX BY BINARY_INTEGER;
l_var_type1 l_type1;

CURSOR CUR_EMP (p_person_id IN NUMBER)
IS
SELECT ENAME
FROM EMP
WHERE empno = p_person_id;

l_cur_emp VARCHAR2 (100);
BEGIN
OPEN C2;
LOOP
FETCH C2 INTO l_c2;
EXIT WHEN C2%NOTFOUND;
OPEN C1 (l_c2.person_id);
LOOP
l_var_type.DELETE;
l_counter := 0;
FETCH C1
BULK COLLECT INTO l_var_type
LIMIT 100;
FOR i IN 1 .. l_var_type.COUNT
LOOP
l_counter := l_counter + 1;
l_var_type1 (l_counter).item_name := l_var_type (i).item_name;
l_var_type1 (l_counter).cat_set_id := l_var_type (i).cat_set_id;
l_var_type1 (l_counter).cat_id := l_var_type (i).cat_id;
l_var_type1 (l_counter).record_id := EMP_S.NEXTVAL;

DBMS_OUTPUT.put_line (l_var_type (i).person_id);

OPEN CUR_EMP (l_var_type (i).person_id);

FETCH CUR_EMP INTO l_cur_emp;

CLOSE CUR_EMP;

DBMS_OUTPUT.put_line (l_cur_emp);

l_var_type1 (l_counter).attribute1 := l_cur_emp;
l_var_type1 (l_counter).attribute2 := l_var_type (i).attribute2;
l_var_type1 (l_counter).attribute3 := l_var_type (i).attribute3;
l_var_type1 (l_counter).person_id := l_var_type (i).person_id;
l_var_type1 (l_counter).effective_date := SYSDATE;

l_cur_emp := NULL;
END LOOP;

FORALL i IN 1 .. l_var_type1.COUNT
INSERT INTO item_cat_int
VALUES l_var_type1 (i);

COMMIT;
EXIT WHEN l_var_type.COUNT < 100;
END LOOP;

CLOSE C1;
END LOOP;

CLOSE C2;
END;


45. API to Create, Update and Delete Approved Supplier List (ASL)

APPLIES TO:Oracle Purchasing – Version 12.1.3 and later

–The following SQL statement is a sample API to create, update, and delete approved supplier lists.
–Check the errors in ‘PO_ASL_API_ERRORS’
DECLARE
paslrec   po_approved_supplier_list_rec   ;
paarec    po_asl_attributes_rec      ;
padrec    po_asl_documents_rec      ;
chvrec    chv_authorizations_rec     ;
psicrec   po_supplier_item_capacity_rec   ;
psitrec   po_supplier_item_tolerance_rec  ;
x_errors  PO_ASL_API_ERROR_rec;

x_session_key    NUMBER;
x_return_status    VARCHAR2(30);
x_return_msg    VARCHAR2(2000);

BEGIN
— Populating values in paslrec
paslrec := new po_approved_supplier_list_rec();
paslrec.user_key := po_tbl_number(1);
paslrec.process_action := po_tbl_varchar30(‘CREATE’);
paslrec.global_flag := po_tbl_varchar1(‘Y’);
paslrec.owning_organization_id := po_tbl_number(NULL);
paslrec.owning_organization_dsp := po_tbl_varchar240(‘Vision Operations’);
paslrec.vendor_business_type:= po_tbl_varchar25(‘Distributor’);
paslrec.asl_status_id := po_tbl_number(NULL);
paslrec.asl_status_dsp := po_tbl_varchar25(‘Debarred’);
paslrec.manufacturer_id := po_tbl_number(NULL);
paslrec.manufacturer_dsp := po_tbl_varchar100(NULL);
paslrec.vendor_id := po_tbl_number(NULL);
paslrec.vendor_dsp := po_tbl_varchar240(‘Advanced Network Devices’);
paslrec.item_id := po_tbl_number(NULL);
paslrec.item_dsp := po_tbl_varchar50(‘555Item01’);
paslrec.category_id := po_tbl_number(NULL);
paslrec.category_dsp := po_tbl_varchar240(NULL);
paslrec.vendor_site_id := po_tbl_number(NULL);
paslrec.vendor_site_dsp := po_tbl_varchar50(‘SAN BURNO’);
paslrec.primary_vendor_item := po_tbl_varchar25(NULL);
paslrec.manufacturer_asl_id := po_tbl_number(NULL);
paslrec.manufacturer_asl_dsp := po_tbl_varchar50(‘GE Medical’);
paslrec.review_by_date := po_tbl_date(NULL);
paslrec.comments := po_tbl_varchar240(‘Test ASL API script’);
paslrec.attribute_category := po_tbl_varchar30(NULL);
paslrec.attribute1 := po_tbl_varchar240(‘TEST1’);
paslrec.attribute2 := po_tbl_varchar240(NULL);
paslrec.attribute3 := po_tbl_varchar240(NULL);
paslrec.attribute4 := po_tbl_varchar240(NULL);
paslrec.attribute5 := po_tbl_varchar240(NULL);
paslrec.attribute6 := po_tbl_varchar240(NULL);
paslrec.attribute7 := po_tbl_varchar240(NULL);
paslrec.attribute8 := po_tbl_varchar240(NULL);
paslrec.attribute9 := po_tbl_varchar240(NULL);
paslrec.attribute10 := po_tbl_varchar240(NULL);
paslrec.attribute11 := po_tbl_varchar240(NULL);
paslrec.attribute12 := po_tbl_varchar240(NULL);
paslrec.attribute13 := po_tbl_varchar240(NULL);
paslrec.attribute14 := po_tbl_varchar240(NULL);
paslrec.attribute15 := po_tbl_varchar240(NULL);
paslrec.request_id := po_tbl_number(NULL);
paslrec.program_application_id := po_tbl_number(NULL);
paslrec.program_id := po_tbl_number(NULL);
paslrec.program_update_date := po_tbl_date(NULL);
paslrec.disable_flag := po_tbl_varchar1(‘N’);

— Populating values in paarec
paarec := new po_asl_attributes_rec();
paarec.user_key:= po_tbl_number(1);
paarec.process_action := po_tbl_varchar30(‘ADD’);
paarec.using_organization_id:= po_tbl_number(-1);
paarec.using_organization_dsp := po_tbl_varchar240(NULL);
paarec.release_generation_method:= po_tbl_varchar25(NULL);
paarec.release_generation_method_dsp:= po_tbl_varchar50(‘Automatic Release’);
paarec.purchasing_unit_of_measure_dsp := po_tbl_varchar25(‘Dozen’);
paarec.enable_plan_schedule_flag_dsp:= po_tbl_varchar1(‘Y’);
paarec.enable_ship_schedule_flag_dsp:= po_tbl_varchar1(null);
paarec.plan_schedule_type := po_tbl_varchar25(null);
paarec.plan_schedule_type_dsp := po_tbl_varchar50(null);
paarec.ship_schedule_type := po_tbl_varchar25(null);
paarec.ship_schedule_type_dsp := po_tbl_varchar50(null);
paarec.plan_bucket_pattern_id := po_tbl_number(null);
paarec.plan_bucket_pattern_dsp:= po_tbl_varchar50(null);
paarec.ship_bucket_pattern_id := po_tbl_number (null);
paarec.ship_bucket_pattern_dsp:= po_tbl_varchar50(null);
paarec.enable_autoschedule_flag_dsp := po_tbl_varchar1(null);
paarec.scheduler_id := po_tbl_number(NULL);
paarec.scheduler_dsp:= po_tbl_varchar50(‘2213Doe, Mr. 2213John’);
paarec.enable_authorizations_flag_dsp := po_tbl_varchar1(‘Y’);
paarec.vendor_id:= po_tbl_number(null);
paarec.vendor_dsp := po_tbl_varchar240(‘Advanced Network Devices’);
paarec.vendor_site_id := po_tbl_number (null);
paarec.vendor_site_dsp:= po_tbl_varchar50(‘SAN BURNO’);
paarec.item_id:= po_tbl_number(null);
paarec.item_dsp := po_tbl_varchar50(‘555Item01’);
paarec.category_id:= po_tbl_number(null);
paarec.category_dsp := po_tbl_varchar50(null);
paarec.attribute_category := po_tbl_varchar30(null);
paarec.attribute1 := po_tbl_varchar240(null);
paarec.attribute2 := po_tbl_varchar240(null);
paarec.attribute3 := po_tbl_varchar240(null);
paarec.attribute4 := po_tbl_varchar240(null);
paarec.attribute5 := po_tbl_varchar240(null);
paarec.attribute6 := po_tbl_varchar240(null);
paarec.attribute7 := po_tbl_varchar240(null);
paarec.attribute8 := po_tbl_varchar240(null);
paarec.attribute9 := po_tbl_varchar240(null);
paarec.attribute10:= po_tbl_varchar240(null);
paarec.attribute11:= po_tbl_varchar240(null);
paarec.attribute12:= po_tbl_varchar240(null);
paarec.attribute13:= po_tbl_varchar240(null);
paarec.attribute14:= po_tbl_varchar240(null);
paarec.attribute15:= po_tbl_varchar240(null);
paarec.request_id := po_tbl_number(null);
paarec.program_application_id := po_tbl_number(null);
paarec.program_id := po_tbl_number(null);
paarec.program_update_date:= po_tbl_date(null);
paarec.price_update_tolerance_dsp := po_tbl_number (null);
paarec.processing_lead_time_dsp := po_tbl_number (null);
paarec.min_order_qty_dsp:= po_tbl_number(10.256);
paarec.fixed_lot_multiple_dsp := po_tbl_number(85.25);
paarec.delivery_calendar_dsp:= po_tbl_varchar25(null);
paarec.country_of_origin_code_dsp := po_tbl_varchar25(null);
paarec.enable_vmi_flag_dsp:= po_tbl_varchar1(null);
paarec.vmi_min_qty_dsp:= po_tbl_number(null);
paarec.vmi_max_qty_dsp:= po_tbl_number(null);
paarec.enable_vmi_auto_replenish_flag := po_tbl_varchar1(null);
paarec.vmi_replenishment_approval := po_tbl_varchar30(null);
paarec.vmi_replenishment_approval_dsp := po_tbl_varchar30(null);
paarec.consigned_from_supp_flag_dsp := po_tbl_varchar1(null);
paarec.last_billing_date:= po_tbl_date(null);
paarec.consigned_billing_cycle_dsp:= po_tbl_number(null);
paarec.consume_on_aging_flag_dsp:= po_tbl_varchar1(null);
paarec.aging_period_dsp := po_tbl_number(null);
paarec.replenishment_method := po_tbl_number(null);
paarec.replenishment_method_dsp := po_tbl_varchar50(null);
paarec.vmi_min_days_dsp := po_tbl_number(null);
paarec.vmi_max_days_dsp := po_tbl_number(null);
paarec.fixed_order_quantity_dsp := po_tbl_number(NULL);
paarec.forecast_horizon_dsp := po_tbl_number(null);

–Populating values in padrec
padrec := new po_asl_documents_rec();
padrec.user_key := po_tbl_number(1);
padrec.process_action := po_tbl_varchar30(‘ADD’);
padrec.using_organization_id:= po_tbl_number(-1);
padrec.using_organization_dsp := po_tbl_varchar240(null);
padrec.sequence_num := po_tbl_number (1);
padrec.document_type_code := po_tbl_varchar25(null);
padrec.document_type_dsp:= po_tbl_varchar50(‘Blanket Purchase Agreement’);
padrec.document_header_id := po_tbl_number (NULL);
padrec.document_header_dsp:= po_tbl_varchar50(‘6103’);
padrec.document_line_id := po_tbl_number (null);
padrec.document_line_num_dsp:= po_tbl_number(1);
padrec.attribute_category := po_tbl_varchar30(null);
padrec.attribute1 := po_tbl_varchar240(null);
padrec.attribute2 := po_tbl_varchar240(null);
padrec.attribute3 := po_tbl_varchar240(null);
padrec.attribute4 := po_tbl_varchar240(null);
padrec.attribute5 := po_tbl_varchar240(null);
padrec.attribute6 := po_tbl_varchar240(null);
padrec.attribute7 := po_tbl_varchar240(null);
padrec.attribute8 := po_tbl_varchar240(null);
padrec.attribute9 := po_tbl_varchar240(null);
padrec.attribute10:= po_tbl_varchar240(null);
padrec.attribute11:= po_tbl_varchar240(null);
padrec.attribute12:= po_tbl_varchar240(null);
padrec.attribute13:= po_tbl_varchar240(null);
padrec.attribute14:= po_tbl_varchar240(null);
padrec.attribute15:= po_tbl_varchar240(null);
padrec.request_id := po_tbl_number(null);
padrec.program_application_id := po_tbl_number(null);
padrec.program_id := po_tbl_number(null);
padrec.program_update_date:= po_tbl_date(null);
padrec.org_id := po_tbl_number(NULL);

— Populating values in chvrec
chvrec := new chv_authorizations_rec();
chvrec.USER_KEY := po_tbl_number(1);
chvrec.PROCESS_ACTION := po_tbl_varchar30(‘ADD’);
chvrec.USING_ORGANIZATION_ID:= po_tbl_number(-1);
chvrec.USING_ORGANIZATION_DSP := po_tbl_varchar240(NULL);
chvrec.AUTHORIZATION_CODE := po_tbl_varchar25(NULL);
chvrec.AUTHORIZATION_CODE_DSP := po_tbl_varchar50(‘Raw Materials’);
chvrec.AUTHORIZATION_SEQUENCE_DSP := po_tbl_number(3);
chvrec.TIMEFENCE_DAYS_DSP := po_tbl_number(8.95);
chvrec.ATTRIBUTE_CATEGORY := po_tbl_varchar30(NULL);
chvrec.ATTRIBUTE1 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE2 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE3 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE4 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE5 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE6 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE7 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE8 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE9 := po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE10:= po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE11:= po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE12:= po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE13:= po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE14:= po_tbl_varchar240(NULL);
chvrec.ATTRIBUTE15:= po_tbl_varchar240(NULL);
chvrec.REQUEST_ID := po_tbl_number(NULL);
chvrec.PROGRAM_APPLICATION_ID := po_tbl_number(NULL);
chvrec.PROGRAM_ID := po_tbl_number(NULL);
chvrec.PROGRAM_UPDATE_DATE:= po_tbl_date(NULL);

–Populating values in psicrec
psicrec := NEW po_supplier_item_capacity_rec();
psicrec.user_key := po_tbl_number(1);
psicrec.process_action := po_tbl_varchar30(‘ADD’);
psicrec.using_organization_id:= po_tbl_number(-1);
psicrec.using_organization_dsp := po_tbl_varchar240(NULL);
psicrec.from_date_dsp:= po_tbl_date(’20-JUL-2012′);
psicrec.to_date_dsp:= po_tbl_date(’20-DEC-2012′);
psicrec.capacity_per_day_dsp := po_tbl_number(8.95);
psicrec.attribute_category := po_tbl_varchar30(null);
psicrec.attribute1 := po_tbl_varchar240(null);
psicrec.attribute2 := po_tbl_varchar240(null);
psicrec.attribute3 := po_tbl_varchar240(null);
psicrec.attribute4 := po_tbl_varchar240(null);
psicrec.attribute5 := po_tbl_varchar240(null);
psicrec.attribute6 := po_tbl_varchar240(null);
psicrec.attribute7 := po_tbl_varchar240(null);
psicrec.attribute8 := po_tbl_varchar240(null);
psicrec.attribute9 := po_tbl_varchar240(null);
psicrec.attribute10:= po_tbl_varchar240(null);
psicrec.attribute11:= po_tbl_varchar240(null);
psicrec.attribute12:= po_tbl_varchar240(null);
psicrec.attribute13:= po_tbl_varchar240(null);
psicrec.attribute14:= po_tbl_varchar240(null);
psicrec.attribute15:= po_tbl_varchar240(null);
psicrec.request_id := po_tbl_number(null);
psicrec.program_application_id := po_tbl_number(null);
psicrec.program_id := po_tbl_number(null);
psicrec.program_update_date:= po_tbl_date(NULL);

–Populating values in psitrec
psitrec := new po_supplier_item_tolerance_rec();
psitrec.USER_KEY := po_tbl_number(1);
psitrec.PROCESS_ACTION := po_tbl_varchar30(‘ADD’);
psitrec.USING_ORGANIZATION_ID:= po_tbl_number(-1);
psitrec.USING_ORGANIZATION_DSP := po_tbl_varchar240(NULL);
psitrec.NUMBER_OF_DAYS_DSP := po_tbl_number(5.62);
psitrec.TOLERANCE_DSP:= po_tbl_number(6.358);
psitrec.ATTRIBUTE_CATEGORY := po_tbl_varchar30(NULL);
psitrec.ATTRIBUTE1 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE2 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE3 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE4 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE5 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE6 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE7 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE8 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE9 := po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE10:= po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE11:= po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE12:= po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE13:= po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE14:= po_tbl_varchar240(NULL);
psitrec.ATTRIBUTE15:= po_tbl_varchar240(NULL);
psitrec.REQUEST_ID := po_tbl_number(NULL);
psitrec.PROGRAM_APPLICATION_ID := po_tbl_number(NULL);
psitrec.PROGRAM_ID := po_tbl_number(NULL);
psitrec.PROGRAM_UPDATE_DATE:= po_tbl_date(NULL);

po_asl_api_pub.create_update_asl(p_asl_rec    =>  paslrec,
p_asl_attr_rec   =>  paarec,
p_asl_doc_rec    =>  padrec,
p_chv_auth_rec   =>  chvrec,
p_capacity_rec   =>  psicrec,
p_tolerance_rec  =>  psitrec,
p_commit         =>  ‘N’,
x_session_key    =>  x_session_key,
x_return_status  =>  x_return_status,
x_return_msg     =>  x_return_msg,
x_errors         =>  x_errors
);

Dbms_Output.put_line(‘Session Key>>>>  ‘ || x_session_key);
Dbms_Output.put_line(‘Return Status>>>>’ || x_return_status);
Dbms_Output.put_line(‘Return Msg>>>>   ‘ || x_return_msg);

for i in 1 .. x_errors.user_key.Count loop
Dbms_Output.put_line(‘Session Key:  ‘ || x_errors.session_key(i) || ‘    user key:’ ||x_errors.user_key(i) || ‘   entity:’ ||x_errors.entity_name(i) || ‘   reason:’ ||x_errors.rejection_reason(i));
end loop;

for j in 1 .. paslrec.user_key.Count loop
Dbms_Output.put_line(‘user key:’ || paslrec.user_key(j) || ‘  status:’ || paslrec.process_status(j));
end loop;
END;


46. Display VARRAY in dbms_output.put_line:
CREATE OR REPLACE TYPE APPS.TEST_OBJ
IS
OBJECT
(
TEST1 NUMBER,
TEST2 VARCHAR2(500),
TEST3 VARCHAR2(500),
TEST4 VARCHAR2(500)
);

CREATE OR REPLACE TYPE APPS.TEST_ARR
IS VARRAY (500000) OF APPS.TEST_OBJ;

SET SERVEROUTPUT ON;

DECLARE
p_in_quote_header_id NUMBER;
p_out_header_details rac_qot_fws_hdr_obj;
p_out_asset_buyout_det_arr rac_qot_fws_outbound_arr;
l_asset_array rac_qot_fws_asset_arr;
l_buyout_array rac_qot_fws_buyout_arr;
l_pools_array rac_qot_fws_req_pools_arr;
l_fees_array rac_qot_fws_req_fees_arr;
l_att_array rac_qot_fws_req_att_arr;
l_ext_array rac_qot_fws_req_ext_arr;
p_out_error_message VARCHAR2(200);
p_out_return_status VARCHAR2(200);
BEGIN
p_in_quote_header_id:=23439342;–17483107;–23439342;
rac_qot_fws_int_pkg.generate_request(
p_in_quote_header_id
, p_out_header_details
, p_out_asset_buyout_det_arr
, p_out_error_message
, p_out_return_status
);
IF p_out_header_details.quote_header_id IS NULL
THEN
p_out_asset_buyout_det_arr:=rac_qot_fws_outbound_arr ();
END IF;
dbms_output.put_line(‘P_OUT_ERROR_MESSAGE=’||p_out_error_message);
dbms_output.put_line(‘P_OUT_RETURN_STATUS=’||p_out_return_status);
IF
p_out_asset_buyout_det_arr.first IS NOT NULL
AND p_out_asset_buyout_det_arr.last IS NOT NULL
THEN
dbms_output.put_line(‘**********************HEADER VALUES******************************’);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.QUOTE_HEADER_ID=’||p_out_header_details.quote_header_id);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TRANSMISSION_TYPE=’||p_out_header_details.transmission_type);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.CREDIT_APP_NUMBER=’||p_out_header_details.credit_app_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TAKE_DOWN_ID=’||p_out_header_details.take_down_id);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_PO_NUMBER=’||p_out_header_details.lease_po_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_TERM=’||p_out_header_details.lease_term);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.RATE_FACTOR=’||p_out_header_details.rate_factor);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.FUNDING_TOTAL_AMOUNT=’||p_out_header_details.funding_total_amount);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TOTAL_BUYOUT_AMOUNT=’||p_out_header_details.total_buyout_amount);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ADVANCE_PAYMENTS=’||p_out_header_details.advance_payments);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ADVANCE_PAYMENT_AMNT=’||p_out_header_details.advance_payment_amnt);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_PAYMENT_EQUIPMENT=’||p_out_header_details.lease_payment_equipment);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.PLAN=’||p_out_header_details.plan);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TAX_EXEMPT=’||p_out_header_details.tax_exempt);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.NON_STD_ESC_IND=’||p_out_header_details.non_std_esc_ind);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ANN_ESC_MAINTENANCE=’||p_out_header_details.ann_esc_maintenance);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ANN_ESC_OVERAGE=’||p_out_header_details.ann_esc_overage);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ORDER_BRANCH_NUMBER=’||p_out_header_details.order_branch_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SALES_REP_NAME=’||p_out_header_details.sales_rep_name);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SALES_REP_EMAIL=’||p_out_header_details.sales_rep_email);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SUPPLEMENT_INDICATOR=’||p_out_header_details.supplement_indicator);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ADD_ON_PARENT_NUMBER=’||p_out_header_details.add_on_parent_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TRANSITION_BILLING_IND=’||p_out_header_details.transition_billing_ind);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.CUSTOMER_PO=’||p_out_header_details.customer_po);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TRANSITION_BILLING_EQ_AMNT=’||p_out_header_details.transition_billing_eq_amnt);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.TRANSITION_BILLING_MAINT_AMNT=’||p_out_header_details.transition_billing_maint_amnt);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.MAINT_FREQUENCY=’||p_out_header_details.maint_frequency);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_BILLING_FREQUENCY=’||p_out_header_details.lease_billing_frequency);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILL_FREQUENCY_METER=’||p_out_header_details.bill_frequency_meter);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BUNDLED_INDICATOR=’||p_out_header_details.bundled_indicator);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.VENDOR_BILL_OVERAGE=’||p_out_header_details.vendor_bill_overage);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.NOTES=’||p_out_header_details.notes);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.PO_EXPIRY_DATE=’||p_out_header_details.po_expiry_date);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_NUMBER=’||p_out_header_details.lease_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_COMMENCEMENT_DATE=’||p_out_header_details.lease_commencement_date);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.LEASE_FUNDING_DATE=’||p_out_header_details.lease_funding_date);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.CUSTOMER_INVOICE_DUE_DATE=’||p_out_header_details.customer_invoice_due_date);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.FIRST_METER_READ_DD=’||p_out_header_details.first_meter_read_dd);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CUSTOMER_NAME=’||p_out_header_details.soldto_customer_name);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_DUNS_NUMBER=’||p_out_header_details.soldto_duns_number);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_DBA=’||p_out_header_details.soldto_dba);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_FEDTAXID=’||p_out_header_details.soldto_fedtaxid);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CONTACT_NAME=’||p_out_header_details.soldto_contact_name);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CONTACT_EMAIL=’||p_out_header_details.soldto_contact_email);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CONTACT_PHONE=’||p_out_header_details.soldto_contact_phone);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CONTACT_FAX=’||p_out_header_details.soldto_contact_fax);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_ADDRESS_LINE1=’||p_out_header_details.soldto_address_line1);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_STREET=’||p_out_header_details.soldto_street);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_CIY=’||p_out_header_details.soldto_ciy);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_STATE=’||p_out_header_details.soldto_state);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.SOLDTO_ZIP_CODE=’||p_out_header_details.soldto_zip_code);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_BILLING_NAME=’||p_out_header_details.billto_billing_name);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_DBA=’||p_out_header_details.billto_dba);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_CONTACT_NAME=’||p_out_header_details.billto_contact_name);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_CONTACT_EMAIL=’||p_out_header_details.billto_contact_email);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_CONTACT_PHONE=’||p_out_header_details.billto_contact_phone);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_CONTACT_FAX=’||p_out_header_details.billto_contact_fax);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_ADDRESS_LINE1=’||p_out_header_details.billto_address_line1);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_ADDRESS_LINE2=’||p_out_header_details.billto_address_line2);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_CITY=’||p_out_header_details.billto_city);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_STATE=’||p_out_header_details.billto_state);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.BILLTO_ZIP_CODE=’||p_out_header_details.billto_zip_code);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE1=’||p_out_header_details.attribute1);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE2=’||p_out_header_details.attribute2);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE3=’||p_out_header_details.attribute3);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE4=’||p_out_header_details.attribute4);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE5=’||p_out_header_details.attribute5);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE6=’||p_out_header_details.attribute6);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE7=’||p_out_header_details.attribute7);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE8=’||p_out_header_details.attribute8);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE9=’||p_out_header_details.attribute9);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE10=’||p_out_header_details.attribute10);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE11=’||p_out_header_details.attribute11);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE12=’||p_out_header_details.attribute12);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE13=’||p_out_header_details.attribute13);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE14=’||p_out_header_details.attribute14);
dbms_output.put_line(‘P_OUT_HEADER_DETAILS.ATTRIBUTE15=’||p_out_header_details.attribute15);
FOR icounterlines IN p_out_asset_buyout_det_arr.first..p_out_asset_buyout_det_arr.last LOOP
dbms_output.put_line(‘iCounterLines-‘||icounterlines);
l_asset_array:=p_out_asset_buyout_det_arr(icounterlines).assetarray;
l_buyout_array:=p_out_asset_buyout_det_arr(icounterlines).buyoutarray;
l_pools_array:=p_out_asset_buyout_det_arr(icounterlines).poolsarray;
l_fees_array:=p_out_asset_buyout_det_arr(icounterlines).feesarray;
l_att_array:=p_out_asset_buyout_det_arr(icounterlines).attarray;
l_ext_array:=p_out_asset_buyout_det_arr(icounterlines).extarray;
dbms_output.put_line(‘**********************Asset VALUES******************************’);
FOR icounterarray IN l_asset_array.first..l_asset_array.last LOOP
dbms_output.put_line(‘<<<<<<<<<<<<<<iCounterArray-‘||icounterarray);
dbms_output.put_line(‘L_ASSET_ARRAY.QUOTE_HEADER_ID=’ ||l_asset_array(icounterarray).quote_header_id);
dbms_output.put_line(‘L_ASSET_ARRAY.LEASE_ORDER_NUMBER=’ ||l_asset_array(icounterarray).lease_order_number);
dbms_output.put_line(‘L_ASSET_ARRAY.ST_CUSTOMER_NAME=’ ||l_asset_array(icounterarray).st_customer_name);
dbms_output.put_line(‘L_ASSET_ARRAY.SHIPTO_CONTACT_NAME=’ ||l_asset_array(icounterarray).shipto_contact_name);
dbms_output.put_line(‘L_ASSET_ARRAY.SHIPTO_CONTACT_EMAIL=’ ||l_asset_array(icounterarray).shipto_contact_email);
dbms_output.put_line(‘L_ASSET_ARRAY.PHONE=’ ||l_asset_array(icounterarray).phone);
dbms_output.put_line(‘L_ASSET_ARRAY.ADDRESS_LINE_1=’ ||l_asset_array(icounterarray).address_line_1);
dbms_output.put_line(‘L_ASSET_ARRAY.ADDRESS_LINE_2=’ ||l_asset_array(icounterarray).address_line_2);
dbms_output.put_line(‘L_ASSET_ARRAY.CITY=’ ||l_asset_array(icounterarray).city);
dbms_output.put_line(‘L_ASSET_ARRAY.STATE=’ ||l_asset_array(icounterarray).state);
dbms_output.put_line(‘L_ASSET_ARRAY.ZIP=’ ||l_asset_array(icounterarray).zip);
dbms_output.put_line(‘L_ASSET_ARRAY.ASSET_AMOUNT=’ ||l_asset_array(icounterarray).asset_amount);
dbms_output.put_line(‘L_ASSET_ARRAY.MAKE=’ ||l_asset_array(icounterarray).make);
dbms_output.put_line(‘L_ASSET_ARRAY.MODEL=’ ||l_asset_array(icounterarray).model);
dbms_output.put_line(‘L_ASSET_ARRAY.ORD_MNF_SUGG_RET_PRICE=’ ||l_asset_array(icounterarray).ord_mnf_sugg_ret_price);
dbms_output.put_line(‘L_ASSET_ARRAY.INSTALLATION_DATE=’ ||l_asset_array(icounterarray).installation_date);
dbms_output.put_line(‘L_ASSET_ARRAY.CONDITION=’ ||l_asset_array(icounterarray).condition);
dbms_output.put_line(‘L_ASSET_ARRAY.SVC_INDICATOR=’ ||l_asset_array(icounterarray).svc_indicator);
dbms_output.put_line(‘L_ASSET_ARRAY.ASSET_INV_INFO=’ ||l_asset_array(icounterarray).asset_inv_info);
dbms_output.put_line(‘L_ASSET_ARRAY.BLACK_MAINT_AMNT=’ ||l_asset_array(icounterarray).black_maint_amnt);
dbms_output.put_line(‘L_ASSET_ARRAY.BLACK_VOLUME=’ ||l_asset_array(icounterarray).black_volume);
dbms_output.put_line(‘L_ASSET_ARRAY.BLACK_START_METER=’ ||l_asset_array(icounterarray).black_start_meter);
dbms_output.put_line(‘L_ASSET_ARRAY.COLOR_MAINT_AMNT=’ ||l_asset_array(icounterarray).color_maint_amnt);
dbms_output.put_line(‘L_ASSET_ARRAY.COLOR_VOLUME=’ ||l_asset_array(icounterarray).color_volume);
dbms_output.put_line(‘L_ASSET_ARRAY.COLOR_START_METER=’ ||l_asset_array(icounterarray).color_start_meter);
dbms_output.put_line(‘L_ASSET_ARRAY.POOL_NAME_BW=’ ||l_asset_array(icounterarray).pool_name_bw);
dbms_output.put_line(‘L_ASSET_ARRAY.POOL_NAME_CLR=’ ||l_asset_array(icounterarray).pool_name_clr);
dbms_output.put_line(‘L_ASSET_ARRAY.SERVICING_BRANCH=’ ||l_asset_array(icounterarray).servicing_branch);
dbms_output.put_line(‘L_ASSET_ARRAY.MAIN_MACHINE_SERIAL=’ ||l_asset_array(icounterarray).main_machine_serial);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_DESCRIPTION=’ ||l_asset_array(icounterarray).equip_description);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_TYPE=’ ||l_asset_array(icounterarray).equip_type);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_COST=’ ||l_asset_array(icounterarray).equip_cost);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_SOFT_COST=’ ||l_asset_array(icounterarray).equip_soft_cost);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIPMENT_BUYOUT_AMT=’ ||l_asset_array(icounterarray).equipment_buyout_amt);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_PAYMENT_AMT=’ ||l_asset_array(icounterarray).equip_payment_amt);
dbms_output.put_line(‘L_ASSET_ARRAY.EQUIP_TRADE_IN_AMT=’ ||l_asset_array(icounterarray).equip_trade_in_amt);
dbms_output.put_line(‘L_ASSET_ARRAY.SERIAL=’ ||l_asset_array(icounterarray).serial);
dbms_output.put_line(‘L_ASSET_ARRAY.FUNDING_AMT=’ ||l_asset_array(icounterarray).funding_amt);
dbms_output.put_line(‘L_ASSET_ARRAY.ITEM_MODEL=’ ||l_asset_array(icounterarray).item_model);
dbms_output.put_line(‘L_ASSET_ARRAY.INVOICE_NUMBER=’ ||l_asset_array(icounterarray).invoice_number);
dbms_output.put_line(‘L_ASSET_ARRAY.INVOICE_DATE=’ ||l_asset_array(icounterarray).invoice_date);
dbms_output.put_line(‘L_ASSET_ARRAY.PART_NUMBER=’ ||l_asset_array(icounterarray).part_number);
dbms_output.put_line(‘L_ASSET_ARRAY.ITM_MNF_SUGG_RET_PRICE=’ ||l_asset_array(icounterarray).itm_mnf_sugg_ret_price);
dbms_output.put_line(‘L_ASSET_ARRAY.ITEM_DESC=’ ||l_asset_array(icounterarray).item_desc);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE1=’ ||l_asset_array(icounterarray).attribute1);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE2=’ ||l_asset_array(icounterarray).attribute2);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE3=’ ||l_asset_array(icounterarray).attribute3);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE4=’ ||l_asset_array(icounterarray).attribute4);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE5=’ ||l_asset_array(icounterarray).attribute5);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE6=’ ||l_asset_array(icounterarray).attribute6);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE7=’ ||l_asset_array(icounterarray).attribute7);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE8=’ ||l_asset_array(icounterarray).attribute8);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE9=’ ||l_asset_array(icounterarray).attribute9);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE10=’ ||l_asset_array(icounterarray).attribute10);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE11=’ ||l_asset_array(icounterarray).attribute11);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE12=’ ||l_asset_array(icounterarray).attribute12);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE13=’ ||l_asset_array(icounterarray).attribute13);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE14=’ ||l_asset_array(icounterarray).attribute14);
dbms_output.put_line(‘L_ASSET_ARRAY.ATTRIBUTE15=’ ||l_asset_array(icounterarray).attribute15);
END LOOP;

dbms_output.put_line(‘**********************Buyout VALUES******************************’);
FOR icounterbuyout IN l_buyout_array.first..l_buyout_array.last LOOP
dbms_output.put_line(‘>>>>>>>>>>>>>>>>>>iCounterBuyout-‘||icounterbuyout);
dbms_output.put_line(‘L_BUYOUT_ARRAY.QUOTE_HEADER_ID=’ ||l_buyout_array(icounterbuyout).quote_header_id);
dbms_output.put_line(‘L_BUYOUT_ARRAY.LEASE_ACCOUNT_NUMBER=’ ||l_buyout_array(icounterbuyout).lease_account_number);
dbms_output.put_line(‘L_BUYOUT_ARRAY.FUNDED_BUYOUT_AMOUNT=’ ||l_buyout_array(icounterbuyout).funded_buyout_amount);
dbms_output.put_line(‘L_BUYOUT_ARRAY.BUYOUT_EXPIRY_DATE=’ ||l_buyout_array(icounterbuyout).buyout_expiry_date);
dbms_output.put_line(‘L_BUYOUT_ARRAY.PAYEE=’ ||l_buyout_array(icounterbuyout).payee);
dbms_output.put_line(‘L_BUYOUT_ARRAY.QUOTE_TYPE=’ ||l_buyout_array(icounterbuyout).quote_type);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE1=’ ||l_buyout_array(icounterbuyout).attribute1);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE2=’ ||l_buyout_array(icounterbuyout).attribute2);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE3=’ ||l_buyout_array(icounterbuyout).attribute3);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE4=’ ||l_buyout_array(icounterbuyout).attribute4);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE5=’ ||l_buyout_array(icounterbuyout).attribute5);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE6=’ ||l_buyout_array(icounterbuyout).attribute6);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE7=’ ||l_buyout_array(icounterbuyout).attribute7);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE8=’ ||l_buyout_array(icounterbuyout).attribute8);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE9=’ ||l_buyout_array(icounterbuyout).attribute9);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE10=’ ||l_buyout_array(icounterbuyout).attribute10);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE11=’ ||l_buyout_array(icounterbuyout).attribute11);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE12=’ ||l_buyout_array(icounterbuyout).attribute12);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE13=’ ||l_buyout_array(icounterbuyout).attribute13);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE14=’ ||l_buyout_array(icounterbuyout).attribute14);
dbms_output.put_line(‘L_BUYOUT_ARRAY.ATTRIBUTE15=’ ||l_buyout_array(icounterbuyout).attribute15);
END LOOP;

dbms_output.put_line(‘**********************Pools VALUES******************************’);
FOR icounterbuyout IN l_pools_array.first..l_pools_array.last LOOP
dbms_output.put_line(‘>>>>>>>>>>>>>>>>>>iCounterPools-‘||icounterbuyout);
dbms_output.put_line(‘l_pools_array.QUOTE_HEADER_ID=’ ||l_pools_array(icounterbuyout).quote_header_id);
dbms_output.put_line(‘l_pools_array.NAME=’ ||l_pools_array(icounterbuyout).name);
dbms_output.put_line(‘l_pools_array.TYPE=’ ||l_pools_array(icounterbuyout).type);
dbms_output.put_line(‘l_pools_array.VOLUME=’ ||l_pools_array(icounterbuyout).volume);
dbms_output.put_line(‘l_pools_array.MAINT_AMNT=’ ||l_pools_array(icounterbuyout).maint_amnt);
dbms_output.put_line(‘l_pools_array.EXC_COPY_RATE=’ ||l_pools_array(icounterbuyout).exc_copy_rate);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE1=’ ||l_pools_array(icounterbuyout).attribute1);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE2=’ ||l_pools_array(icounterbuyout).attribute2);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE3=’ ||l_pools_array(icounterbuyout).attribute3);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE4=’ ||l_pools_array(icounterbuyout).attribute4);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE5=’ ||l_pools_array(icounterbuyout).attribute5);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE6=’ ||l_pools_array(icounterbuyout).attribute6);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE7=’ ||l_pools_array(icounterbuyout).attribute7);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE8=’ ||l_pools_array(icounterbuyout).attribute8);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE9=’ ||l_pools_array(icounterbuyout).attribute9);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE10=’ ||l_pools_array(icounterbuyout).attribute10);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE11=’ ||l_pools_array(icounterbuyout).attribute11);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE12=’ ||l_pools_array(icounterbuyout).attribute12);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE13=’ ||l_pools_array(icounterbuyout).attribute13);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE14=’ ||l_pools_array(icounterbuyout).attribute14);
dbms_output.put_line(‘l_pools_array.ATTRIBUTE15=’ ||l_pools_array(icounterbuyout).attribute15);
END LOOP;

dbms_output.put_line(‘**********************Fees VALUES******************************’);
FOR icounterbuyout IN l_fees_array.first..l_fees_array.last LOOP
dbms_output.put_line(‘>>>>>>>>>>>>>>>>>>iCounterFees-‘||icounterbuyout);
dbms_output.put_line(‘l_fees_array.QUOTE_HEADER_ID=’ ||l_fees_array(icounterbuyout).quote_header_id);
dbms_output.put_line(‘l_fees_array.TYPE=’ ||l_fees_array(icounterbuyout).type);
dbms_output.put_line(‘l_fees_array.AMOUNT=’ ||l_fees_array(icounterbuyout).amount);
dbms_output.put_line(‘l_fees_array.FREQUENCY=’ ||l_fees_array(icounterbuyout).frequency);
dbms_output.put_line(‘l_fees_array.INC_IN_RENT=’ ||l_fees_array(icounterbuyout).inc_in_rent);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE1=’ ||l_fees_array(icounterbuyout).attribute1);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE2=’ ||l_fees_array(icounterbuyout).attribute2);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE3=’ ||l_fees_array(icounterbuyout).attribute3);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE4=’ ||l_fees_array(icounterbuyout).attribute4);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE5=’ ||l_fees_array(icounterbuyout).attribute5);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE6=’ ||l_fees_array(icounterbuyout).attribute6);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE7=’ ||l_fees_array(icounterbuyout).attribute7);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE8=’ ||l_fees_array(icounterbuyout).attribute8);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE9=’ ||l_fees_array(icounterbuyout).attribute9);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE10=’ ||l_fees_array(icounterbuyout).attribute10);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE11=’ ||l_fees_array(icounterbuyout).attribute11);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE12=’ ||l_fees_array(icounterbuyout).attribute12);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE13=’ ||l_fees_array(icounterbuyout).attribute13);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE14=’ ||l_fees_array(icounterbuyout).attribute14);
dbms_output.put_line(‘l_fees_array.ATTRIBUTE15=’ ||l_fees_array(icounterbuyout).attribute15);
END LOOP;

dbms_output.put_line(‘**********************Attachments VALUES******************************’);
FOR icounterbuyout IN l_att_array.first..l_att_array.last LOOP
dbms_output.put_line(‘>>>>>>>>>>>>>>>>>>iCounterAttachment-‘||icounterbuyout);
dbms_output.put_line(‘l_att_array.QUOTE_HEADER_ID=’ ||l_att_array(icounterbuyout).quote_header_id);
dbms_output.put_line(‘l_att_array.NAME=’ ||l_att_array(icounterbuyout).name);
dbms_output.put_line(‘l_att_array.TYPE=’ ||l_att_array(icounterbuyout).type);
dbms_output.put_line(‘l_att_array.ID=’ ||l_att_array(icounterbuyout).id);
dbms_output.put_line(‘l_att_array.ATTRIBUTE1=’ ||l_att_array(icounterbuyout).attribute1);
dbms_output.put_line(‘l_att_array.ATTRIBUTE2=’ ||l_att_array(icounterbuyout).attribute2);
dbms_output.put_line(‘l_att_array.ATTRIBUTE3=’ ||l_att_array(icounterbuyout).attribute3);
dbms_output.put_line(‘l_att_array.ATTRIBUTE4=’ ||l_att_array(icounterbuyout).attribute4);
dbms_output.put_line(‘l_att_array.ATTRIBUTE5=’ ||l_att_array(icounterbuyout).attribute5);
dbms_output.put_line(‘l_att_array.ATTRIBUTE6=’ ||l_att_array(icounterbuyout).attribute6);
dbms_output.put_line(‘l_att_array.ATTRIBUTE7=’ ||l_att_array(icounterbuyout).attribute7);
dbms_output.put_line(‘l_att_array.ATTRIBUTE8=’ ||l_att_array(icounterbuyout).attribute8);
dbms_output.put_line(‘l_att_array.ATTRIBUTE9=’ ||l_att_array(icounterbuyout).attribute9);
dbms_output.put_line(‘l_att_array.ATTRIBUTE10=’ ||l_att_array(icounterbuyout).attribute10);
dbms_output.put_line(‘l_att_array.ATTRIBUTE11=’ ||l_att_array(icounterbuyout).attribute11);
dbms_output.put_line(‘l_att_array.ATTRIBUTE12=’ ||l_att_array(icounterbuyout).attribute12);
dbms_output.put_line(‘l_att_array.ATTRIBUTE13=’ ||l_att_array(icounterbuyout).attribute13);
dbms_output.put_line(‘l_att_array.ATTRIBUTE14=’ ||l_att_array(icounterbuyout).attribute14);
dbms_output.put_line(‘l_att_array.ATTRIBUTE15=’ ||l_att_array(icounterbuyout).attribute15);
END LOOP;

dbms_output.put_line(‘**********************Extensions VALUES******************************’);
FOR icounterbuyout IN l_ext_array.first..l_ext_array.last LOOP
dbms_output.put_line(‘>>>>>>>>>>>>>>>>>>iCounterExtensions-‘||icounterbuyout);
dbms_output.put_line(‘l_ext_array.QUOTE_HEADER_ID=’ ||l_ext_array(icounterbuyout).quote_header_id);
dbms_output.put_line(‘l_ext_array.NAME=’ ||l_ext_array(icounterbuyout).name);
dbms_output.put_line(‘l_ext_array.VALUE=’ ||l_ext_array(icounterbuyout).value);
dbms_output.put_line(‘l_ext_array.FIELD_TYPE=’ ||l_ext_array(icounterbuyout).field_type);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE1=’ ||l_ext_array(icounterbuyout).attribute1);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE2=’ ||l_ext_array(icounterbuyout).attribute2);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE3=’ ||l_ext_array(icounterbuyout).attribute3);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE4=’ ||l_ext_array(icounterbuyout).attribute4);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE5=’ ||l_ext_array(icounterbuyout).attribute5);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE6=’ ||l_ext_array(icounterbuyout).attribute6);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE7=’ ||l_ext_array(icounterbuyout).attribute7);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE8=’ ||l_ext_array(icounterbuyout).attribute8);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE9=’ ||l_ext_array(icounterbuyout).attribute9);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE10=’ ||l_ext_array(icounterbuyout).attribute10);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE11=’ ||l_ext_array(icounterbuyout).attribute11);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE12=’ ||l_ext_array(icounterbuyout).attribute12);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE13=’ ||l_ext_array(icounterbuyout).attribute13);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE14=’ ||l_ext_array(icounterbuyout).attribute14);
dbms_output.put_line(‘l_ext_array.ATTRIBUTE15=’ ||l_ext_array(icounterbuyout).attribute15);
END LOOP;

END LOOP;

END IF;

END;


47. Regular Expressions

  • REGEXP_LIKE
  • REGXP_REPLACE
  • REGEXP_INSTR
  • REGEXP_SUBSTR

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE(object_type, ‘TAB’); — same as object_type LIKE ‘%TAB%’
Here LIKE and REGEXP_LIKE returned same number of records. We can find importance of REGEXP while using meta characters like
* → Zero or more — Matches zero or more occurrences of the preceding sub-expression
{m} → Exact count — Matches exactly m occurrences of the preceding sub-expression
{m, } → Lease count — Matches at least m occurrences of the preceding sub-expression
{m, n} → Between count — Matches at least m, but not more than n occurrences of the preceding sub-expression
[…] → Matching character list — Matches any single character in the list within the brackets
[^…] → Non-matching character list — Matches any single character which is not in the list within the brackets. Matches starting characters mentioned in the list within brackets.
| → OR — Matches either one of the alternatives
(…) → Grouping — Treats the expression within parentheses as a unit.

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE(OBJECT_TYPE, ‘TAB|DURE’) — same as object_type LIKE ‘%TAB%’ or object_type LIKE ‘%DURE%’. REGEXP improves performance
ORDER BY 2;

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘TAB|Dure’, ‘c’)–Here ‘c’ is case sensitive so we dont see any PROCEDURE records and default is case sensitive
ORDER BY 2;

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘TAB|Dure’, ‘i’)–Here ‘i’ is case in-sensitive so we see PROCEDURE records as well
ORDER BY 2;

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^PRO’) –Matches first characters (only characters after ^ – Here its PRO). We get all records with object_type as PROCEDURE
ORDER BY 2;

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘PRO(*)’) –After PRO any number of characters. Understand that PRO need to be in the beginning. For example we have object_type like ABCPRO or ABPROCD, even these records will be picked.
ORDER BY 2;

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^Pro(*)’, ‘i’) –First characters should be PRO and after that any number of characters with case in-sensitive. Here ABCPRO and ABPROCD wont be picked
ORDER BY 2;

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^P(…)’, ‘i’); –First character should be P and each decimal represents one character
Output is: PACKAGE, PACKAGE BODY, PROCEDURE, PROGRAM

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^P(……)’, ‘i’); –First character should be P and each decimal represents one character. Here I gave 6 decimals.
Output is: PACKAGE, PACKAGE BODY, PROCEDURE, PROGRAM

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^P(…….)’, ‘i’); –First character should be P and each decimal represents one character. Here I gave 7 decimals.
Output is: PACKAGE BODY, PROCEDURE

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘[S]{2}’) — Exactly two ‘S’ should be there without any spaces…means side by side
ORDER BY 1;
Output: JAVA CLASS, JOB CLASS

SELECT object_name, object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^Ste(v|ph)en$’)
ORDER BY 1;
Output: Returns Steven or Stephen…here ^ represents starting,  | represents OR, $ represents ending

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘(*)E$’) — Ending should be ‘E’, starting can be anything
ORDER BY 1;
Output: PACKAGE, PROCEDURE, SEQUENCE, TABLE, TYPE

SELECT DISTINCT object_type
FROM dba_objects
WHERE 1=1
AND REGEXP_LIKE (object_type, ‘^P(*)E$’) — Starting should be ‘P’, ending should be ‘E’
ORDER BY 1;
Output: No rows returned since few meta characters combination will not work. Here its ^ and *

REGEXP_COUNT = LENGTH(REPLACE)
SELECT ‘123123123123123’ string,
REGEXP_COUNT (‘123123123123123’, ‘(12)3’, 1, ‘i’) REGEXP_COUNT — pattern is 12 and 3 starting from first position
FROM dual;

SELECT ‘123123123123123’ string,
REGEXP_COUNT (‘123123123123123’, ‘(12)3’, 1, ‘i’) REGEXP_COUNT — pattern is 12 and 3 starting from first position
FROM dual;

SELECT ‘123123123123123’ string,
REGEXP_COUNT (‘123123123123123’, ‘(12)3’, 5, ‘i’) REGEXP_COUNT — pattern is 12 and 3 starting from 5th position
FROM dual;

SELECT ‘This is to test REGEXP_COUNT’ string,
REGEXP_COUNT (‘This is to test REGEXP_COUNT’, ‘t’, 1) REGEXP_COUNT — find number of ‘t’s starting from first position
FROM dual;
Output: 3

SELECT ‘This is to test REGEXP_COUNT’ string,
REGEXP_COUNT (‘This is to test REGEXP_COUNT’, ‘t’, 1, ‘i’) REGEXP_COUNT — find number of ‘t’s starting from first position with case insensitive
FROM dual;
Output: 5

SELECT ‘This is to test REGEXP_COUNT’ string,
REGEXP_COUNT (‘This is to test REGEXP_COUNT’, ‘t’, 2, ‘i’) REGEXP_COUNT — find number of ‘t’s starting from second position with case insensitive
FROM dual;
Output: 4

SELECT ‘This is to test REGEXP_COUNT’ string,
REGEXP_COUNT (‘This is to test To REGEXP_COUNT’, ‘to’, 2, ‘i’) REGEXP_COUNT — find number of ‘to’s starting from second position with case insensitive
FROM dual;
Output: 2

REGEXP_SUBSTR
SELECT ‘This is, to test, to, REGEXP_COUNT’ string,
REGEXP_SUBSTR (‘This is, to test, to, REGEXP_COUNT’, ‘,[^,]+,’) REGEXP_SUBSTR — ,[^,]+ here , represents start data from first comma; ^ represents from first character starting data from comma , till another comma is found; + after that any number of characters
FROM dual;
Output: , to test,

SELECT ‘http://www.ebsguide.com/test’ string,
REGEXP_SUBSTR (‘http://www.ebsguide.com/test’, ‘http://([[:alnum:]]+\.?){3,4}/?’) REGEXP_SUBSTR –starting from http:// and having any numbers alphanumeric characters, starting from first dot and after that cut till 3 to 4 charaters
FROM dual;
Output: http://www.ebsguide.com/

SELECT ‘This is to test to REGEXP_COUNT’ string,
REGEXP_SUBSTR (‘This is to test to REGEXP_COUNT’, ‘(\T*)(\t)’, 1, 1) REGEXP_SUBSTR
FROM dual;

SELECT ‘This is to test numbers 4 and 6 in REGEXP_COUNT’ string,
REGEXP_SUBSTR (‘This is to test numbers 4 and 6 in REGEXP_COUNT’, ‘\d’) REGEXP_SUBSTR — Returns first number
FROM dual;
Output: 4

SELECT ‘This is to test numbers 4 and 6 in REGEXP_COUNT’ string,
REGEXP_SUBSTR (‘This is to test numbers 4 and 6 in REGEXP_COUNT’, ‘\d’, 1, 2) REGEXP_SUBSTR — Returns number from first position to second occurance
FROM dual;
Output: 6

SELECT ‘This is to tester to REGEXP_COUNT’ string,
REGEXP_SUBSTR (‘This is to tester to REGEXP_COUNT’, ‘te[^ ]er’) REGEXP_SUBSTR
FROM dual;
Output: tester

REGEXP_INSTR
SELECT ‘This is to tester to REGEXP_INSTR’ string,
REGEXP_INSTR (‘This is to tester to REGEXP_INSTR’, ‘[^ ]+’, 1, 2) REGEXP_INSTR –Gives count of space from 1st position till second occurrence
FROM dual;

REGEXP_REPLACE
SELECT ‘1234567890’ string,
REGEXP_REPLACE (‘123.456.7890’, ‘([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})\.’, ‘(\1) \2-\3’) REGEXP_REPLACE –formatting phone number
FROM dual;


48. PLSQL Wrapper
wrap iname = input_file_name [oname=output_file_name]
wrap is external utility from Oracle corp. like SQL * Loader

CREATE OR REPLACE PROCEDURE get_emp (p_empno emp.empno%TYPE)
AS
lv_emprec empno%ROWTYPE;
BEGIN
SELECT * INTO lv_emprec FROM emp WHERE empno = p_empno;
dbms_output.put_line(‘Employee number: ‘||p_empno);
dbms_output.put_line(‘Employee name: ‘||lv_emprec.ename);
dbms_output.put_line(‘Employee job: ‘||lv_emprec.job);
dbms_output.put_line(‘Employee salary: ‘||lv_emprec.sal);
END get_emp;

Save the above procedure in one file on some location…say D:\plsqlwrapper\get_emp.sql
wrap iname = D:\plsqlwrapper\get_emp.sql; — We get error by executing this snytax
So move to BIN folder where Oracle has been installed. From command prompt change directory as below
c:\users\gdsrikanth>cd D:\oraserver\app\…..\product\11.2.0\dbhome_1\BIN
c:\users\gdsrikanth> D:
By executing above syntax we can check if we are in correct path or not (should be as below path).
D:\oraserver\app\…..\product\11.2.0\dbhome_1\BIN>D:\plsqlwrapper\get_emp.sql oname=D:\plsqlwrapper\get_emp_out.plb

Now open .plb file which was created. Copy the code and compile in SQL or PL/SQL developer. We get message as ‘Procedure Created’.
Here .plb = plsql library


49. Package to send output of Standard Concurrent program via Email

CREATE OR REPLACE PACKAGE BODY xxaa_po_output_email AS
/************************************************************************
* TYPE : Package *
* NAME : xxaa_po_output_email *
* INPUT Parameters : p_from_address, p_to_address *
* OUTPUT Parametrs : p_errbuf, p_retcode *
* PURPOSE : PO Output for Communication *
************************************************************************/
——————————————
— * Procedure Name : xxaa_po_output
— * Description : This Procedure fetches approved POs
— * and sends PDF output through email to
— * supplier site email address
——————————————
PROCEDURE xxaa_po_output (
p_errbuf OUT NOCOPY VARCHAR2
,p_retcode OUT NOCOPY VARCHAR2
,p_from_address IN VARCHAR2
,p_to_address IN VARCHAR2
) IS
l_request_id NUMBER;
l_bol_delivery BOOLEAN;
l_last_run_date DATE;
l_curr_run_date DATE;
l_creation_date DATE;
ln_responsibility_id NUMBER;
ln_application_id NUMBER;
lv_db_name VARCHAR2(50);
l_to_address VARCHAR2(200);
l_rowcount NUMBER;
lv_resp_name VARCHAR2(100);
ln_org_id NUMBER;
lv_org_name VARCHAR2(100);
ln_resp_id NUMBER;
BEGIN
lv_org_name := upper(fnd_profile.value(‘ORG_NAME’) );
IF
lv_org_name = ‘ABC’
THEN
lv_resp_name := fnd_profile.value(‘RESP_NAME’);
ln_resp_id := fnd_profile.value(‘RESP_ID’);
ln_org_id := fnd_profile.value(‘ORG_ID’);
l_to_address := p_to_address;
ln_responsibility_id := NULL;
ln_application_id := NULL;
BEGIN
SELECT frt.responsibility_id
,frt.application_id INTO
ln_responsibility_id,ln_application_id
FROM fnd_responsibility_tl frt
WHERE 1 = 1
AND frt.responsibility_name = lv_resp_name;
EXCEPTION
WHEN OTHERS THEN
ln_responsibility_id := NULL;
ln_application_id := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Responsibility ID and Application ID: ‘ || sqlerrm
);
END;
fnd_global.apps_initialize(
user_id => fnd_profile.value(‘USER_ID’)
,resp_id => ln_responsibility_id
,resp_appl_id => ln_application_id
);
/*Query to fetch concurrent program last run date*/
BEGIN
SELECT actual_completion_date INTO
l_last_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = ‘ABC PO Output for Communication’
AND fcr1.status_code = ‘C’
);
EXCEPTION
WHEN OTHERS THEN
l_last_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program last run date: ‘ || sqlerrm
);
END;
/*Query to fetch concurrent program current run date*/
BEGIN
SELECT actual_start_date INTO
l_curr_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = ‘ABC PO Output for Communication’
AND fcr1.status_code = ‘R’
AND fcr1.phase_code = ‘R’
);
EXCEPTION
WHEN OTHERS THEN
l_curr_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program current run date: ‘ || sqlerrm
);
END;
BEGIN
SELECT name INTO
lv_db_name
FROM v$database;
EXCEPTION
WHEN OTHERS THEN
lv_db_name := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Database Name: ‘ || sqlerrm
);
END;
BEGIN
SELECT creation_date INTO
l_creation_date
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name = ‘ABC PO Output for Communication’;
EXCEPTION
WHEN OTHERS THEN
l_creation_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program creation date: ‘ || sqlerrm
);
END;
/* IF
lv_db_name <> ‘TEST’ AND ( l_to_address IS NULL OR p_from_address IS NULL )
THEN
p_retcode := 1;
apps.fnd_file.put_line(apps.fnd_file.log,’Please enter valid parameters for NON-PROD instances’);
ELSE*/
FOR i IN ( SELECT a.segment1
,b.email_address
,c.vendor_name
,ROWNUM
FROM po_headers_all a
,ap_supplier_sites_all b
,ap_suppliers c
WHERE 1 = 1
AND a.vendor_site_id = b.vendor_site_id
AND c.vendor_id = b.vendor_id
AND b.supplier_notif_method = ‘PRINT’
AND a.authorization_status = ‘APPROVED’
AND a.type_lookup_code = ‘STANDARD’
AND a.org_id = ln_org_id
AND a.last_update_date BETWEEN ( nvl(
l_last_run_date
,l_creation_date
) ) AND ( l_curr_run_date )
ORDER BY a.segment1 ) LOOP
l_rowcount := i.rownum;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Fetching PO: ‘ || i.segment1 || ‘ for Supplier: ‘ || i.vendor_name || ‘ having email ‘ || i.email_address
);
IF
lv_db_name = ‘PROD’
THEN
l_to_address := i.email_address;
ELSIF lv_db_name != ‘PROD’ AND upper(
i.email_address
) LIKE upper(
‘%@test.com’
) THEN
l_to_address := i.email_address;
ELSIF upper(p_to_address) LIKE upper(
‘%@test.com’
) THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’Please Change the email address at Supplier site as @test.com for Supplier: ‘ || i.segment1 || ‘ in NON-PROD instance’
);
apps.fnd_file.put_line(
apps.fnd_file.log
,’Purchase Order sent to Email Address: ‘ || p_to_address
);
l_to_address := p_to_address;
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,’Please change the Email address at Supplier site or Input Parameter as @test.com for NON-PROD instance’
);
l_to_address := NULL;
END IF;
IF
i.segment1 IS NOT NULL AND l_to_address IS NOT NULL
THEN
l_bol_delivery := fnd_request.add_delivery_option(
type => ‘E’
, — EMAIL
p_argument1 => ‘FYI: For Your Review — Standard Purchase Order ‘ || i.segment1
, — Email Subject
p_argument2 => p_from_address
, — From Address
p_argument3 => l_to_address
, — To Address
p_argument4 => NULL — CC
);
l_request_id := fnd_request.submit_request(
application => ‘PO’
, –v_conc_prog_appl_short_name,
program => ‘POXPOPDF’
, –v_conc_prog_short_name,
description => NULL
, –Description
start_time => NULL
, –Time to start the program
sub_request => false
, — sub program
argument1 => ‘R’
,argument2 => NULL
,argument3 => i.segment1
,argument4 => i.segment1
,argument5 => NULL
,argument6 => NULL
,argument7 => NULL
,argument8 => NULL
,argument9 => NULL
,argument10 => ‘N’
,argument11 => ‘Y’
,argument12 => NULL
,argument13 => NULL
,argument14 => NULL
,argument15 => NULL
,argument16 => NULL
,argument17 => ‘Communicate’
,argument18 => ‘T’
,argument19 => ‘N’
,argument20 => ‘Y’
,argument21 => NULL
,argument22 => NULL
,argument23 => NULL
,argument24 => NULL
,argument25 => NULL
,argument26 => NULL
,argument27 => NULL
,argument28 => NULL
,argument29 => ‘N’
);
COMMIT;
IF
l_request_id = 0
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’Request not submitted error ‘ || fnd_message.get
);
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,’Request submitted successfully request id ‘ || l_request_id
);
END IF;
END IF;
END LOOP;
IF
l_rowcount IS NULL
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’No Approved Purchase Orders found between previous run and current run..!’
);
END IF;
ELSIF lv_org_name = ‘DEF’ THEN
lv_resp_name := fnd_profile.value(‘RESP_NAME’);
ln_resp_id := fnd_profile.value(‘RESP_ID’);
ln_org_id := fnd_profile.value(‘ORG_ID’);
l_to_address := p_to_address;
ln_responsibility_id := NULL;
ln_application_id := NULL;
BEGIN
SELECT frt.responsibility_id
,frt.application_id INTO
ln_responsibility_id,ln_application_id
FROM fnd_responsibility_tl frt
WHERE 1 = 1
AND frt.responsibility_name = lv_resp_name;
EXCEPTION
WHEN OTHERS THEN
ln_responsibility_id := NULL;
ln_application_id := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Responsibility ID and Application ID: ‘ || sqlerrm
);
END;
fnd_global.apps_initialize(
user_id => fnd_profile.value(‘USER_ID’)
,resp_id => ln_responsibility_id
,resp_appl_id => ln_application_id
);
/*Query to fetch concurrent program last run date*/
BEGIN
SELECT actual_completion_date INTO
l_last_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = ‘ABC PO Output for Communication’
AND fcr1.status_code = ‘C’
);
EXCEPTION
WHEN OTHERS THEN
l_last_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program last run date: ‘ || sqlerrm
);
END;
/*Query to fetch concurrent program current run date*/
BEGIN
SELECT actual_start_date INTO
l_curr_run_date
FROM fnd_concurrent_requests fcr
WHERE fcr.request_id = ( SELECT MAX(request_id)
FROM fnd_concurrent_requests fcr1
,fnd_concurrent_programs_tl fcpt
WHERE 1 = 1
AND fcpt.concurrent_program_id = fcr1.concurrent_program_id
AND fcr1.responsibility_id = ln_resp_id
AND fcpt.user_concurrent_program_name = ‘ABC PO Output for Communication’
AND fcr1.status_code = ‘R’
AND fcr1.phase_code = ‘R’
);
EXCEPTION
WHEN OTHERS THEN
l_curr_run_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program current run date: ‘ || sqlerrm
);
END;
BEGIN
SELECT name INTO
lv_db_name
FROM v$database;
EXCEPTION
WHEN OTHERS THEN
lv_db_name := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Database Name: ‘ || sqlerrm
);
END;
BEGIN
SELECT creation_date INTO
l_creation_date
FROM fnd_concurrent_programs_tl
WHERE user_concurrent_program_name = ‘ABC PO Output for Communication’;
EXCEPTION
WHEN OTHERS THEN
l_creation_date := NULL;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Error while fetching Concurrent program creation date: ‘ || sqlerrm
);
END;
FOR i IN ( SELECT a.segment1
,b.email_address
,c.vendor_name
,ROWNUM
FROM po_headers_all a
,ap_supplier_sites_all b
,ap_suppliers c
WHERE 1 = 1
AND a.vendor_site_id = b.vendor_site_id
AND c.vendor_id = b.vendor_id
AND b.supplier_notif_method = ‘PRINT’
AND a.authorization_status = ‘APPROVED’
AND a.type_lookup_code = ‘STANDARD’
AND a.org_id = ln_org_id
AND a.last_update_date BETWEEN ( nvl(
l_last_run_date
,l_creation_date
) ) AND ( l_curr_run_date )
ORDER BY a.segment1 ) LOOP
l_rowcount := i.rownum;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Fetching PO: ‘ || i.segment1 || ‘ for Supplier: ‘ || i.vendor_name || ‘ having email ‘ || i.email_address
);
IF
lv_db_name = ‘PROD’
THEN
l_to_address := i.email_address;
ELSIF lv_db_name != ‘PROD’ AND upper(
i.email_address
) LIKE upper(
‘%@test.com’
) THEN
l_to_address := i.email_address;
ELSIF upper(p_to_address) LIKE upper(
‘%@test.com’
) THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’Please Change the email address at Supplier site as @test.com for Supplier: ‘ || i.segment1 || ‘ in NON-PROD instance’
);
apps.fnd_file.put_line(
apps.fnd_file.log
,’Purchase Order sent to Email Address: ‘ || p_to_address
);
l_to_address := p_to_address;
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,’Please change the Email address at Supplier site or Input Parameter as @test.com for NON-PROD instance’
);
l_to_address := NULL;
END IF;
IF
i.segment1 IS NOT NULL AND l_to_address IS NOT NULL
THEN
l_bol_delivery := fnd_request.add_delivery_option(
type => ‘E’
, — EMAIL
p_argument1 => ‘FYI: For Your Review — Standard Purchase Order ‘ || i.segment1
, — Email Subject
p_argument2 => p_from_address
, — From Address
p_argument3 => l_to_address
, — To Address
p_argument4 => ” — CC
);
–fnd_request.submit_request should have same number of parameters as defined in concurrent program irrespective of display check box
— below is the standard program ‘PO Output for Communication’
l_request_id := fnd_request.submit_request(
application => ‘PO’
, –v_conc_prog_appl_short_name,
program => ‘POXPOPDF’
, –v_conc_prog_short_name,
description => NULL
, –Description
start_time => NULL
, –Time to start the program
sub_request => false
, — sub program
argument1 => ‘R’
,argument2 => NULL
,argument3 => i.segment1
,argument4 => i.segment1
,argument5 => NULL
,argument6 => NULL
,argument7 => NULL
,argument8 => NULL
,argument9 => NULL
,argument10 => ‘N’
,argument11 => ‘Y’
,argument12 => NULL
,argument13 => NULL
,argument14 => NULL
,argument15 => NULL
,argument16 => NULL
,argument17 => ‘Communicate’
,argument18 => ‘T’
,argument19 => ‘N’
,argument20 => ‘Y’
,argument21 => NULL
,argument22 => NULL
,argument23 => NULL
,argument24 => NULL
,argument25 => NULL
,argument26 => NULL
,argument27 => NULL
,argument28 => NULL
,argument29 => ‘N’
);
COMMIT;
IF
l_request_id = 0
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’Request not submitted error ‘ || fnd_message.get
);
ELSE
apps.fnd_file.put_line(
apps.fnd_file.log
,’Request submitted successfully request id ‘ || l_request_id
);
END IF;
END IF;
END LOOP;
IF
l_rowcount IS NULL
THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’No Approved Purchase Orders found between previous run and current run..!’
);
END IF;
ELSE
p_retcode := 2;
apps.fnd_file.put_line(
apps.fnd_file.log
,’Please select valid responsibility to execute Supplier conversion’
);
END IF;
EXCEPTION
WHEN OTHERS THEN
apps.fnd_file.put_line(
apps.fnd_file.log
,’Unexpected error in ABC PO Output for Communication’ || sqlerrm
);
END xxaa_po_output;
END xxaa_po_output_email;


50. API to fetch lookup meaning
arpt_sql_func_util.get_lookup_meaning(lookup_type, base_table_name.base_table_columns_name)


51. ROLLBACK
Suppose we have multiple transactions for single record. The first_transaction could be INSERT, second_transaction could be UPDATE, third_transaction could be DELETE & so on, for one person_id or employee_number. If the third_transaction fails then fourth and fifth should not take place and even the first and second should be roll backed then use below code.

In the place of RAISE lv_error if we use ROLLBACK then third, second and first transactions will be roll backed but the fourth and fifth proceeds.

DECLARE
lv_error EXCEPTION;
BEGIN
BEGIN
BEGIN
first_transaction;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE lv_error;
END;

BEGIN
second_transaction;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE lv_error;
END;

BEGIN
third_transaction;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE lv_error;
END;

BEGIN
fourth_transaction;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE lv_error;
END;

BEGIN
fifth_transaction;
EXCEPTION WHEN OTHERS THEN dbms_output.put_line(SQLERRM);
RAISE lv_error;
END;

EXCEPTION WHEN lv_error THEN ROLLBACK;
END;
COMMIT;
END;

SELECT * FROM TEST;

A(Primary_Key) B C
1 1 1
2 2 2

DECLARE
lv_user EXCEPTION;
BEGIN
FOR i IN ( SELECT a, b, c FROM test
) LOOP
dbms_output.put_line(‘Loop start ‘|| i.a);
IF
i.a = 1
THEN
BEGIN
BEGIN
INSERT INTO test VALUES (3, 3, 3);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in first INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (4, 4, 4);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in second INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (5, 5, 5);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in third INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (4, 4, 4);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in fourth INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (6, 6, 6);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in fifth INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (7, 7, 7);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in sixth INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
COMMIT;
EXCEPTION
WHEN lv_user THEN
ROLLBACK;
END;
ELSIF i.a = 2 THEN
BEGIN
BEGIN
INSERT INTO test VALUES (8, 8, 8);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in seventh INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
BEGIN
INSERT INTO test VALUES (9, 9, 9);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘Error in eight INSERT ‘|| i.a|| sqlerrm);
RAISE lv_user;
END;
COMMIT;
EXCEPTION
WHEN lv_user THEN
ROLLBACK;
END;
END IF;
END LOOP;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;


 


1. Explain INSTRING with an example.

2. Explain SUBSTRING with an example.

3. Explain REPLACE with an example.

4. Explain TRANSLATE with an example.

5. Explain DECODE with an example.

6. Which yields faster results — IN or EXISTS?
Answer: EXISTS

7. How do you copy data from one table to another table?
Answer: INSERT INTO table2 (column1, column2, column3, …)
SELECT column1, column2, column3, …
FROM table1
WHERE condition;

8. What are Cursor attributes?
Answer:
%ISOPEN – Evaluates to True when cursor is open, else False
%NOTFOUND – Evaluates to True if the most recent fetch doesn’t return a row, else False
%FOUND – Evaluates to True if the most recent fetch returns a row, else False
%ROWCOUNT – Gives total number of rows returned by cursor

9. What is the difference between TRUNCATE and DELETE?

10. What are the two different parts of the PL/SQL packages?

11. What are some predefined exceptions in PL/SQL?

12. Does PL/SQL support CREATE command? If yes, provide the method name.

13. When does a transaction complete? (Choose all that apply.)
A. When a DELETE statement is executed
B. When a ROLLBACK command is executed
C. When a PL/SQL anonymous block is executed
D. When a data definition language statement is executed
E. When a TRUNCATE statement is executed after the pending transaction
Answer: B,D,E

14. Which two statements are true regarding views? (Choose two.)
A. A subquery that defines a view cannot include the GROUP BY clause
B. A view is created with the subquery having the DISTINCT keyword can be updated
C. A view that is created with the subquery having the pseudo column ROWNUM keyword cannot be updated
D. A Data Manipulation Language (DML) operation can be performed on a view that is created with the subquery having all the NOT NULL columns of a table
Answer: C,D

15. Which two statements are true regarding working with dates? (Choose two.)
A. The default internal storage of dates is in the numeric format
B. The default internal storage of dates is in the character format
C. The RR date format automatically calculates the century from the SYSDATE function and does not allow the user to enter the century
D. The RR date format automatically calculates the century from the SYSDATE function but allows the user to enter the century if required
Answer: A,D

16. Which three statements are true regarding subqueries? (Choose three.)
A. Subqueries can contain GROUP BY and ORDER BY clauses
B. Main query and subquery can get data from different tables
C. Main query and subquery must get data from the same tables
D. Subqueries can contain ORDER BY but not the GROUP BY clause
E. Only one column or expression can be compared between the main query and subqeury
F. Multiple columns or expressions can be compared between the main query and subquery
Answer: A,B,F

17. Which three statements/commands would cause a transaction to end? (Choose three.)
A. COMMIT
B. SELECT
C. CREATE
D. ROLLBACK
E. SAVEPOINT
Answer: A,C,D

18. Which two statements are true about sequences created in a single instance database? (Choosetwo.)
A. The numbers generated by a sequence can be used only for one table
B. DELETE <sequencename> would remove a sequence from the database
C. CURRVAL is used to refer to the last sequence number that has been generated
D. When the MAXVALUE limit for a sequence for reached, you can increase the MAXVALUE limit by using the ALTER SEQUENCE statement
E. When a database instance shuts down abnormally, the sequence numbers that have been cached but not used would be available once again when the database instance is restarted
Answer: C,D

Assignments:

1. Inbound interface — Create a PL/SQL wrapper to extract data from flat file into Oracle DB using External table.
a) Place the file in usr/tmp path
b) Write the external table script
c) Verify data in staging table

2. Outbound interface — Create a PL/SQL wrapper to extract data from Oracle DB into flat file using UTL_FILE.
a) Develop the PL/SQL program (Either package or procedure)
b) Write the Cursor to retrieve the data from DB tables
c) Open the file by using UTL_File.fopen()
d) Open the cursor
e) Perform some data validations
f) Transfer the data into file using UTL_File.Put_Line()
g) Close the cursor
h) Close the file by using UTL_File.fclose()
i) Execute the anonymous block to get the result

3. Bulk Collect
Write a PL/SQL wrapper with and without BULK COLLECT with LIMIT clause and show the time difference to fetch one million records with and without BULK COLLECT.