I am reading Oracle encrypted data defined as RAW(200) with SAS which is
then assigned an internal format of $HEX400. and padded with trailing
blanks (hex 20s).
=20
The problem I am encountering is when the Oracle encrypted data has a
hexdecimal 20 as its last encrypted character, that character and all
padded hex 20s get truncated when I write that encrypted value to an
Oracle output table ... ugh!
=20
SAS Technical Support does not have a workaround for this ... double
ugh.
=20
Anyone know of anything I can try?
=20
____________________________________________
=20
Ken Krawczyk | Information Technology Delivery  - INGENIX
2717 North 118th Street  - Suite 300 - Omaha - Nebraska  - 68164
402 445 5482 (ofc)       402 578 3794 (cell)      402 445 5547 (fax)
kenneth.krawczyk @ ingenix.com <mailto:kenneth.krawczyk@ingenix.com>=20
=20

This e-mail, including attachments, may include confidential and/or
proprietary information, and may be used only by the person or entity
to which it is addressed. If the reader of this e-mail is not the intended
recipient or his or her authorized agent, the reader is hereby notified
that any dissemination, distribution or copying of this e-mail is
prohibited. If you have received this e-mail in error, please notify the
sender by replying to this message and delete this e-mail immediately.

Ken -
  When you pull the data from Oracle pull the length into a numeric
variable in SAS. I am not sure what the length function in Oracle
would be.

  Here is some *psuedo* code to show the idea.
proc sql ;
 connect to oracle ;
 create table sd as select * from connection to oracle
   (select data,length(data) length_data from x.y)
 ;

  When writing to Oracle you might be able use some string function
oracle has to convert HEX to RAW so that you can give it the HEX
values and it will convert it. Then you can use something like this to
create a string of hex codes.

  substr(put(data,$hex400.),1,2*length_data)

  Otherwise create code in Oracle to truncate (or pad) the values
based on the numeric length column.

- Tom Abernathy



On Jan 8, 1:11=A0pm, kenneth.krawc...@INGENIX.COM ("Krawczyk, Kenneth
E") wrote:
> I am reading Oracle encrypted data defined as RAW(200) with SAS which is
> then assigned an internal format of $HEX400. and padded with trailing
> blanks (hex 20s).
> =3D20
> The problem I am encountering is when the Oracle encrypted data has a
> hexdecimal 20 as its last encrypted character, that character and all
> padded hex 20s get truncated when I write that encrypted value to an
> Oracle output table ... ugh!
> =3D20
> SAS Technical Support does not have a workaround for this ... double
> ugh.
> =3D20
> Anyone know of anything I can try?
> =3D20
> ____________________________________________
> =3D20
> Ken Krawczyk | Information Technology Delivery =A0- INGENIX
> 2717 North 118th Street =A0- Suite 300 - Omaha - Nebraska =A0- 68164
> 402 445 5482 (ofc) =A0 =A0 =A0 402 578 3794 (cell) =A0 =A0 =A0402 445 554=
7 (fax)
> kenneth.krawczyk @ ingenix.com <mailto:kenneth.krawc...@ingenix.com>=3D20
> =3D20
>
> This e-mail, including attachments, may include confidential and/or
> proprietary information, and may be used only by the person or entity
> to which it is addressed. If the reader of this e-mail is not the intende=
d
> recipient or his or her authorized agent, the reader is hereby notified
> that any dissemination, distribution or copying of this e-mail is
> prohibited. If you have received this e-mail in error, please notify the
> sender by replying to this message and delete this e-mail immediately.

Ken,
You probably want to ask Oracle (no, not THE Oracle) what the
length of the original character variable was:

proc sql;
connect to oracle <options>;
Create table extract as select * from connection to oracle(
select rawchar,length(rawchar) as len from <table>);

then, after loading the SAS table, you can ask Oracle to
set the appropriate length:

execute(
  update <table you loaded> set rawchar=rpad(rawchar,len,' ')
   ) by oracle;

Hope this is helpful
Søren

On Fri, 8 Jan 2010 12:11:47 -0600, Krawczyk, Kenneth E
<kenneth.krawczyk@INGENIX.COM> wrote:

>I am reading Oracle encrypted data defined as RAW(200) with SAS which is
>then assigned an internal format of $HEX400. and padded with trailing
>blanks (hex 20s).
>
>The problem I am encountering is when the Oracle encrypted data has a
>hexdecimal 20 as its last encrypted character, that character and all
>padded hex 20s get truncated when I write that encrypted value to an
>Oracle output table ... ugh!
>
>SAS Technical Support does not have a workaround for this ... double
>ugh.
>
>Anyone know of anything I can try?
>
>____________________________________________
>
>Ken Krawczyk | Information Technology Delivery  - INGENIX
>2717 North 118th Street  - Suite 300 - Omaha - Nebraska  - 68164
>402 445 5482 (ofc)       402 578 3794 (cell)      402 445 5547 (fax)
>kenneth.krawczyk @ ingenix.com <mailto:kenneth.krawczyk@ingenix.com>
>
>
>This e-mail, including attachments, may include confidential and/or
>proprietary information, and may be used only by the person or entity
>to which it is addressed. If the reader of this e-mail is not the intended
>recipient or his or her authorized agent, the reader is hereby notified
>that any dissemination, distribution or copying of this e-mail is
>prohibited. If you have received this e-mail in error, please notify the
>sender by replying to this message and delete this e-mail immediately.