August 6, 2011 at 1:01 pm
Greetings all. I just realized there is an express version of Oracle, so I thought "why not get it?". I was able to successfully set up a linked server on my SQL Server 2008 machine (after much, much grumbling), and I can see the system tables in the default table catalog. On the Oracle box, I created a table, gave myself a login, and granted the login select permissions on the table. However, I am unable to see it from SQL Server. I know absolutely nothing about Oracle, and it seems I am missing something simple. This works...
SELECT * FROM MYA..FLOWS_020100.WWV_FLOW_DUAL100
Where 'MYA' is my linked server. Is 'FLOWS_020100' a schema? Do I need to create a database on the Oracle box first?
When I look in the Oracle object browser, I see my table, called 'test', but no dice in the catalog on SQL Server. Can anyone offer any tips? Thank you.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 8, 2011 at 5:31 am
Greg Snidow (8/6/2011)
Greetings all. I just realized there is an express version of Oracle, so I thought "why not get it?". I was able to successfully set up a linked server on my SQL Server 2008 machine (after much, much grumbling), and I can see the system tables in the default table catalog. On the Oracle box, I created a table, gave myself a login, and granted the login select permissions on the table. However, I am unable to see it from SQL Server. I know absolutely nothing about Oracle, and it seems I am missing something simple. This works...
SELECT * FROM MYA..FLOWS_020100.WWV_FLOW_DUAL100
Where 'MYA' is my linked server. Is 'FLOWS_020100' a schema? Do I need to create a database on the Oracle box first?
When I look in the Oracle object browser, I see my table, called 'test', but no dice in the catalog on SQL Server. Can anyone offer any tips? Thank you.
The Oracle concept of database is pretty different from the SQL Server concept of it, in simple terms - if you have an Oracle instance up and running you have a database.
Which Oracle account (user id) are you using to connect to the Oracle database, is it the same one that created/owns the target table?
Do you have an Oracle client installed anywhere so to validate access/credentials via SQLPLUS?
Finaly ... if the table name is TEST why are you selecting from WWV_FLOW_DUAL100?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.August 8, 2011 at 8:12 am
The Oracle concept of database is pretty different from the SQL Server concept of it, in simple terms - if you have an Oracle instance up and running you have a database.
Thank you Paul, I was only thinking in terms of SQL Server.
Which Oracle account (user id) are you using to connect to the Oracle database, is it the same one that created/owns the target table?
It is not the same. I created the table first, with the default account. I later created a user account and password, and used this account when setting up the linked server.
Do you have an Oracle client installed anywhere so to validate access/credentials via SQLPLUS?
I'm not sure what you mean by SQLPLUS. I installed the full database software on both the client and server first, then created a DSN on the client, and created the linked server, only after fiddling with the tsanames.ora file. At this point, I'm not sure if my DSN is even doing anything. I guess I could delete it and see what happens. Once I got the linked server to connect, I un-installed the database software from the client, leaving only the Client connect app, and the linked server still worked. And when I say 'worked', I mean I am able to connect to it and return data. From the client, I am able to ping the host, and tnsping the host.
Finaly ... if the table name is TEST why are you selecting from WWV_FLOW_DUAL100?
I did this only to demonstrate that I am able to connect to the host, and return data using the credentials I provided.
You hit the nail right on the head. I need to understand how Oracle works first. I tried so much stuff setting it up that I am now unsure what the critical steps were in. Does the tsanames.ora file need to be the same on both the client and the server?
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
August 8, 2011 at 9:52 pm
Um... am I right in thinking that Oracle Express is not the same as the usual Oracle RDBMS? I vaguely remember something about that, but I haven't looked at Oracle Express in some time, so it may no longer be the case (or I may have gotten it completely wrong).
Anyway, I'm not sure which tools Oracle Express ships with. Somewhere it will have some sort of query tool (otherwise how did you create your table?) Can you connect to the Oracle Express database as your new user using the Oracle query tool, and see the table there? That should verify that the connections are correct.
Also, is SQL Server running on the same physical machine as the Oracle instance? If not, you will still need to load an Oracle client on the server. I suspect they're both running on your PC, but pays to check these things.
When you have the linked server set up, you should be able to browse it and see if there's anything under there.
Where I have had issues in the past is this:
In Oracle, create table A under schema X. Grant select on A to user Y.
In SQL Server, set up the linked server using user Y to connect to Oracle.
The name of the table will be Oracle..X.A (NOT Oracle..Y.A), as Y has select permissions, but the table is still under the X schema. That can get a bit confusing!
August 12, 2011 at 7:56 am
Greg Snidow (8/8/2011)
The Oracle concept of database is pretty different from the SQL Server concept of it, in simple terms - if you have an Oracle instance up and running you have a database.
Thank you Paul, I was only thinking in terms of SQL Server.
Which Oracle account (user id) are you using to connect to the Oracle database, is it the same one that created/owns the target table?
It is not the same. I created the table first, with the default account. I later created a user account and password, and used this account when setting up the linked server.
Do you have an Oracle client installed anywhere so to validate access/credentials via SQLPLUS?
I'm not sure what you mean by SQLPLUS. I installed the full database software on both the client and server first, then created a DSN on the client, and created the linked server, only after fiddling with the tsanames.ora file. At this point, I'm not sure if my DSN is even doing anything. I guess I could delete it and see what happens. Once I got the linked server to connect, I un-installed the database software from the client, leaving only the Client connect app, and the linked server still worked. And when I say 'worked', I mean I am able to connect to it and return data. From the client, I am able to ping the host, and tnsping the host.
Finaly ... if the table name is TEST why are you selecting from WWV_FLOW_DUAL100?
I did this only to demonstrate that I am able to connect to the host, and return data using the credentials I provided.
You hit the nail right on the head. I need to understand how Oracle works first. I tried so much stuff setting it up that I am now unsure what the critical steps were in. Does the tsanames.ora file need to be the same on both the client and the server?
Greg --
The key in this particular case is that the account used to connect via linked server is not the one that onws the table on the Oracle side.
Let's say that the fully qualified name of the table in Oracle side is (schema_name.table_name) user-a.mytable while linked server is using user-b account. In this particular scenario mytable was created by user-a, the owning schema.
For user-b to be able to "see" and access the table at least "select" privileges have to be granted on the target table, to do this...
1 Log into the Oracle instance either as user-a or a user with DBA privileges.
2 Grant privs on table by running: grant select on user-a.mytable to user-b;
Hope this helps
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply