Home » SQL & PL/SQL » SQL & PL/SQL » Locking complete table (Oracle 10g)
Locking complete table [message #417870] Tue, 11 August 2009 23:46 Go to next message
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 #417878 is a reply to message #417870] Wed, 12 August 2009 00:03 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Hi Mahi ,

I think u can achive this by using External table if u are using Oracle 9i and above .

U dont need to lock the table explicitly..

Frank ..Please correct me if I am wrong .

Regards
Prajakta

[Updated on: Wed, 12 August 2009 00:05]

Report message to a moderator

Re: Locking complete table [message #417880 is a reply to message #417870] Wed, 12 August 2009 00:07 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>I think u can achive this by using External table if u are using Oracle 9i and above .
Oracle does not know how to read native Excel files.
Please prove me wrong with reproducible test cases.
Re: Locking complete table [message #417881 is a reply to message #417880] Wed, 12 August 2009 00:09 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Yeh .. the file has to be in .csv fromat
Smile
forgot to mention

Regards
Prajakta
Re: Locking complete table [message #417882 is a reply to message #417870] Wed, 12 August 2009 00:12 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Please help me on this.
Excel is a a very, very, very poor GUI interface to Oracle.
It is like using a hammer to divide a board into 2 pieces of lumber.
You may succeed with Excel, but it would be easier if you used a better or different interface tool.
Re: Locking complete table [message #417884 is a reply to message #417882] Wed, 12 August 2009 00:23 Go to previous messageGo to next message
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 #417885 is a reply to message #417870] Wed, 12 August 2009 00:25 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
>Yeh .. the file has to be in .csv fromat
Please provide working examples how .csv can be used with multiple work sheets within Excel.
Re: Locking complete table [message #417887 is a reply to message #417885] Wed, 12 August 2009 00:36 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

Has MAhi mentioned multiple excel sheets?
Re: Locking complete table [message #417888 is a reply to message #417887] Wed, 12 August 2009 00:39 Go to previous messageGo to next message
virmahi
Messages: 266
Registered: April 2008
Location: India
Senior Member
No there is only one sheet.

Please suggest me on the way I have used the array if its correct and whether I should use the LOCK on tables.


Re: Locking complete table [message #417890 is a reply to message #417888] Wed, 12 August 2009 00:46 Go to previous messageGo to next message
Prajakta001
Messages: 52
Registered: June 2008
Location: Mumbai
Member

you dont have to lock the table explicitly.

Regards
Prajakta

[Updated on: Wed, 12 August 2009 00:48]

Report message to a moderator

Re: Locking complete table [message #417893 is a reply to message #417890] Wed, 12 August 2009 01:01 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #417914 is a reply to message #417912] Wed, 12 August 2009 01:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
@Prajakta001

Stop using IM speak, like "u".

Regards
Michel
Re: Locking complete table [message #417915 is a reply to message #417910] Wed, 12 August 2009 01:50 Go to previous messageGo to next message
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 #417916 is a reply to message #417870] Wed, 12 August 2009 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Install HSODBC then you can use your Excel file as a table and do it in a single MERGE statement.

Regards
Michel
Re: Locking complete table [message #417917 is a reply to message #417916] Wed, 12 August 2009 02:00 Go to previous messageGo to next message
Frank
Messages: 7880
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 Go to previous messageGo to next message
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 #417925 is a reply to message #417922] Wed, 12 August 2009 02:18 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Google is your friend.
Do some research and come back if you have more specific questions.
Re: Locking complete table [message #417928 is a reply to message #417880] Wed, 12 August 2009 02:54 Go to previous messageGo to next message
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 Go to previous message
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
Previous Topic: ORA-00604: error occurred at recursive SQL level 1
Next Topic: DROP TABLE DEPT - dependencies
Goto Forum:
  


Current Time: Sat Dec 03 20:40:04 CST 2016

Total time taken to generate the page: 0.15628 seconds