CODEWITHSUNDEEP
oracle-databaseoracle-apex
Martins B.
Martins B.

Reputation: 3

:APP_USER usage in SQL query. Oracle Application Express (Apex) 5.0.4

I want to use session variable :APP_USER in query in selection database statement like this: 

select * from :APP_USER.data

I have users john.doe and johny.b. I have table john.doe.data and i want to get all data from this table. Also i have table johny.b.data and when johny.b will login in, I want to get data from table johny.b.data.

I hope you understand my plan, so it is like every user have own data table and I want to display table according to logged in user. What will be the right way to do this?

Upvotes: 0

Views: 5720

Answers (1)

bob dylan
bob dylan

Reputation: 1498

I would say this would be possible but shouldn't be done. You'd be better off doing select * from apex_user.table (prefix not needed) where column = :APP_USER and having them all in one big table or having a different table (but same apex_schema) per user. How you'd go about creating this table is up to you - you could select a pseudo-table from dual and then only create it when necessary to prefent any table not found issues.

You'll no doubt run into tablespace permission issues down the line or worse - give the apex user more security permissions than it requires if you go down your intended route which will make exporting and importing a nightmare.

Upvotes: 1

Related Questions