Home » Developer & Programmer » Forms » Inserting into schema of currently logged in user
Inserting into schema of currently logged in user [message #377435] Tue, 23 December 2008 01:01 Go to next message
coolguy01
Messages: 64
Registered: August 2006
Member
Hi ,
I am trying to insert into table based on user currently logged into application. So i get the username currently logged in into a global variable called :GLOBAL.user_name. SO where ever there is a insert done into any table it should be done as
insert into :GLOBAL.user_name.table ..... to insert into table in his schema.

For example if temp user is logged in the value of :GLOBAL.username will be temp. so when he does a insert it should get inserted in temp.table. So how do i dynamically substitute the schema name depending on whichever user is logged in. Would just a insert into :GLOBAL.user_name.table substitute the value of the variable or has the value be executed and substituted doing some manipulation.

Thanks and regards
Re: Inserting into schema of currently logged in user [message #377445 is a reply to message #377435] Tue, 23 December 2008 01:50 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
This may help: Dynamic SQL.
Re: Inserting into schema of currently logged in user [message #377455 is a reply to message #377445] Tue, 23 December 2008 03:03 Go to previous messageGo to next message
coolguy01
Messages: 64
Registered: August 2006
Member
Do u mean i could use the insert statement like this

sql_stmt := 'INSERT INTO :1.table VALUES (val1, val2, val3)';
EXECUTE IMMEDIATE sql_stmt USING :GLOBAL.user_name;
Re: Inserting into schema of currently logged in user [message #377467 is a reply to message #377455] Tue, 23 December 2008 03:19 Go to previous messageGo to next message
Frank Naude
Messages: 4502
Registered: April 1998
Senior Member
You can test it, but I doubt if it will work. Look at the DBMS_SQL example a bit lower down.
Re: Inserting into schema of currently logged in user [message #377533 is a reply to message #377467] Tue, 23 December 2008 06:35 Go to previous message
Littlefoot
Messages: 20896
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Maybe I got it wrong, but - when user logs on, he uses credentials: username, password, database. Schema which is "valid" during this logon time is his (this user's) schema. It means that database block is based on a table which belongs to this user. Consequentially, every insert into a table will be done into this user's own table. All by default, you don't have to do anything about it.

When someone else logs on, he'll insert into his own table, and so on.

However, does this mean that EVERY user which uses this application has its own tables? Is that a good idea/design? Wouldn't you rather have a schema owner (which contains all the tables), while every other user is granted to use these tables in a certain manner (selecting, inserting, updating, ...). Data will be stored into the same table, no matter which user does that; you might, however, store username (or some other user ID) so that you'd know who has done that (or even enable database auditing).

Furthermore, you might investigate use of the Virtual Private Database.

Whatever you do, I hope it is/will be different from I understood in the first place. Because, I believe that such a design sucks (pardon my French).
Previous Topic: Problem with list item
Next Topic: How to assign a value after clearing a record
Goto Forum:
  


Current Time: Tue Dec 06 12:32:39 CST 2016

Total time taken to generate the page: 0.13095 seconds