Oracle INSERTING into RAW with UTL_RAW.cast_to

I ran into some code that was never implemented (see below).

CREATE TABLE encryption_values
(
  NAME   VARCHAR2(6),
  VALUE  NVARCHAR2(100)
);
/

insert into encryption_values
select 'key' name,
rawtohex (
rpad ('52AB32;^$!ER94988OPS3W21@@=WTQ32',32,'X')
) value
from dual
union
select 'iv' name,
rawtohex (
rpad ('TY54ABCX12@÷× ==643QREVDG43AAYMN',32,'X')
) value
from dual;

I want to change the table definition from NVARCHAR2(100) to RAW(256). I tried using UTL_RAW.cast_to_raw() but I'm running into some syntax errors. Can someone please provide me with the correct syntax. Note I want to keep the RPAD to ensure I'm converting 32 character bytes.

I'm looking to INSERT the data into this table definition.


CREATE TABLE encryption_values
(
  NAME   VARCHAR2(6),
  VALUE  RAW(256)
);
/

CodePudding user response：

You are using the wrong function, rawtohex. You must use in your case cast_to_raw from the package utl_raw

This function converts a VARCHAR2 value represented using some number of data bytes into a RAW value with that number of data bytes. The data itself is not modified in any way, but its datatype is recast to a RAW datatype.

Demo

SQL> desc encryption_values
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(6)
 VALUE                                              RAW(256)

SQL> insert into encryption_values
  2  select 'key' name,
  3  utl_raw.cast_to_raw (rpad('52AB32;^$!ER94988OPS3W21@@=WTQ32',32,'X')) value
  4  from dual
  5  union
  6  select 'iv' name,
  7  utl_raw.cast_to_raw (rpad('TY54ABCX12@÷× ==643QREVDG43AAYMN',32,'X')) value
  8* from dual
SQL> /

2 rows created.

SQL> select * from encryption_values ;

NAME
------
VALUE
--------------------------------------------------------------------------------
iv
5459353441424358313240EFBFBDEFBFBDEFBFBDEFBFBD2B3D3D3634335152455644473433414159

key
3532414233323B5E2421455239343938384F50533357323140403D5754513332