Exception handling for Bulk Insert and Bulk update using FORALL

DECLARE
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
-- SAVE EXCEPTIONS means don't stop if some DELETEs fail.
FORALL i IN num_tab.FIRST..num_tab.LAST SAVE EXCEPTIONS
DELETE FROM emp2 WHERE salary > 500000/num_tab(i);
-- 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 DELETE 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;

FNDLOAD for Profile

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afscprof.lct filename.ldt PROFILE PROFILE_NAME="profile name" APPLICATION_SHORT_NAME="XXDIS"

UPLOAD
=======
FNDLOAD apps/appspwd O Y UPLOAD $FND_TOP/patch/115/import/afscprof.lct filename.ldt

FNDLOAD script for value set with values

DOWNLOAD
=========
FNDLOAD apps/appspwd O Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt VALUE_SET FLEX_VALUE_SET_NAME="valueset name"

UPLOAD
======
FNDLOAD apps/appsdev 0 Y UPLOAD $FND_TOP/patch/115/import/afffload.lct filename.ldt

PLSQL block with bulk collect and limit

DECLARE
CURSOR get_item_det_cur (
p_in_num_from_itemid IN NUMBER,
p_in_num_to_itemid IN NUMBER
)
IS
SELECT msib.inventory_item_id, msib.organization_id
FROM mtl_system_items_b msib
WHERE msib.segment2 = msib.attribute5
AND msib.organization_id = 81
AND msib.inventory_item_id BETWEEN p_in_num_from_itemid
AND p_in_num_to_itemid;

TYPE item_det_tbl IS TABLE OF get_item_det_cur%ROWTYPE
INDEX BY BINARY_INTEGER;

l_item_det_tbl item_det_tbl;
l_item_id NUMBER;
l_org_id NUMBER;
l_cnt NUMBER := 0;
BEGIN
OPEN get_item_det_cur (1174281, 1174500);

LOOP
l_cnt := l_cnt + 1;
DBMS_OUTPUT.put_line ('l_cnt==>' || l_cnt);

FETCH get_item_det_cur
BULK COLLECT INTO l_item_det_tbl LIMIT 10;

FOR idx IN l_item_det_tbl.FIRST .. l_item_det_tbl.LAST
LOOP
l_item_id := l_item_det_tbl (idx).inventory_item_id;
l_org_id := l_item_det_tbl (idx).organization_id;
DBMS_OUTPUT.put_line ('l_item_id==>' || l_item_id);
DBMS_OUTPUT.put_line ('l_org_id==>' || l_org_id);
END LOOP;
END LOOP;

CLOSE get_item_det_cur;
END;

SQL to find which query is running while in the concurrent request

SELECT vst.sql_text, SYSDATE, fcr.request_id, fcp.concurrent_program_name,
fcr.oracle_process_id traceid, fcr.requested_start_date,
fcr.actual_start_date, fcr.actual_completion_date,
fcr.status_code status,
ROUND ( ( NVL (fcr.actual_completion_date, SYSDATE)
- fcr.actual_start_date
)
* 24
* 60,
2
) run_time,
vst.sql_text, vs.SID, vs.serial#, vs.status, fcr.parent_request_id,
fcr.os_process_id
FROM v$sqlarea vst,
v$session vs,
apps.fnd_concurrent_programs fcp,
apps.fnd_concurrent_requests fcr
WHERE vst.address(+) = vs.sql_address
AND vs.process(+) = fcr.os_process_id
AND fcp.concurrent_program_id = fcr.concurrent_program_id
AND fcr.request_id = --Request ID--;

Concurrent Program Specific SQL Queries

To fetch responsibility assigned to a concurrent program
===========================================================
select resp.responsibility_name
from fnd_responsibility_vl resp, fnd_request_group_units rgu, fnd_concurrent_programs_vl prog
where resp.request_group_id = rgu.request_group_id
AND rgu.request_unit_id = prog.concurrent_program_id
AND rgu.unit_application_id = prog.application_id
AND prog.user_concurrent_program_name = ;


To fetch parameters assigned to a concurrent program
=====================================================

select cols.end_user_column_name,
cols.form_left_prompt,
fvs.flex_value_set_name,
cols.default_value
from fnd_descr_flex_col_usage_vl cols,
fnd_flex_value_sets fvs,
fnd_concurrent_programs_vl prog
where cols.flex_value_set_id = fvs.flex_value_set_id
AND cols.descriptive_flexfield_name = '$SRS$.' || prog.concurrent_program_name
And prog.user_concurrent_program_name like ;


To fetch details of executables and concurrent programs
===========================================================

select
prog.user_concurrent_program_name "program name",
prog.concurrent_program_name "program short name",
appl.application_name "program application name",
prog.description "program description",
exe.executable_name "executable name",
exe.execution_file_name "executable file name",
exe.description "executable description",
decode( exe.execution_method_code, 'I', 'PLSQL Stored Procedure', 'P', 'Report', 'L', 'SQL Loader', exe.execution_method_code) "execution method"
from
fnd_executables exe,
fnd_application_tl appl,
fnd_concurrent_programs_vl prog
where prog.application_id = appl.application_id
AND exe.executable_id = prog.executable_id
AND prog.user_concurrent_program_name =
AND prog.concurrent_program_name =
AND exe.execution_file_name =

Find a concurrent program's trace file

SELECT
req.request_id
,req.logfile_node_name node
,req.oracle_Process_id
,req.enable_trace
,dest.VALUE||'/'||LOWER(dbnm.VALUE)||'_ora_'||oracle_process_id||'.trc' trace_filename
,prog.user_concurrent_program_name
,execname.execution_file_name
,execname.subroutine_name
,phase_code
,status_code
,ses.SID
,ses.serial#
,ses.module
,ses.machine
FROM
fnd_concurrent_requests req
,v$session ses
,v$process proc
,v$parameter dest
,v$parameter dbnm
,fnd_concurrent_programs_vl prog
,fnd_executables execname
WHERE 1=1
AND req.request_id = &request
AND req.oracle_process_id=proc.spid(+)
AND proc.addr = ses.paddr(+)
AND dest.NAME='user_dump_dest'
AND dbnm.NAME='db_name'
AND req.concurrent_program_id = prog.concurrent_program_id
AND req.program_application_id = prog.application_id
AND prog.application_id = execname.application_id
AND prog.executable_id=execname.executable_id