Fnd_Global.User_ID [message #254698] |
Fri, 27 July 2007 10:08 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
Safeeq.S
Messages: 100 Registered: October 2005 Location: Bangalore
|
Senior Member |
![saffeq%40yahoo.com](/forum/theme/orafaq/images/yahoo.png)
|
|
Hi,
I am trying to load a temp table through Oracle applications system and the program is completed with error. I am trying to assing the user_id of the user to the WHO column "Created_By" which is causing the problem.
control File :
--------------
LOAD DATA
APPEND
INTO TABLE exl_vendor_automation
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
vendor_number
,vendor_site_code
,vendor_name
,Created_By "fnd_global.user_id"
)
any help would highly appreciated.
Error Message:
SQL*Loader-930: Error parsing insert statement for column EXL_VENDOR_AUTOMATION.CREATED_BY.
ORA-00984: column not allowed here
/u01/app/oracle/pptappl/exel/1.0.0/bin/EXLVENDORSH
Program exited with status 1
Thanks
Safeeq
|
|
|
Re: Fnd_Global.User_ID [message #254714 is a reply to message #254698] |
Fri, 27 July 2007 11:54 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/72104.gif) |
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
I don't know Oracle Apps so excuse my ignorance. What is "fnd_global.user_id" ? Is it an Apps built-in function which returns user ID? Well, whatever it is, it isn't allowed here.
Now, let me guess and make my own test case. It is probably not the one you use, but perhaps you'll find something useful in here.
I suppose that users are stored in a table (their ID and name). Is it, perhaps, ALL_USERS? Let's assume it is! Now we could create a function (stored in the database) which would return user_id depending on username:CREATE OR REPLACE FUNCTION fun_user RETURN NUMBER IS
l_id NUMBER;
BEGIN
SELECT user_id INTO l_id
FROM all_users
WHERE username = (SELECT USER FROM dual);
RETURN l_id;
END;
/
Input sample file might look like this (note that USER_ID column is empty):10;13;Pero
20;55;Tina
30;23;Jurica
40;22;Diana
Control file (note function we use, "FUN_USER"):load data
infile 'test.txt'
badfile 'test.bad'
discardfile 'test.dsc'
replace
into table exl_vendor_automation
fields terminated by ";"
trailing nullcols
(vendor_number,
vendor_site_code,
vendor_name,
created_by "fun_user"
)
Running the SQL*Loader gives such a result:C:\TEMP>sqlldr scott/tiger control=test.ctl
SQL*Loader: Release 10.2.0.1.0 - Production on Pet Srp 27 18:48:29 2007
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Commit point reached - logical record count 3
Commit point reached - logical record count 4
C:\TEMP> Testing what we've done:C:\TEMP>sqlplus scott/tiger
SQL> select * from exl_vendor_automation;
VENDOR_NUMBER VENDOR_SITE_CODE VENDOR_NAME CREATED_BY
------------- ---------------- -------------------- ----------
10 13 Pero 54
20 55 Tina 54
30 23 Jurica 54
40 22 Diana 54
SQL> select * from all_users where username = 'SCOTT';
USERNAME USER_ID CREATED
------------------------------ ---------- --------
SCOTT 54 30.08.05
SQL>
So, it seems that USER_ID found its way into the CREATED_BY column.
As I've already said, this might not work for you, but ... perhaps I was lucky today.
|
|
|
|
|
|