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
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;
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
);
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 - 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...
It might be necessary to ask Tech support if no one here knows.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.