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. API to fetch lookup meaning
50. 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 a, b FROM dual;
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

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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
CLEAR SCREEN;
SET SERVEROUTPUT ON;
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
 -------------------dbms_output------------------------------*/

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
 -------------------dbms_output------------------------------*/

Using BULK COLLECT:

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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;
CLEAR SCREEN;
SET SERVEROUTPUT ON;
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
 -------------------dbms_output------------------------------*/

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:

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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
-------------------dbms_output------------------------------*/

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

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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;
  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;
  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;
  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.
-------------------dbms_output------------------------------*/

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.

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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;
CLEAR SCREEN;
SET SERVEROUTPUT ON;
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;
CLEAR SCREEN;
SET SERVEROUTPUT ON;
CREATE TABLE emp_rowtype_insert
  AS
    SELECT * FROM emp WHERE 1 = 2;
SELECT * FROM emp_rowtype_insert;
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;
CLEAR SCREEN;
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;
CLEAR SCREEN;
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;
CLEAR SCREEN;
SET SERVEROUTPUT ON;
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.

CLEAR SCREEN;
SET SERVEROUTPUT ON;
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

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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
    dbms_output.put_line('Error ' || sqlerrm);
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.

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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
***************dbms_output***************/

 

SET SERVEROUTPUT ON
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
***************dbms_output***************/

 

SET SERVEROUTPUT ON;
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
***************dbms_output***************/

 

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 (
  studi        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, 'Object2', '01-JAN-1985', '01-JAN-2005', 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

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

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)

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

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

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

Opening the cursor variable that contains the query

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

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:

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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
***********Output************/
/
SHOW ERROR

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.

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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:
*****************Run Time Error*****************/

Exception Handling

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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
*****************dbms_output.put_line*****************/
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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.
*****************dbms_output.put_line*****************/

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.

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR
SET SERVEROUTPUT ON;
CLEAR SCREEN;
DECLARE
  l_err_buf    VARCHAR2(100);
  l_ret_code   NUMBER;
BEGIN
  rollbacktest.main(
    x_errbuf    => l_err_buf
    ,x_retcode   => l_ret_code
  );
END;
/
SHOW ERROR

 


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. API to fetch lookup meaning
arpt_sql_func_util.get_lookup_meaning(lookup_type, base_table_name.base_table_columns_name)


50. 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.

SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

 

SELECT * FROM TEST;

A(Primary_Key) B C
1 1 1
2 2 2
SET SERVEROUTPUT ON;
CLEAR SCREEN;
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;
/
SHOW ERROR

 


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.