• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 253
  • Last Modified:

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.  
0
MikeySample
Asked:
MikeySample
2 Solutions
 
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
 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now