OraFAQ Forum: Server Utilities » SQL * Loader usage in differentiating between the files

Home » RDBMS Server » Server Utilities » SQL * Loader usage in differentiating between the files

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL * Loader usage in differentiating between the files [message #258716]

Mon, 13 August 2007 07:58

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.

Report message to a moderator

Re: SQL * Loader usage in differentiating between the files [message #258786 is a reply to message #258716]

Mon, 13 August 2007 10:18

Littlefoot
Messages: 21823
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.

Report message to a moderator

Re: SQL * Loader usage in differentiating between the files [message #258828 is a reply to message #258786]

Mon, 13 August 2007 11:28

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.

Report message to a moderator

Re: SQL * Loader usage in differentiating between the files [message #258836 is a reply to message #258828]

Mon, 13 August 2007 11:42

Littlefoot
Messages: 21823
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).

Report message to a moderator

Re: SQL * Loader usage in differentiating between the files [message #258893 is a reply to message #258836]

Mon, 13 August 2007 14:06

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.

Report message to a moderator

Re: SQL * Loader usage in differentiating between the files [message #258928 is a reply to message #258893]

Mon, 13 August 2007 16:02

Littlefoot
Messages: 21823
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?

Report message to a moderator