| SQL Loader and control file changes for different users [message #376630] |
Thu, 18 December 2008 02:03  |
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 #376656 is a reply to message #376631] |
Thu, 18 December 2008 03:25  |
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 #376820 is a reply to message #376656] |
Thu, 18 December 2008 12:43 |
Frank
Messages: 7901
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
