Oracle Connection

  • Thread starter Thread starter Bryan
  • Start date Start date
B

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
 
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.
 
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
 
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' ;
 
Back
Top