Is there a User ID trick that would preface Oracle table names with the User ID

The tables residing on an Oracle server appear from within acces to have the user id as a preface.  The schema log files I was given (captured from a Unix workstation) do not include the user id prefix.  
Is Unix or Oracle adding the user id as a result of a driver or setup?
Is there a way to have Access add that user id?

For example, the following from clause shows the prefix issue.
FROM EMAC_C_TYPE    ' the Unix log file shows this
FROM APPLIX.EMAC_C_TYPE   ' This form is needed using Access as the frontend where APPLIX is the user ID

If I don't add the user id to the table names, Oracle does no recognize the table.  In most cases Oracle returns that there are more than one table by that name.  
MikeySampleAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

23637269Commented:
Are you talking about the UserID for the person logged into windows, or the UserID of a user that exists within the table of a database?

If you store the UserID to a variable say lUserID then you can concatinate it to any existing field in the existing table.

If the application auto generates the table with the User's ID Number then your SQL statement could be something like:"Select * From " & lUserID & ".EMAC_C_TYPE"
You would need to store the User's ID Number to the lUserID variable before you execute the SQL statement.

Thanks
Roger
0
Jinesh KamdarCommented:
>> If I don't add the user id to the table names, Oracle does no recognize the table
Are you accessing these tables from the same schema in which they reside ?

>> In most cases Oracle returns that there are more than one table by that name.  
You mean tables with same names in different schemas? If yes, then prepend the schema identifier to ensure that u select from the right table.

Your above 2 statements are actually quite contradictory to each other.
Pls explain further, preferably with an example.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
MikeySampleAuthor Commented:
23637269:
I don't want to manual add such variables because there are unique schema for hundreds of part catagories.  I am looking at thousands for different query statements to cover all the part type possibilities.
0
MikeySampleAuthor Commented:
jinesh_kamdar,
 Keep in mind I have worked on computers since 1971, but sending sSQL directly to an Oracle database is very new.  I am an Industrial Engineer struggling to write a frontend in Access to automate our new database User Acceptance Testing.

Regarding, "Are you accessing these tables from the same schema in which they reside", I believe the answer would be Yes.

I am querying a view rather than permanent tables.

regarding "You mean tables with same names in different schemas? If yes, then prepend the schema identifier to ensure that u select from the right table".

I believe yes.  About this prepend the schema identifier, can that be done outside the SQL statement? That might be what is happening on the Unix workstations.

The DBAs are addressing this Oracle database from a Unix workstation.  They tell me they don't need to include the user id.  Oracle apparently does that for them as a result of logging into the Oracle connection.  

When I log into the Oracle server from Access, while entering the same user id and password, I must include the user id for each viewtable in the query statement.

0
johnsoneSenior Oracle DBACommented:
You need to find out who they are logged in as from the workstation.

If you are logged in from Access as the owner of the table, then there is no need to put the owner before the object name, Oracle will always resolve names in the current schema.

As to the question of whether you can do this outside the SQL statement, the answer is yes.  You need to create synonyms.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.