Connecting from SAS pass-through to Oracle Data Management Workbench

[PerBundgaard]

Hi all,

We are migrating from Oracle Clinical to Oracle Data Management workbench

I need to connect pass-through from SAS to and Oracle Data Management Workbench (DMW) business area. My problem is that I need to issue both an exec acquire access and a select in the same pass-through session.

proc sql;
connect to oracle as connection_name(path = "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=AAAAAA))
(ADDRESS=(PROTOCOL=TCP)
(HOST=nnn.nnn.nnn.nn)(PORT=nnn)))"
user = "XXX"
password = "YYYY"
);
execute (exec name.name.enable_read('USER')) by connection_name ;
Create table test as
Select *
from connection to oracle
( SELECT * FROM XXXX.YYYY
);
disconnect from connection_name;
quit;

Any experience with connection to Data Management Workbench 

[ChrisNZ]

I don't use  Data Management Workbench, but doesn't something like this work?

execute by connection (

  exec name.name.enable_read('USER');
  create table TEST as
  select *
  from XXXX.YYYY
);

[PerBundgaard]

Hi CrisNZ,
Thanks for your suggestion, but I'm afraid not.
The
execute by connection(
exec name.name.enable_read('USER');)
Doesn't throw Oracle errors

But I can't have Oracle create table with the USER privileges
And I would still need to create table to my SAS WORK library
But Thanks and
Best regards,
Per

[ChrisNZ]

Oh I see. I think.

So you need to read a table while passing the credentials to access that table?

Why not pass these credentials in the connect to oracle string?

[PerBundgaard]

Hi ChrisNZ,
I do pass the credentials in the connect string (user and password).
Then I need to execute a package:
exec XX.yy('User') ;
prior to the select
I can pass the credentials and execute the package without errors from Oracle as follows
proc sql;
connect to oracle as dmw_conn(path = "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=LSHTST))
(ADDRESS=(PROTOCOL=TCP)
(HOST=132.240.154.71)(PORT=1521)))"
user = "User"
password = "Password"
);
execute(exec XX_yy('User')) by dmw_conn ;
quit;
but when I include a select from connection to Oracle in the same proc sql:
proc sql;
connect to oracle as dmw_conn(path = "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=LSHTST))
(ADDRESS=(PROTOCOL=TCP)
(HOST=132.240.154.71)(PORT=1521)))"
user = "User"
password = "Password"
);
Create table test as
Select *
from connection to oracle
(SELECT * FROM zzz.xxx
);
disconnect from dmw_conn;
quit;
I get the
ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error
Thanks for your input

[SASKiwi]

@PerBundgaard  - How about using the SHARED SQL connection option then splitting your EXECUTE and SELECT statements into separate uses of that shared connection - https://documentation.sas.com/?docsetId=acreldb&docsetTarget=n0rn6hhsizv3trn1cl3e0ofosawi.htm&docset...

[PerBundgaard]

Hi SASKiwi,

Sorry for the delayed response, I apparently filed it wrong

Thank you for your suggestion using the connection=shared.
As I read the documentation the 'shared' option is the default, only one connection is established and the connection is shared among SQL pass-through statements.
Not entirely sure I understand the 'global' option in a pass-through setting, but I've tried both in various ways. For example:

proc sql;
connect to oracle as dmw_conn(path = "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=LSHTST))
(ADDRESS=(PROTOCOL=TCP)
(HOST=132.240.154.71)(PORT=1521)))"
user = "User"
password = "password"
connection = shared
);
execute(exec package('User')) by dmw_conn ;
Create table test as
Select *
from connection to oracle
(SELECT * FROM schema.table
);
disconnect from dmw_conn;
quit;
I'm afraid with the same result. I can execute the package pass-through alone but when I try to Select using the same connection in the same proc sql. I get the

ERROR: ORACLE connection error: ORA-12560: TNS:protocol adapter error.

EXEC package('User');
SELECT * FROM schema.table

Works fine in Toad for Oracle

Best Regards,
Per

[ChrisNZ]

It might be necessary to ask Tech support if no one here knows.

[PerBundgaard · Accepted solution]

Solution turned out to be quite simple when I had help from Henrik Dorph SAS Institute DK.

In all this excitement I lost track of how many connections I opened in the explicit pass-through and their alias names.

You can both

execute(...) by conn

and

Select from connection to conn

in the same pass-through. But you must be care full with the connection alias (conn) and when they are 'open'

proc sql;
  connect to oracle as dmw_conn(path = "(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=LSHTST))
                                                                                               (ADDRESS=(PROTOCOL=TCP)
                                                                                                (HOST=132.240.154.71)(PORT=1521)))"
                                                      user = "User"
                                                      password = "Password"
                                                      );
Create table test as
Select *
from connection to dmw_conn
(SELECT * FROM zzz.xxx
);
disconnect from dmw_conn;
quit;