Connecting to Oracle

[Adam Martin-7]

Hi all

I have browsed the archives and haven't found what I'm looking for. Currently I pull all my data in through SAS - I would like to do this in SPSS now.

Here is the SAS syntax:
****
PROC SQL;
CONNECT TO ORACLE (path='something.something.net' user='user' password='****');
CREATE TABLE DATA AS SELECT * FROM CONNECTION TO ORACLE
(
INSERT SQL HERE
)
;
DISCONNECT FROM ORACLE;
QUIT;
***

What would the SPSS equivalent be of that?

Thanks!

Adam

[Marks, Jim]

Something like this:

 

GET DATA

  /TYPE=ODBC

  /CONNECT='DSN=dbase;UID=me;PWD=******;Host=port.computer.com;Port=3827;SN='schema.computer.com'

  /SQL=' SELECT field1 '

     'FROM table  '

  'WHERE (ID >= 1069 AND ID <= 1072)  '

  /ASSUMEDSTRWIDTH=50.

CACHE.

--jim

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Adam
Sent: Tuesday, August 11, 2009 2:09 AM
To: [hidden email]
Subject: Connecting to Oracle

 

Hi all

I have browsed the archives and haven't found what I'm looking for. Currently I pull all my data in through SAS - I would like to do this in SPSS now.

Here is the SAS syntax:
****
PROC SQL;
CONNECT TO ORACLE (path='something.something.net' user='user' password='****');
CREATE TABLE DATA AS SELECT * FROM CONNECTION TO ORACLE
(
INSERT SQL HERE
)
;
DISCONNECT FROM ORACLE;
QUIT;
***

What would the SPSS equivalent be of that?

Thanks!

Adam

[Daniel Robertson]

Try GET DATA with the subcommand  /TYPE=ODBC. See the GET DATA section
of the syntax guide for details -- the format of the /CONNECT subcommand
will depend on how your connection to the Oracle server is set up (e.g.,
local client or not). Note that the syntax guide suggests the following:
"The recommended method for generating a valid CONNECT specification is
to initially use the Database Wizard and paste the resulting syntax to a
syntax window in the last step of the wizard."

Adam wrote:

> Hi all
>
> I have browsed the archives and haven't found what I'm looking for.
> Currently I pull all my data in through SAS - I would like to do this
> in SPSS now.
>
> Here is the SAS syntax:
> ****
> PROC SQL;
> CONNECT TO ORACLE (path='something.something.net
> <http://something.something.net>' user='user' password='****');
> CREATE TABLE DATA AS SELECT * FROM CONNECTION TO ORACLE
> (
> INSERT SQL HERE
> )
> ;
> DISCONNECT FROM ORACLE;
> QUIT;
> ***
>
> What would the SPSS equivalent be of that?
>
> Thanks!
>
> Adam

--
Daniel Robertson
Senior Research and Planning Associate
Institutional Research and Planning
Cornell University / irp.cornell.edu

=====================
To manage your subscription to SPSSX-L, send a message to
[hidden email] (not to SPSSX-L), with no body text except the
command. To leave the list, send the command
SIGNOFF SPSSX-L
For a list of commands to manage subscriptions, send the command
INFO REFCARD

[Roberts, Michael]

Hi Adam,

 

Have you set up your Oracle ODBC drivers to access the db?  If you are using a Windows environment, it should be relatively easy to do. 

 

This is the code I use:

INCLUDE FILE=’path etc’.  // This is a call to a macro that obviates my having to enter a password each time I query //

GET DATA /TYPE=ODBC /CONNECT= !my macro from above /

   SQL = 'SELECT fieldnames etc '+

    'FROM tablename_etc  '+

    'WHERE(selectioncriteria etc)'

/ASSUMEDSTRWIDTH=55.

CACHE.

EXECUTE.

 

HTH

 

Mike

 

From: SPSSX(r) Discussion [mailto:[hidden email]] On Behalf Of Adam
Sent: Tuesday, August 11, 2009 3:09 AM
To: [hidden email]
Subject: Connecting to Oracle

 

Hi all

I have browsed the archives and haven't found what I'm looking for. Currently I pull all my data in through SAS - I would like to do this in SPSS now.

Here is the SAS syntax:
****
PROC SQL;
CONNECT TO ORACLE (path='something.something.net' user='user' password='****');
CREATE TABLE DATA AS SELECT * FROM CONNECTION TO ORACLE
(
INSERT SQL HERE
)
;
DISCONNECT FROM ORACLE;
QUIT;
***

What would the SPSS equivalent be of that?

Thanks!

Adam