SQL Server and Oracle 
Author Message
 SQL Server and Oracle
I am trying to connect a SQL Server 7 db to an Oracle db.  My goal is to
read data from Oracle tables/views without moving the data to SQL Server
tables - similar to a 'linked' table in Access.

any help would be appreciated.

thanks
tony



Sat, 22 May 2004 05:48:52 GMT
 SQL Server and Oracle

Tony,

* TIP: SETTING UP AN ORACLE LINKED SERVER

Q. When I try to add an Oracle server to a local machine on which SQL
Server is installed, the action fails. Here are the parameters for my
machine:

General:
server:\\bao\cjf(\\domain\machine name)
product name:oracle
data source:msdaora
provider:proview(alias)

Security:
local login:cjf(sa)
remote login:internal/oracle

Doesn't SQL Server support a linked server on a local machine?

A. As SQL Server Books Online (BOL) states, you can use the Microsoft
OLE DB Provider for Oracle to query data in Oracle databases. BOL
specifies several requirements for this provider to work. Review these
guidelines to make sure you've met them. For example, the OLE DB
Provider for Oracle requires Oracle Client Software Support File
version 7.3.3.4.0 or later and SQL*Net version 2.3.3.0.4. BOL tells you
how to create an SQL*Net alias name and create mappings from SQL Server
logins to Oracle logins. BOL also gives you some guidelines for how to
reference Oracle database instances and how to reference tables in an
Oracle linked server. According to these guidelines, to set up your
Oracle linked server, you'd run

exec sp_addlinkedserver 'localOracle', 'Oracle', 'MSDAORA', 'proview'
--Thus, SQL Server would call the server localOracle and
  SQL*NET would reference the server as proview.

exec sp_addlinkedsrvlogin 'localOracle', false, 'sa', 'internal',
'oracle'
--Log in to SQL Server as sa, which will match to an internal account
on Oracle.

Then, log in to SQL Server as the sa user and run a distributed query
that references

SELECT * FROM localOracle..Schema.Table

-------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:
> I am trying to connect a SQL Server 7 db to an Oracle db.  My goal is to
> read data from Oracle tables/views without moving the data to SQL Server
> tables - similar to a 'linked' table in Access.

> any help would be appreciated.

> thanks
> tony



Sat, 22 May 2004 08:05:55 GMT
 SQL Server and Oracle
bp,

you do stash old posts! :)

-oj


Quote:
> Tony,

> * TIP: SETTING UP AN ORACLE LINKED SERVER

> Q. When I try to add an Oracle server to a local machine on which SQL
> Server is installed, the action fails. Here are the parameters for my
> machine:

> General:
> server:\\bao\cjf(\\domain\machine name)
> product name:oracle
> data source:msdaora
> provider:proview(alias)

> Security:
> local login:cjf(sa)
> remote login:internal/oracle

> Doesn't SQL Server support a linked server on a local machine?

> A. As SQL Server Books Online (BOL) states, you can use the Microsoft
> OLE DB Provider for Oracle to query data in Oracle databases. BOL
> specifies several requirements for this provider to work. Review these
> guidelines to make sure you've met them. For example, the OLE DB
> Provider for Oracle requires Oracle Client Software Support File
> version 7.3.3.4.0 or later and SQL*Net version 2.3.3.0.4. BOL tells you
> how to create an SQL*Net alias name and create mappings from SQL Server
> logins to Oracle logins. BOL also gives you some guidelines for how to
> reference Oracle database instances and how to reference tables in an
> Oracle linked server. According to these guidelines, to set up your
> Oracle linked server, you'd run

> exec sp_addlinkedserver 'localOracle', 'Oracle', 'MSDAORA', 'proview'
> --Thus, SQL Server would call the server localOracle and
>   SQL*NET would reference the server as proview.

> exec sp_addlinkedsrvlogin 'localOracle', false, 'sa', 'internal',
> 'oracle'
> --Log in to SQL Server as sa, which will match to an internal account
> on Oracle.

> Then, log in to SQL Server as the sa user and run a distributed query
> that references

> SELECT * FROM localOracle..Schema.Table

> -------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > I am trying to connect a SQL Server 7 db to an Oracle db.  My goal is to
> > read data from Oracle tables/views without moving the data to SQL Server
> > tables - similar to a 'linked' table in Access.

> > any help would be appreciated.

> > thanks
> > tony



Sat, 22 May 2004 08:11:29 GMT
 SQL Server and Oracle
oj,

Shhhh ... I don't want the Feds to know about my stash    :-)

BPM


Quote:
> bp,

> you do stash old posts! :)

> -oj



> > Tony,

> > * TIP: SETTING UP AN ORACLE LINKED SERVER

> > Q. When I try to add an Oracle server to a local machine on which SQL
> > Server is installed, the action fails. Here are the parameters for my
> > machine:

> > General:
> > server:\\bao\cjf(\\domain\machine name)
> > product name:oracle
> > data source:msdaora
> > provider:proview(alias)

> > Security:
> > local login:cjf(sa)
> > remote login:internal/oracle

> > Doesn't SQL Server support a linked server on a local machine?

> > A. As SQL Server Books Online (BOL) states, you can use the Microsoft
> > OLE DB Provider for Oracle to query data in Oracle databases. BOL
> > specifies several requirements for this provider to work. Review these
> > guidelines to make sure you've met them. For example, the OLE DB
> > Provider for Oracle requires Oracle Client Software Support File
> > version 7.3.3.4.0 or later and SQL*Net version 2.3.3.0.4. BOL tells you
> > how to create an SQL*Net alias name and create mappings from SQL Server
> > logins to Oracle logins. BOL also gives you some guidelines for how to
> > reference Oracle database instances and how to reference tables in an
> > Oracle linked server. According to these guidelines, to set up your
> > Oracle linked server, you'd run

> > exec sp_addlinkedserver 'localOracle', 'Oracle', 'MSDAORA', 'proview'
> > --Thus, SQL Server would call the server localOracle and
> >   SQL*NET would reference the server as proview.

> > exec sp_addlinkedsrvlogin 'localOracle', false, 'sa', 'internal',
> > 'oracle'
> > --Log in to SQL Server as sa, which will match to an internal account
> > on Oracle.

> > Then, log in to SQL Server as the sa user and run a distributed query
> > that references

> > SELECT * FROM localOracle..Schema.Table

> > -------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > I am trying to connect a SQL Server 7 db to an Oracle db.  My goal is
to
> > > read data from Oracle tables/views without moving the data to SQL
Server
> > > tables - similar to a 'linked' table in Access.

> > > any help would be appreciated.

> > > thanks
> > > tony



Sat, 22 May 2004 08:55:19 GMT
 SQL Server and Oracle
then,how to execute an oracle store procedure from the SQL SERVER analyser?



Quote:
> I am trying to connect a SQL Server 7 db to an Oracle db.  My goal is to
> read data from Oracle tables/views without moving the data to SQL Server
> tables - similar to a 'linked' table in Access.

> any help would be appreciated.

> thanks
> tony



Tue, 01 Jun 2004 16:42:13 GMT
 SQL Server and Oracle
    You have to use OPENQUERY to execute the SP. However, OPENQUERY expects
a result set from the pass-through query. Here is one sample that I posted
last year.

You can do it using a Package with PL/SQL tables & the {resultset}
escape syntax of ODBC / OLE-DB provider for Oracle. Here is one example:

/* Run in Oracle */
CONNECT SCOTT/TIGER
/
CREATE PACKAGE WrapPack
AS
TYPE ReturnTbl IS TABLE OF NUMBER
/* The index is important, otherwise {resultset} doesn't work. */
INDEX BY BINARY_INTEGER;
PROCEDURE WrapPackSP
(
ReturnVal OUT ReturnTbl
);
END WrapPack;
/
CREATE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
ReturnVal OUT ReturnTbl
)
IS
Begin
/* Call some other SP, just for demo */
/* Test; */
/* This is just a dummy return value. */
/*
This method can be used to return result sets from
Oracle SPs to SQL Server via ODBC / OLE-DB driver's
{resultset} escape syntax.
*/
ReturnVal( 1 ) := 0;
End WrapPackSP;
End WrapPack;
/
/* End of Oracle Routines */

/* Do this in SQL Server */
SELECT * FROM OPENQUERY( Oracle_Srvr ,
'{Call SCOTT.WrapPack.WrapPackSP( {resultset 1 , ReturnVal} )'
)

    So this way, you can return a result set to T-SQL. The Oracle cursor
approach will work fine from a host language like C, C++ or Java etc. For
linked server setup in SQL Server, see BOL topics.

Quote:
>> Can I put something like select * from sometable where the
>> 'Test;' message is?

    No, you can't do this in Oracle. SELECT statement by itself should have
an IN clause. Returning result sets imho is a pain in Oracle & it depends on
the language/api you are using. SQL Server makes it so easy. AFAIK, you can
only return results via cursors, pl/sql tables, arrays as output parameters.

--> How am I going to return resultset based on a select
--> statement at this point?

    Please check the PL/SQL docs on PL/SQL tables. Here is one sample using
my example & this returns the list of employees, ID from SCOTT.EMP table.
Please check the syntax - it has been a while since I coded considerably in
Oracle.

CREATE PACKAGE WrapPack
AS
TYPE EmpID IS TABLE OF NUMBER
/* The index is important, otherwise {resultset} doesn't work. */
INDEX BY BINARY_INTEGER;
TYPE EmpName IS TABLE OF VARCHAR2( 30 )
INDEX BY BINARY_INTEGER;
PROCEDURE WrapPackSP
(
NameLike IN varchar2
EID OUT EmpID
EName OUT EmpName
);
END WrapPack;
/
CREATE PACKAGE BODY WrapPack
AS
PROCEDURE WrapPackSP
(
NameLike IN varchar2
EID OUT EmpID
EName OUT EmpName
)
IS
    EmpCount NUMBER DEFAULT 1;
    CURSOR EmpCur IS
        SELECT EmpNo , EName FROM SCOTT.EMP
        WHERE EName LIKE NameLike;
BEGIN
    FOR Emp IN EmpCur
    LOOP
        EmpID( EmpCount ) := Emp.EmpNo;
        EmpName( EmpCount ) := Emp.EName;
        EmpCount := EmpCount + 1;
    END LOOP;
END WrapPackSP;
END WrapPack;
/

    The above is one standard way to use PL/SQL tables. There may be other
ways of doing this. You call this from T-SQL using OPENQUERY like:

SELECT * FROM OPENQUERY( Oracle_Srvr ,
'{Call Scott.WrapPack.WrapPackSP( ''J%'', {resultset 20, eid, ename})}' )

    This returns all employees whose name start with 'J%'. To pass
parameters using variables from T-SQL, the entire statement has to be formed
dynamically & executed. There are several threads about this.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )



Thu, 03 Jun 2004 01:05:27 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. GA-Atlanta-98073--DBA Skills-MS SQL Server-ORACLE-Database Administrator (DBA)-SQL Server 6.5 / Oracle

2. GA-Atlanta-98073--DBA Skills-MS SQL Server-ORACLE-Database Administrator (DBA)-SQL Server 6.5 / Oracle

3. GA-Atlanta-97392--DBA Skills-MS SQL Server-ORACLE-Database Administrator (DBA)-SQL Server 6.5/Oracle

4. GA-Atlanta-98073--DBA Skills-MS SQL Server-ORACLE-Database Administrator (DBA)-SQL Server 6.5 / Oracle

5. sql server vs sybase sql server vs oracle

6. MD-Baltimore-86692--ORACLE-ORACLE Tools-MS SQL Server-DBA-Oracle or SQL

7. SQL Server to Oracle (Transact SQL to PL/SQL)

8. Linked Server from SQL Server to Oracle

9. SQL Server and Oracle Server

10. SQL Server with Oracle on the Same Server

11. SQL Server and Oracle on same server?

12. SQL Server have Oracle Parallel Server capability


 
Powered by phpBB® Forum Software