FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.
If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.
Example:-
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
— The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
— SAVE EXCEPTIONS means don’t stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
— Count how many rows were inserted for each department; that is,
— how many employees are in each department.
dbms_output.put_line(‘Dept ‘||deptnums(i)||’: inserted ‘||
SQL%BULK_ROWCOUNT(i)||’ records’);
END LOOP;
dbms_output.put_line(‘Total records inserted =’ || SQL%ROWCOUNT);
— If any errors occurred during the FORALL SAVE EXCEPTIONS,
— a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN — Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of INSERT statements that failed: ‘ || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line(‘Error #’ || i || ‘ occurred during ‘||
‘iteration #’ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(‘Error message is ‘ ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
FlexFields
Note:- The following Flexfields are avilable in the 11.5.8 Oracle Application Version.
—–
You may find more flexfield in the new Oracle apps versions.
To check all the Flexfield avilable for your Oracle Apps version, use the Flexfield Query
Share this:
QUERY FOR FINDING REQUEST GROUP
frg.request_group_name,
fe.execution_file_name,
fe.executable_name
FROM fnd_request_group_units frgu,
fnd_concurrent_programs fcp,
fnd_request_groups frg,
fnd_executables fe,
fnd_application fa
WHERE frgu.request_unit_id = fcp.concurrent_program_id
AND frgu.request_group_id = frg.request_group_id
AND fe.executable_id = fcp.executable_id
AND FRG.application_id = fa.application_id
AND fe.executable_name = ‘XX_PC_PURCHASE’;
Share this:
Operating Unit name based on the Operating Unit ID
select Business_group_id, Organization_id, name,date_from,date_to, legal_entity_id, set_of_books_id from hr_operating_units where organization_id = ‘your Operating Unit ID’;
Share this:
Concurrent program name based on the request ID
Select program from FND_CONC_REQ_SUMMARY_V
where request_id = ‘Your request ID’;
Share this:
FORALL
Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.
If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.
Example:-
CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
— The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
— SAVE EXCEPTIONS means don’t stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
— Count how many rows were inserted for each department; that is,
— how many employees are in each department.
dbms_output.put_line(‘Dept ‘||deptnums(i)||’: inserted ‘||
SQL%BULK_ROWCOUNT(i)||’ records’);
END LOOP;
dbms_output.put_line(‘Total records inserted =’ || SQL%ROWCOUNT);
— If any errors occurred during the FORALL SAVE EXCEPTIONS,
— a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN — Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of INSERT statements that failed: ‘ || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line(‘Error #’ || i || ‘ occurred during ‘||
‘iteration #’ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(‘Error message is ‘ ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
Share this: