Oracle Connection

[Bryan]

I have an Oracle connection working but I need to specify the owner or schema
(not sure what). Is there any way to specify it in the connection string?

If I run this query, I get a Table or View does not exist error.

SELECT STUDENT.* FROM STUDENT WHERE STUDENT.SSN='XXX'

But if I specify the table in the query it works.

SELECT STUDENT.* FROM FAIS_Prod.STUDENT WHERE STUDENT.SSN='XXX'

Is there any way to specify the FAIS_Prod in my query connection string so
that when I move from DEV to PROD, I won't have to update the SQL too?

ODBC;Description=SO;DRIVER={Microsoft ODBC for
Oracle};SERVER=FAIS;Uid=readonly;Pwd=XXX;

I can update the querydef but would rather have this work if possible.

Bryan

 

[Jerry Whittle]

The owner and schema in Oracle are for all practical purposes, the same thing.

Did you misspeak about FAIS_Prod being a table? By your SQL statement, it
looks more like the owner/schema.

You could ask the Oracle DBA to make a synonym of Student for the
FAIS_Prod.Student table in both database instances.

 

[Bryan]

Sorry,

I did misspeak about the table. I should have said the owner/schema of the
table.

Having a synonym created would take too long and delay current progress -
it's another department's larger scale app and we're the red-headed
step-child.

I was hoping there was a field that would specify the owner/schema in the
Connection String.


Bryan

 

[Jerry Whittle]

I don't know if this will help, but it will tell you who owns the table
STUDENT in Oracle.

select owner
from sys.all_tables
where table_name = 'STUDENT' ;