Oracle Encryption

CREATE OR REPLACE procedure saveSecure(v_NCC number,v_ccv_secure_group_id number,
v_ccv_secure_variable_id number,
V_LAST_MODIFIED_DATE timestamp,v_value in long raw,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct NCC,vcc_group_id,ccv_variable_id from CCV_SECURE where
NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id )loop
--row exists
update CCV_SECURE set NCC = v_NCC, vcc_group_id=v_ccv_secure_group_id,ccv_variable_id=v_ccv_secure_variable_id,
last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE  NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO CCV_SECURE(CCV_SECURE_ID,NCC,vcc_group_id,ccv_variable_id,LAST_MODIFIED_DATE,SECURE_VALUE)
    VALUES (CCV_SECURE_SEQ.nextval,v_NCC,v_ccv_secure_group_id,v_ccv_secure_variable_id,V_LAST_MODIFIED_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/

 The above procedure giving an error on secure value if i enter more that 2000 characters for v_value column.  Do you know how to enter more than 2000 characters for v_value

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at line 1
ORA-06512: at "saveSecure", line 6
ORA-06512: at line 1

vadicherlaAsked:
Who is Participating?
 
sdstuberConnect With a Mentor Commented:
It doesn't matter that your column is a long raw, the SQL statement itself is failing because you are generating a raw string that is too long

Here's a simple test to demonstrate


create table rawtest (lr  long raw);

insert into rawtest (lr) values(utl_raw.cast_to_raw(rpad('test',2000,'test')));   --- this will work because the generated RAW is 4000 bytes long

insert into rawtest (lr) values(utl_raw.cast_to_raw(rpad('test',2001,'test')));   --- this will fail because the generated RAW is 4002 bytes long
0
 
sdstuberCommented:
you'll have to split your string into pieces, encrypt each and concatenate the results
0
 
vadicherlaAuthor Commented:
Okey. Can you please provide more information how to do for above procedure
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
vadicherlaAuthor Commented:
i modified but still i am getting error

CREATE OR REPLACE procedure saveSecure(v_NCC number,v_ccv_secure_group_id number,
v_ccv_secure_variable_id number,
V_LAST_MODIFIED_DATE timestamp,v_value1 in varchar2,v_value2 in varchar2,encrypt_decrypt_key in varchar2) as v_temp integer;
begin
for r in(select distinct NCC,vcc_group_id,ccv_variable_id from CCV_SECURE where
NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id )loop
--row exists
update CCV_SECURE set NCC = v_NCC, vcc_group_id=v_ccv_secure_group_id,ccv_variable_id=v_ccv_secure_variable_id,
last_modified_date=v_last_modified_date, SECURE_VALUE=sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value1||v_value2),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               ) WHERE  NCC=v_NCC and vcc_group_id=v_ccv_secure_group_id and ccv_variable_id =v_ccv_secure_variable_id;
--DBMS_OUTPUT.PUT_LINE('Number of Rows Updated: ' ||SQL%ROWCOUNT);
     return;
  end loop;
  -- row doesn't exist
INSERT INTO CCV_SECURE(CCV_SECURE_ID,NCC,vcc_group_id,ccv_variable_id,LAST_MODIFIED_DATE,SECURE_VALUE)
    VALUES (CCV_SECURE_SEQ.nextval,v_NCC,v_ccv_secure_group_id,v_ccv_secure_variable_id,V_LAST_MODIFIED_DATE,
               sys.DBMS_CRYPTO.
                encrypt(
                   UTL_RAW.cast_to_long raw(v_value1 ||v_value2),
                   8  + 256 + 4096,
                   UTL_RAW.cast_to_raw(encrypt_decrypt_key)
               )
           );
--DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
end;
/

ROR at line 1:
ORA-06502: PL/SQL: numeric or value error: raw variable length too long
ORA-06512: at "SYS.UTL_RAW", line 224
ORA-06512: at "DX_TEST.SAVESECURESITESTRINGVARIABLE", line 16
ORA-06512: at line 1

0
 
vadicherlaAuthor Commented:
Can you please update
0
 
sdstuberCommented:
Are you sure the problem is in the encryption?

What is line 16?  Is it the update

Is the problem that secure_value is being updated with a RAW value too big?

There is a quirk to working with SQL and PL/SQL together.

VARCHAR2 and RAW can have length up to 32767 in pl/sql,  but only 4000 in SQL.
If you have multi-byte characters, the limits are proportionally less
0
 
vadicherlaAuthor Commented:
I gave data type as long raw for secure value column in table


its insert which is failing

INSERT INTO SSV_SECURE(SSV_SECURE_ID,CCN,SSV_GROUP_ID,SSV_VARIABLE_ID,LAST_MODIFIED_DATE,SECURE_VALUE)
 17      VALUES (SSV_SECURE_SEQ.nextval,v_ccn,v_ssv_secure_group_id,v_ssv_secure_variable_id,V_LAST_MODIFIED_DATE,
 18                 sys.DBMS_CRYPTO.
                encrypt(UTL_RAW.cast_to_raw(v_value1 ||v_value2),
 19   20                     8  + 256 + 4096,
 21                     UTL_RAW.cast_to_raw(encrypt_decrypt_key)));
 22  --DBMS_OUTPUT.PUT_LINE('Number of Rows inserted: ' ||SQL%ROWCOUNT);
0
 
vadicherlaAuthor Commented:
Do we have solution for this.. Can you please let me know
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.