, , , ,

Bulk Collect in Oracle PL/SQL

We usually use cursor for loops to process data.(i.e declare a cursor, open it, fetch from it row by row in a loop and process the row they fetch) statements in plsql programs causes a context switch between the plsql engine and the sql engine.Too many context switches may degrade performance dramatically.

In order to reduce the number of these context switches we can use bulk collecting feature
Bulk collecting lets us to transfer rows between the sql engine and the plsql engine as collections.
Bulk collecting is available for select, insert, delete and update statements.

Below are some examples:

create table BULK_COLLECT_TEST as select * from PER_ALL_PEOPLE_F;

Table created.

insert into BULK_COLLECT_TEST

select * from BULK_COLLECT_TEST;

20000 rows created.

–BLOCK1:Using Loops
declare
 cursor c1
 is select object_name from BULK_COLLECT_TEST;
 rec1 c1%rowtype;
 begin
      open c1;
       loop
       fetch c1 into rec1;
    exit when c1%notfound;
    null;
    end loop;
 end;

total Elapsed Time is : 45 Secs

–BLOCK2: Using Bulk Collecting
declare
  cursor c1 is select object_name from BULK_COLLECT_TEST;
  type c1_type is table of c1%rowtype;
  rec1 c1_type;
begin
open c1;
   fetch c1 bulk collect into rec1;
end;

total Elapsed Time is : 5 Sec

So bulk collecting the rows shows a huge performance improvement over fetching row by row.

Some cases there are many rows to process, we can limit the number of rows to bulk collect, process those rows and fetch again.
Otherwise process memory gets bigger and bigger as you fetch the rows.

–Bulk Collect Example using LIMIT :
declare
 cursor c1 is select object_name from BULK_COLLECT_TEST;
 type c1_type is  table of c1%rowtype;
 rec1 c1_type;
begin
    open c1;
    loop
    fetch c1 bulk collect into rec1 limit 200;
    for i in 1..rec1.count loop
    null;
    end loop;
    exit when c1%notfound;
    end loop;
end;

/by
, , , ,

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

How to Kill the session when there is a lock on the objects you are working in TOAD or Sqlplus

select * from V$SESSION
where OSUSER like ‘c_sgoud’ — User name of the terminal ( may be your windows login name )

Alter system kill session ‘146,46619’

select sid, serial# from v$session where username = ‘USER’;

alter system kill session ‘SID,SERIAL#’;

you need to find first lock
SELECT DECODE(request,0,’Holder: ‘,’Waiter: ‘)||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;
by this query you can find lock

then you can kill
col program for a25
col status for a10
col SER# for a10
col LOGON_TIME for a20
select
substr(a.spid,1,9) pid,
substr(b.sid,1,5) sid,
substr(b.serial#,1,5) ser#,
substr(b.machine,1,6) box,
substr(b.username,1,10) username,
— b.server,
substr(b.osuser,1,8) os_user,
substr(b.program,1,30) program ,
b.status,
— b.module,
b.LOGON_TIME
from v$session b, v$process a
where
b.paddr = a.addr
and type=’USER’
and b.status=’ACTIVE’
–and b.module like ‘%blbn%’
–and b.username=’ENBAPP23′
order by program ,OS_USER

 How to Unlock the Objects

The following query could be useful :

Select SPID from V$PROCESS where ADDR in
(select PADDR from V$SESSION where SID in
(select SESSION_ID from V$LOCKED_OBJECT where OBJECT_ID in
(select OBJECT_ID from DBA_OBJECTS where OBJECT_NAME=’Locked Object’)))

Get the Process id from the query. Login as unix user and run the following command to kill the process.

/by
, , , , , ,

Resolving ORACLE ERROR:ORA-28000: the account is locked

From your command prompt, type
sqlplus “/ as sysdba”

Once logged in as SYSDBA, you need to unlock the scott account
SQL> alter user scott account unlock;
SQL> grant connect, resource to scott;

/by
, , , , , , , , ,

Allocating Move Order INV_REPLENISH_DETAIL_PUB

DECLARE

l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_return_values VARCHAR2 (2) := fnd_api.g_false;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_user_id NUMBER ;
l_resp_id NUMBER ;
l_appl_id NUMBER ;
l_row_cnt NUMBER := 1;
l_trohdr_rec inv_move_order_pub.trohdr_rec_type;
l_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
x_trohdr_rec inv_move_order_pub.trohdr_rec_type;
x_trohdr_val_rec inv_move_order_pub.trohdr_val_rec_type;
l_validation_flag VARCHAR2 (2) := inv_move_order_pub.g_validation_yes;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
x_trolin_val_tbl inv_move_order_pub.trolin_val_tbl_type;
x_number_of_rows NUMBER ;
x_transfer_to_location NUMBER ;
x_expiration_date DATE;
x_transaction_temp_id NUMBER ;

CURSOR c_mo_details IS

SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision,mtrl.from_locator_id,
(select distinct operating_unit from org_organization_definitions
where organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = ‘332557’
AND mtrh.organization_id = 381;

BEGIN

SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;

SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Inventory’;

fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);

FOR i IN c_mo_details

LOOP

mo_global.set_policy_context (‘S’, i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init (‘INV’);

SELECT COUNT (*)
INTO x_number_of_rows
FROM mtl_txn_request_lines
WHERE header_id = i.header_id;

DBMS_OUTPUT.put_line (‘Calling INV_REPLENISH_DETAIL_PUB to Allocate MO’);
— Allocate each line of the Move Order

inv_replenish_detail_pub.line_details_pub
(p_line_id => i.line_id,
x_number_of_rows => x_number_of_rows,
x_detailed_qty => i.quantity,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_revision => i.revision,
x_locator_id => i.from_locator_id,
x_transfer_to_location => x_transfer_to_location,
x_lot_number => i.lot_number,
x_expiration_date => x_expiration_date,
x_transaction_temp_id => x_transaction_temp_id,
p_transaction_header_id => NULL,
p_transaction_mode => NULL,
p_move_order_type => i.move_order_type,
p_serial_flag => fnd_api.g_false,
p_plan_tasks => FALSE,
p_auto_pick_confirm => FALSE,
p_commit => FALSE
);

DBMS_OUTPUT.put_line
(‘==========================================================’);
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (x_msg_count);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;

IF (x_return_status = fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (‘Trx temp ID: ‘);
DBMS_OUTPUT.put_line (x_transaction_temp_id);
END IF;
DBMS_OUTPUT.put_line
(‘==========================================================’);
END LOOP;

EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Exception Occured :’);
DBMS_OUTPUT.put_line (SQLCODE ‘:’ SQLERRM);
DBMS_OUTPUT.put_line
(‘=======================================================’);
END;

DBMS Output: –

Calling INV_REPLENISH_DETAIL_PUB to Allocate MO
==========================================================
S
Trx temp ID: 1094230
==========================================================

select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230

1 Row Returned

/by
, , , , , , , , ,

Transact Move Order Using INV_PICK_WAVE_PICK_CONFIRM_PUB

DECLARE
l_api_version NUMBER := 1.0;
l_init_msg_list VARCHAR2 (2) := fnd_api.g_true;
l_commit VARCHAR2 (2) := fnd_api.g_false;
x_return_status VARCHAR2 (2);
x_msg_count NUMBER := 0;
x_msg_data VARCHAR2 (255);
l_move_order_type NUMBER := 1;
l_transaction_mode NUMBER := 1;
l_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_mold_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_mmtt_tbl inv_mo_line_detail_util.g_mmtt_tbl_type;
x_trolin_tbl inv_move_order_pub.trolin_tbl_type;
l_transaction_date DATE := SYSDATE;
l_user_id NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
CURSOR c_mo_details IS
SELECT mtrh.header_id, mtrh.request_number, mtrh.move_order_type,
mtrh.organization_id, mtrl.line_id, mtrl.line_number,
mtrl.inventory_item_id, mtrl.lot_number, mtrl.quantity,
revision, mtrl.from_locator_id,
(SELECT DISTINCT operating_unit
FROM org_organization_definitions
WHERE organization_id = mtrh.organization_id) org_id
FROM mtl_txn_request_headers mtrh, mtl_txn_request_lines mtrl
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.request_number = ‘332557’
AND mtrh.organization_id = 381;
BEGIN
SELECT user_id
INTO l_user_id
FROM fnd_user
WHERE user_name = ‘A42485’;
SELECT responsibility_id, application_id
INTO l_resp_id, l_appl_id
FROM fnd_responsibility_vl
WHERE responsibility_name = ‘Inventory’;
fnd_global.apps_initialize (l_user_id, l_resp_id, l_appl_id);
FOR i IN c_mo_details
LOOP
mo_global.set_policy_context (‘S’, i.org_id);
inv_globals.set_org_id (i.organization_id);
mo_global.init (‘INV’);
l_trolin_tbl (1).line_id := i.line_id;
— call API to create move order header
DBMS_OUTPUT.put_line
(‘=======================================================’);
DBMS_OUTPUT.put_line
(‘Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API’);
inv_pick_wave_pick_confirm_pub.pick_confirm
(p_api_version_number => l_api_version,
p_init_msg_list => l_init_msg_list,
p_commit => l_commit,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_move_order_type => i.move_order_type,
p_transaction_mode => l_transaction_mode,
p_trolin_tbl => l_trolin_tbl,
p_mold_tbl => l_mold_tbl,
x_mmtt_tbl => x_mmtt_tbl,
x_trolin_tbl => x_trolin_tbl,
p_transaction_date => l_transaction_date
);
DBMS_OUTPUT.put_line
(‘=======================================================’);
DBMS_OUTPUT.put_line (x_return_status);
DBMS_OUTPUT.put_line (x_msg_data);
DBMS_OUTPUT.put_line (x_msg_count);

IF (x_return_status <> fnd_api.g_ret_sts_success)
THEN
DBMS_OUTPUT.put_line (x_msg_data);
END IF;
DBMS_OUTPUT.put_line
(‘=======================================================’);
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line (‘Exception Occured :’);
DBMS_OUTPUT.put_line (SQLCODE ‘:’ SQLERRM);
DBMS_OUTPUT.put_line
(‘=======================================================’);
END;
DBMS Output:
=======================================================
Calling INV_Pick_Wave_Pick_Confirm_PUB.Pick_Confirm API
=======================================================
S
=======================================================
Verification:
select * from mtl_material_transactions_temp
where transaction_temp_id = 1094230
No Rows Returned
— Link the Move Order with the Material Transcations Table

SELECT
mmt.transaction_id,
mtrl.organization_id,
mtrh.request_number,
mtrh.header_id,
mtrl.line_number,
mtrl.line_id,
mtrl.inventory_item_id,
mtrh.description,
mtrh.move_order_type,
mtrl.line_status,
(select meaning from mfg_lookups
where lookup_type = ‘MTL_TXN_REQUEST_STATUS’
and lookup_code = mtrl.line_status) Line_status_meaning,
mtrl.quantity,
mtrl.quantity_delivered,
mmt.transaction_type_id,
mmt.transaction_date
FROM mtl_txn_request_headers mtrh,
mtl_txn_request_lines mtrl,
mtl_material_transactions mmt
WHERE mtrh.header_id = mtrl.header_id
AND mtrh.organization_id = mtrl.organization_id
AND mtrl.line_id = mmt.move_order_line_id
AND mtrh.request_number = 332557
AND mtrh.organization_id = 381

/by