Link to home
Start Free TrialLog in
Avatar of vadicherla
vadicherla

asked on

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

Avatar of Sean Stuber
Sean Stuber

you'll have to split your string into pieces, encrypt each and concatenate the results
Avatar of vadicherla

ASKER

Okey. Can you please provide more information how to do for above procedure
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

Can you please update
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
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);
ASKER CERTIFIED SOLUTION
Avatar of Sean Stuber
Sean Stuber

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Do we have solution for this.. Can you please let me know