Is the decrypt procedure a function? If so, I would think you would be able to call it in passthru just as your dba does (assuming you have perms enough to execute the function).

Do you get the same results if you issue your query at a sql*plus prompt?

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Miller, Scott Sent: Thursday, July 15, 2004 10:09 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: connecting and reading encrypted data from oracle

ok...i found out that the oracle dbase i am attempting to connect, has table level encrypted data fields(made using oracle's obfuscation toolkit). i can pull up a table once i have assigned the oracle library, but the fields in the table that are encrypted are garbled, while others i can read just as always. how can i decrypt these fields while bringing them into sas?

the dba uses an oracle procedure to decrypt each field based on the key in a sql statement. the oracle server is version 9i, while the client version we are using is 8.1. i am running sas 8.2 on pc currently, but am getting 9.1.2 in the next day or so.

Scott D. Miller, MA

Evaluation and Informatics Analyst West Virginia Medical Institute (304) 346-9864 ext 2240

Every good scientist should be one part P.T. Barnum and one part B.F. Skinner.

Sumus quid sumus

...and why did I leave the plow in the field, and look for a job in the town

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU]On Behalf Of Pardee, Roy Sent: Thursday, July 15, 2004 11:08 AM To: SAS-L@LISTSERV.UGA.EDU Subject: Re: connecting and reading encrypted data from oracle

I would bet that the decryption will be handled at the oracle client level & you won't need to change your practices at all (tho you may have more time on your hands as you wait not only for data to come across the wire, but for the decryption step as well).

Have you posed this question to your DBA?

Cheers,

-Roy

P.S. Do I recognize your P.T. Barnum quote from an episode of the Simpsons?

-----Original Message----- From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Miller, Scott Sent: Thursday, July 15, 2004 6:38 AM To: SAS-L@LISTSERV.UGA.EDU Subject: connecting and reading encrypted data from oracle

i am just getting sas 9.1.2 installed and also just found out that an oracle database i am using will be encrypted soon. i connect now to oracle with a libname statement, so my question is how do i connect to this new oracle dbase once it is encrypted? i typically use data steps to access data and rarely use sql. thanx.

Scott D. Miller, MA

Evaluation and Informatics Analyst West Virginia Medical Institute (304) 346-9864 ext 2240

Every good scientist should be one part P.T. Barnum and one part B.F. Skinner.

Sumus quid sumus

...and why did I leave the plow in the field, and look for a job in the town