OraFAQ Forum: Server Utilities » Fnd_Global.User

Home » RDBMS Server » Server Utilities » Fnd_Global.User_ID

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

Fnd_Global.User_ID [message #254698]

Fri, 27 July 2007 10:08

Safeeq.S
Messages: 100
Registered: October 2005
Location: Bangalore

Senior Member

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

Report message to a moderator

Re: Fnd_Global.User_ID [message #254714 is a reply to message #254698]

Fri, 27 July 2007 11:54

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

Report message to a moderator

Re: Fnd_Global.User_ID [message #254716 is a reply to message #254714]

Fri, 27 July 2007 12:12

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Just a small correction in function: you don't need "(select user from dual)", "user" is sufficient.

Another way may be to directly use the built-in SYS_CONTEXT function:

SQL> select sys_context('userenv','session_userid') from dual;

SYS_CONTEXT('USERENV','SESSION_USERID')
---------------------------------------------------------------
49

1 row selected.

So I think:

created_by  "SYS_CONTEXT('USERENV','SESSION_USERID')"

should work but I didn't test it.

Regards
Michel

Report message to a moderator

Re: Fnd_Global.User_ID [message #254718 is a reply to message #254716]

Fri, 27 July 2007 12:26

Littlefoot
Messages: 21811
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I tested it for you, Michel, and it works absolutely perfect!
With a minor error on your side - you got USER_ID = 49, but it must be 54 /forum/fa/1587/0/

Report message to a moderator

Re: Fnd_Global.User_ID [message #254720 is a reply to message #254718]

Fri, 27 July 2007 12:56

Michel Cadot
Messages: 68658
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is just because I used MICHEL and not SCOTT. /forum/fa/917/0/

Regards
Michel

[Updated on: Fri, 27 July 2007 12:57]

Report message to a moderator

Previous Topic:	sql loader
Next Topic:	Creating a Batch File

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jun 02 05:54:22 CDT 2024