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 Collect
13. LIMIT clause
14. Bulk Binds
15. PL/SQL block to create user and assign responsibility to user – Password change
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 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. Bulk Binds
Bulk Binds – Reduce the context switching between PGA (Program Global Area) and SGA (System Global Area). Able to perform bulk DML operations like BULK INSERT, BULK UPDATE, BULK DELETE
If insert, update, delete commands are embedded in FORALL loop then it is called bulk insert, bulk update, bulk delete
FORALL i IN lower_limit..upper_limit
<ONLY ONE DML STATEMENT ALLOWED HERE>
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.
DECLARE TYPE emp_array IS TABLE OF emp.empno%TYPE INDEX BY BINARY_INTEGER; enum emp_array; BEGIN SELECT empno BULK COLLECT INTO enum FROM emp; FORALL i IN enum.first..enum.last UPDATE emp SET sal=sal+1000 WHERE empno=enum(i); END;
DECLARE
TYPE dept_array IS TABLE OF emp.deptno%TYPE INDEX BY BINARY_INTEGER;
dnum dept_array;
BEGIN
SELECT DISTINCT deptno BULK COLLECT INTO dnum FROM emp;
FORALL i IN dnum.first..dnum.last
DELETE FROM emp WHERE deptno = dnum(i);
FOR i IN dnum.first..dnum.last
LOOP
dbms_output.put_line('Iteration '||i||' rows deleted: ||SQL%BULK_ROWCOUNT(i)); -- Returns number of records deleted in each iteration
END LOOP;
END;
15. PL/SQL block to create user and assign responsibility to user – Password change
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;
create or replace procedure JS_Password_reset_p(p_user_name IN varchar2, p_password IN varchar2) IS
v_user_name_all VARCHAR2(10000) := p_user_name;
v_new_password VARCHAR2(30):= p_password ;
v_status BOOLEAN;
v_user_name fnd_user.user_name%TYPE;
Cursor C_user_names is
select regexp_substr(v_user_name_all ,'[^;]+', 1, level) v_user_name from dual
connect by regexp_substr(v_user_name_all ,'[^;]+', 1, level) is not null;
BEGIN
for rec in c_user_names
loop
v_status := fnd_user_pkg.ChangePassword ( username => rec.v_user_name,
newpassword => v_new_password
);
IF v_status THEN
dbms_output.put_line ('The password reset successfully for the User:'||rec.v_user_name);
COMMIT;
ELSE
DBMS_OUTPUT.put_line ('Unable to reset password for '||rec.v_user_name||' due to'||SQLCODE||' '||SUBSTR(SQLERRM, 1, 100));
ROLLBACK;
END IF;
end loop;
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 |
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 | ||||||||
|
|
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 returnedNow 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;
CLEAR SCREEN;
SET SERVEROUTPUT ON;
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; /*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.*/
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 */
/*Memory is restricted only to that particular block and once it comes out of block, memory will be released.*/
Example 2:
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);
dbms_output.put_line('The value in SERIALLY_REUSABLE package is '||my_package_sr.sr_number);
my_package_nsr.ns_number := 50;
dbms_output.put_line('The value in Non SERIALLY_REUSABLE package is '||my_package_nsr.ns_number);
my_package_sr.sr_number := 75;
dbms_output.put_line('The value in SERIALLY_REUSABLE package is '||my_package_sr.sr_number);
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
*/
BEGIN
dbms_output.put_line('The value in Non SERIALLY_REUSABLE package is '||my_package_nsr.ns_number);
dbms_output.put_line('The value in SERIALLY_REUSABLE package is '||my_package_sr.sr_number);
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.