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

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.

bp,

you do stash old posts! :)

-oj

oj,

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

BPM

then,how to execute an oracle store procedure from the SQL SERVER analyser?

    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.

    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. )