Home » RDBMS Server » Server Utilities » SQL * Loader usage in differentiating between the files
SQL * Loader usage in differentiating between the files [message #258716] Mon, 13 August 2007 07:58 Go to next message
gsvd_nagesh
Messages: 4
Registered: August 2007
Junior Member
Hi All,

By using SQL*Loader we'l load the file into staging table.Our requirement is as follows:
There will be a column called FILE_NAME in staging table, and the file name wil not be given in data file and we hav to insert the file name into Staging table. We can do so by passing the file name as parameter. No problem upto this.

The problem comes when multiple users uploads the files at the same time.....at tht time the following command will not work because when second user is executing the prog and finishes earlier than the first , then it will update the first persons records also along with the second one.

if sqlldr $userpass data=$DATAPATH/incoming/$fname control=$TOP/bin/$ctlfile log=$log_file bad=$bad_file
then
echo "set heading off
set echo off
update staging_history_int set data_file_name = '$fname', requestid= '$requestid' where data_file_name is null; | sqlplus -s $userpass.

Is there any way of passing the REQUEST_ID of the sqlldr request into the Control file???

Pls advice in solving this problem. One thing we can't use INCOMAPTABILITY setting option.




Re: SQL * Loader usage in differentiating between the files [message #258786 is a reply to message #258716] Mon, 13 August 2007 10:18 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Obviously, you'll have to differentiate users as well. Perhaps you might add another column to the stagging table (USER_ID or something like that), pass its value just as you do with the file name and, finally, include it into the WHERE clause.
Re: SQL * Loader usage in differentiating between the files [message #258828 is a reply to message #258786] Mon, 13 August 2007 11:28 Go to previous messageGo to next message
gsvd_nagesh
Messages: 4
Registered: August 2007
Junior Member
Hi..thanks for quick response.
2 persons also can execute with the same USER_ID. This processing is done at the lower level and many data files will be processed in a day. So client can use the same ID to load the files.
Here the problem comes.
Re: SQL * Loader usage in differentiating between the files [message #258836 is a reply to message #258828] Mon, 13 August 2007 11:42 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
So create another "ID" column (which might be a sequence or something similar). If you don't make a difference, how are you going to update records you have to?

Did you consider using a global temporary table (GTT) (which would act as your current staging table) and loading it using the external table feature? GTT would enable every user to see only its own data and you'd be able to keep existing UPDATE statement (no other WHERE condition would be needed).
Re: SQL * Loader usage in differentiating between the files [message #258893 is a reply to message #258836] Mon, 13 August 2007 14:06 Go to previous messageGo to next message
gsvd_nagesh
Messages: 4
Registered: August 2007
Junior Member
I m processing using the sql*loader and not using External Table concept.
I want to know the process of differentiating between different Executions of the same progm happening by one ID and at the same time.
For Example: differentiating factor would be the REQUEST_ID (since each execution wil result in Unique REQUEST_ID irrespective of user or time).Is there any way to accomplish this in the Control file itself??
I tried it like this::

LOAD DATA
APPEND
INSERT INTO staging_table
(v_id,
file_name,
created_by "fnd_global.user_id",
request_id "fnd_profile.conc_request_id"
)

when i used this iam getting -1 in place of request_id column.
Is there any solution for this!!
I would really appreciate you if u can show me the way.
Thanx in advance.
Re: SQL * Loader usage in differentiating between the files [message #258928 is a reply to message #258893] Mon, 13 August 2007 16:02 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If 'created_by' looks OK but 'request_id' doesn't, I'd look at 'fnd_profile.conc_request_id'. How does it look like? What does it do? Could you post it here?
Re: SQL * Loader usage in differentiating between the files [message #260055 is a reply to message #258928] Fri, 17 August 2007 05:21 Go to previous messageGo to next message
gsvd_nagesh
Messages: 4
Registered: August 2007
Junior Member
i never said tht CREATED_BY is OK. Even this field also wil b populated as -1
Re: SQL * Loader usage in differentiating between the files [message #260068 is a reply to message #260055] Fri, 17 August 2007 06:07 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
True. All you have said was
gvsd_nagesh
when i used this iam getting -1 in place of request_id column

Now a little bit of me (again):
LF
I'd look at 'fnd_profile.conc_request_id'. How does it look like? What does it do? Could you post it here?
Re: SQL * Loader usage in differentiating between the files [message #290636 is a reply to message #258716] Sun, 30 December 2007 23:54 Go to previous message
setushah
Messages: 1
Registered: December 2007
Junior Member
I am also facing the same problem. I tried using "FND_GLOBAL.CONC_REQUEST_ID" but no success.

Let me know if you have got any solution.
Previous Topic: Control file
Next Topic: Data loading
Goto Forum:
  


Current Time: Sun Dec 11 06:26:17 CST 2016

Total time taken to generate the page: 0.09082 seconds