DATA Step, Macro, Functions and more

Script That Will Run another Block of Code Given SAS/ACCESS Successfully Connected to OracleTable

Reply
Occasional Contributor
Posts: 5

Script That Will Run another Block of Code Given SAS/ACCESS Successfully Connected to OracleTable

[ Edited ]

I am looking to create a regular script or macro allowing me to:

 

  • Run additional oracle library scripts giving the first one connects
  • Reason:  I tend to run 3 at a time and when my password changes at time I still run the the 3 scripts with the old password which then ends up locking my account since after 3 incorrect login attempts my oracle username will lock. 

 

 

Using SAS/CONNECT: libname MAIN odbc user=***** password=*****dsn= myoracle schema=Table1 preserve_tab_names=yes;

 

 

Pseudo code:

 

If username connects to table then do;

Run oracle script 1;

Run oracle script 2;

 

End;

Super Contributor
Posts: 439

Re: Script That Will Run another Block of Code Given SAS/ACCESS Successfully Connected to OracleTabl

Try the Libref function - this checks whether a libref has been assigned and returns 0 if it has and a non-zero value if not - the full documentation is here

Respected Advisor
Posts: 4,173

Re: Script That Will Run another Block of Code Given SAS/ACCESS Successfully Connected to OracleTabl

@eparson

If you issue the libname statement only once then only one attemt gets made to connect to the database. Just using the libref downstream won't create another connection attempt (especially as you're not using option defer=yes).

 

If your code tries to connect multiple times then you must have code with multiple libname statements.

 

In case you run all these 3 SQL's in the same session then just have a single libname statement on top of your script.

 

What at @ChrisBrooks proposes would allow you to check if a connection could get established and lets you implement code that ends in a controlled manner if something goes wrong. It wouldn't protect you though from SAS making a connection attempt first and your account would still get locked out after 3 such unsuccessful attempts.

 

One way to get around this:

Have a SAS Admin talk with the Oracle DBA's so he/she can define an authentication domain in SAS Metadata using a generic account where the password never expires or where admins managed the generic account and keep passwords in sync becomes an IT function. With such a domain defined you won't need to pass credentials in your libname statements anymor, you just need metadata read access to the definition in SAS Metadata.

http://support.sas.com/kb/38/204.html

http://support.sas.com/documentation/cdl/en/bidsag/69848/HTML/default/viewer.htm#p0g4zazv5evotnn1db3...

 

 

If that's not possible then another option I can think of:

1. Implement a SAS Autocall macro

2. Have the macro issue the libname statement

3. Build in the check as proposes by @ChrisBrooks

4. Maintain a permanent control table in SAS where you write an entry in case you couldn't establish a connection

    If possible: Only write this entry if the reason was an expired password. Not sure though if you get a specific return code for this.

5. In your macro always check first your control table for an entry of failed connection attempt and only issue the libname statement if there hasn't been such a failed attempt.

 

...and just as an idea: You could also add a timestamp to the entries for failed attempts and only consider entries not older than 30 minutes or so. Or you just have an adhoc script which cleans out the control table once you've changed your password.

 

Ask a Question
Discussion stats
  • 2 replies
  • 116 views
  • 0 likes
  • 3 in conversation