Home » RDBMS Server » Server Utilities » SQL Loader and control file changes for different users
SQL Loader and control file changes for different users [message #376630] Thu, 18 December 2008 02:03 Go to next message
coolguy01
Messages: 64
Registered: August 2006
Member
I the front end of my application I can select a data file and the control file and load data to the table mentioned in .ctl file. Every user who logs in uses the same .ctl file and so loads onto the same table. Now I want the user to load data onto the table in his own schema. I can get the username of the user currently logged in and i want to insert it into that username.table. So can i copy the contents of the .ctl file into a variable, modify it into username.table in that string and pass that variable as a parameter to the sqlldr command instead of the .ctl file.
Or is there a better way how I can modify the same control file everytime to change tablename to username.tablename in .ctl file and pass to sqlldr to load data to table in local user schema table.

Thanks and Regards
Re: SQL Loader and control file changes for different users [message #376631 is a reply to message #376630] Thu, 18 December 2008 02:09 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Let the user log in using his own account and don't use a schema name in the ctl
Re: SQL Loader and control file changes for different users [message #376656 is a reply to message #376631] Thu, 18 December 2008 03:25 Go to previous messageGo to next message
coolguy01
Messages: 64
Registered: August 2006
Member
Yes the user has user credentials but its only for the application. once the user is in the application from one of the screens he has to explicitly select a data file and a ctl file. So all the users select the same ctl file and the data goes intot he same table. But i want the dta to inserted in his schema .. ie.e in control file it has to username.tablename just instead of username.
Re: SQL Loader and control file changes for different users [message #376657 is a reply to message #376656] Thu, 18 December 2008 03:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But i want the dta to inserted in his schema ..

So once again let them connect with their user account.

Regards
Michel
Re: SQL Loader and control file changes for different users [message #376820 is a reply to message #376656] Thu, 18 December 2008 12:43 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
coolguy01 wrote on Thu, 18 December 2008 10:25
ie.e in control file it has to username.tablename just instead of username.

Why do you insist on using an explicit schema name?
If you omit it, Oracle will make sure to pick the correct table:
- users table
- if no user table found, then users synonym translation
- if no user synonym found, then public synonym translation.

Now, if you would run sqlloader as the logged in user (i.e. use his own credentials), and create a public synonym for the shared table, all will become right as you can see.
Previous Topic: dbms_datapump - data_filter not working
Next Topic: export dump striping
Goto Forum:
  


Current Time: Sun Dec 04 20:24:51 CST 2016

Total time taken to generate the page: 0.04881 seconds