Locking complete table [message #417870] |
Tue, 11 August 2009 23:46  |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I get some records in an excel sheet which contains some lot numbers with ingredient code. I have to update the
table "LOT_INFORMATION" so that it matches with the excel sheet data.
There can be 3 scenarios :-
1). I have to check in the table "LOT_INFORMATION" whether there is any quarantine lot present in it that is LotNo
present in "LOT_INFORMATION" but absent in the excel sheet.
--- If Present, the table "LOT_INFORMATION" needs to be updated with the status as Inactive that is Active=0
2). If there are new records for Lotno in excel sheet but not in the table "LOT_INFORMATION" then I have to insert that in the table.
3). If there are records for Lotno in excel sheet that were inactivated in "LOT_INFORMATION", they now need to be activated in table.
So there will be a 2 way check in the database. One to compare from excel sheet and other from database.
Is it a good idea to update the whole table "LOT_INFORMATION" with status as "INactive" and then compare the excel sheet with the table and keep updating whatever matches to it with status = "active"
And inserting if one is not found in it?
If yes, then do I need to lock the table while doing this process because if I update all the table records with staus = "Inactive", it would give wrong result to anyone who access this table in meantime.
This is a desktop application but multiple users would access this application.
I am not very clear on this topic as how to handle this.
If I have to lock the table what mode I should use in this scenario?
LOCK TABLE LOT_INFORMATION IN lockmode MODE ;
And do I need to unlock the table?
Please help me on this.
Regards,
Mahi
|
|
|
|
|
|
|
Re: Locking complete table [message #417884 is a reply to message #417882] |
Wed, 12 August 2009 00:23   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Sorry I forgot to mention that I would be extracting the data from excel sheet and using an array to match the records. Actually I have to pass the array of lotno from dot net to oracle procedure.
Please find the package I have created, using arrays for first time so not sure...please correc me whereever I am wrong.
CREATE OR REPLACE PACKAGE PKG_UPDATE_JDE_LOT_DATA
as
--CREATE ASSOCIATIVE ARRAYS FOR LOT_INFORMATION
TYPE t_LotNo is table of LOT_INFORMATION.LOT_NO%TYPE
INDEX BY binary_integer;
TYPE t_LotItemCode is table of LOT_INFORMATION.INGREDIENT_CODE%TYPE
INDEX BY binary_integer;
TYPE t_AvailableQty is table of LOT_INFORMATION.AVAILABLE_QTY%TYPE
INDEX BY binary_integer;
PROCEDURE UPDATE_JDE_LOT_DATA
(
--PARAMETERS FOR BOM_INFORMATION
p_JdeItemNo VARCHAR2,
p_BomVersion VARCHAR2,
--PARAMETERS FOR LOT_INFORMATION
p_LotNo in t_LotNo,
p_LotItemCode in t_LotItemCode,
p_AvailableQty in t_AvailableQty,
p_UserId VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY PKG_UPDATE_JDE_LOT_DATA
AS
PROCEDURE UPDATE_JDE_LOT_DATA
(
p_JdeItemNo VARCHAR2,
p_BomVersion VARCHAR2,
p_LotNo in t_LotNo,
p_LotItemCode in t_LotItemCode,
p_AvailableQty in t_AvailableQty,
p_UserId VARCHAR2
)
IS
v_Count INTEGER;
BEGIN
Lock table LOT_INFORMATION ROW EXCLUSIVE;
UPDATE LOT_INFORMATION SET ACTIVE=0;
--INSERT LOT INFORMATION
FOR j IN p_LotNo.first..p_LotNo.last LOOP
IF LENGTH(to_char(p_LotNo(j))) > 0 AND LENGTH(to_char(p_LotItemCode(j))) > 0 THEN
-- Check whether is there any quarantine lot present in the database i.e Lot No present in DB but absent in array parameter
-- If Present, mark the status as In active i.e. Active=0
SELECT Count(*) INTO v_Count FROM LOT_INFORMATION WHERE LOT_NO IN(p_LotNo(j)) AND INGREDIENT_CODE = p_LotItemCode(j);
IF (v_Count > 0) THEN
UPDATE LOT_INFORMATION SET ACTIVE=1 WHERE LOT_NO IN(p_LotNo(j)) AND INGREDIENT_CODE = p_LotItemCode(j);
ELSE
-- If Absent, Insert the new lot data
INSERT INTO LOT_INFORMATION (LOT_NO,INGREDIENT_CODE,AVAILABLE_QTY,ORIGINAL_QTY)
VALUES(p_LotNo(j),p_LotItemCode(j),p_AvailableQty(j)/1000,p_AvailableQty(j)/1000);
END IF;
END IF;
END LOOP;
IF SQL%FOUND THEN
INSERT_AUDIT_LOG ('DOWNLOAD JDE LOT DATA ',
'JDE ITEM NO: ' || p_JdeItemNo || 'BOM Version: ' || p_BomVersion ,
'',SysDate(),p_UserId);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END UPDATE_JDE_LOT_DATA;
END PKG_UPDATE_JDE_LOT_DATA;
/
Thanks for looking into this.
Mahi
|
|
|
|
|
|
|
Re: Locking complete table [message #417893 is a reply to message #417890] |
Wed, 12 August 2009 01:01   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
But I am going to update all the records of the table to status = 'inactive'. And then doing the update or inserts. What would happen if anyone fetches the data from that table? Do you mean to say that if I simply put a commit after all the transactions...it will help? And I do not need to put any lock explicitly?
Please also look into the array part.... as I am not very comfortable using arrays. I have created some types and using them to loop and check for the LotNumber in table.
Regards,
Mahi
|
|
|
Re: Locking complete table [message #417910 is a reply to message #417893] |
Wed, 12 August 2009 01:43   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
As long as you don't commit, nobody can see your changes.
Remove your when others exception, as it does nothing but hide the line number where the actual error occurs.
The upsert part can be replaced by a merge, the set-to-inactive part should (in my opinion) not be done on all records, but using a "where not exists", a "minus" or a "where not in".
|
|
|
Re: Locking complete table [message #417912 is a reply to message #417893] |
Wed, 12 August 2009 01:44   |
Prajakta001
Messages: 52 Registered: June 2008 Location: Mumbai
|
Member |

|
|
During the proc execution ,
If ayone tries to fetch data he/she will get older( Data before commit) data .
Even if u lock the table , You can not stop the reader from reading data.
Hence in any case locking table will not help ...
Please correct me if i am wrong.
Sorry Frank I saw your reply later (after I submitted)
[Updated on: Wed, 12 August 2009 01:46] Report message to a moderator
|
|
|
|
Re: Locking complete table [message #417915 is a reply to message #417910] |
Wed, 12 August 2009 01:50   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Thanks for your reply Frank, but how to use a "where not exists", a "minus" or a "where not in". in this case when I am using arrays.
As I will be getting only one value when the loop moves.... pls tell me a way as how can I use "where not exists" as I wanted to use this only but not sure how to do this when I am getting one value at one time in the loop.
Regards,
Mahi
|
|
|
|
Re: Locking complete table [message #417917 is a reply to message #417916] |
Wed, 12 August 2009 02:00   |
Frank
Messages: 7901 Registered: March 2000
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 12 August 2009 08:52 | Install HSODBC then you can use your Excel file as a table and do it in a single MERGE statement.
|
Either this, or export your excel-sheet as a comma separated file, that can be read as an external table.
You might even go back one step in the process and use the process that generates the Excel file to generate the data you need.
|
|
|
Re: Locking complete table [message #417922 is a reply to message #417917] |
Wed, 12 August 2009 02:08   |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hmmm, a complete new makeover...!!
But I have never used Merge statements and not clear about how to import csv into external table...!
Do u mind giving a little insight on this!
Mahi
|
|
|
|
Re: Locking complete table [message #417928 is a reply to message #417880] |
Wed, 12 August 2009 02:54   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Quote: | Oracle does not know how to read native Excel files.
|
Oracle HS can read from Excel spreadsheets as though each worksheet in them were a separate table.
|
|
|
Re: Locking complete table [message #417944 is a reply to message #417928] |
Wed, 12 August 2009 04:09  |
virmahi
Messages: 266 Registered: April 2008 Location: India
|
Senior Member |
|
|
Hi,
I am doing only a client change request where I have to create a procedure in oracle. The dot net people are sending me an array of lotnos. I have to code after that only....I consulted from them and they do not want that part to be changed as this is a live application. They have only this requirement so I can't use the merge table.
Can you suggest me as how can I use "where not exists" instead of the way I am doing where I am updating the whole table.
Regards,
Mahi
|
|
|