create view in SQL Server from table present in ORACLE

Question

0.00/5 (No votes)

See more:

hi all.

i want to create view (materialzied view) in SQL Server Database, DB tables are present on ORACLE database and on another server.

Scenario is :: my application is using sql server database only. and through application i want to select/update/insert/delete ORACLE Table data.

i tried using granting permissions / creating database link.
i created a Database link in ORACLE .. and using that link i want to creating view... second option tried is GRANT operation...

suppose i am having access only to only one database name "sushil".

which contains a user "UserABC" so i can say third party that create a database link and give it to me for accessing data.....

but i dont know the exact steps..

can anyone help me out.

Thank in Advanced

Regards
Sushilkumar Bondre

Posted 19-Sep-10 0:17am

sushilbondre

Add a Solution

Comments

sushilbondre 19-Sep-10 6:17am

waiting for urgent reply

mslliviu 22-Sep-10 3:26am

I think the first step you have to take is to add the Oracle server to the LinkedServers collection in SQL Server (Server Objects). After that I think you can query Oracle data (don't know what security issues can appear)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

db_developer · Answer 1 · 2010-11-14T02:19:00

The only solution that I can see is to create Stored Procedures with aid of C# CLR in SQL Server, and to perform select/update/insert/delete inside these procedures using ODBC/OLE db connection to Oracle.
Or you may even create assembly for SQL Server to wrap the whole ADO.Net functions' batch: execute, executeScalar, executeNonScalar, and keep connection to Oracle as well.