Topic: SQL*Plus and PL/SQL >> chinese char encrypt + CLOB
|
Disclaimer: The purpose of all dbapool forums including OCP and Other Oracle Certification forums is to help each other with specific issues but not to share dump and copyrighted exam content, materials or intellectual property.
You may review the entire Oracle Certification Program Candidate Agreement online Here.
|
|
|
|
| Title: chinese char encrypt + CLOB |
| SIV |
Posted: Jun 24, 2008 02:49:30 AM |
Total Post: 5
Joined: Feb, 2007
|
Problem Description:
Unable to process string of bigger size through PL/SQL procedure.
Flow of Work: (Struts based Application)
1) User keys-in information from front end for a CLOB column.
2) On Submit, the Server Triggers the encrypt functionality(PL/SQL Pg.) encrypts the input data and saves in the CLOB Column.
3) On Retrieval from the database the decrypt logic is applied to CLOB data and is displayed in the front end.
I/P Front-End (String) ---> User defined Encrypt Function PL/SQL(IN Varchar2,Out Varchar2) ----> DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT (IN RAW, Out RAW) --->DATABASE TABLE( CLOB COLUMN).
System Specs:
ORACLE 10G running on UNIX Server.
Character Set : AL32UTF8 ( To save Chinese characters, its been changed from USASCII)
Business Restriction:
All CLOB based fields needs to encrypted and saved in the database.
Problem Definition:
In AL32UTF, we are following RAW based encoding/decoding of data using DBMS_OBFUSCATION_TOOLKIT.
This function takes input as Varchar2 convert it to RAW, calls the DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT and returns the encrypted output(RAW O/P) as Varchar2 which is saved in the database. If the size of the input Varchar2 is more the function fails throwing "ORA06502: Numeric or value error. Character buffer length too short". We tried changing the return type from Varchar2 which also dint help. Please find attached the encrypt/decrypt logics.
Encryption/Decryption Logic Used:
DBMS_OBFUSCATION_TOOLKIT.DES3ENCRYPT
DBMS_OBFUSCATION_TOOLKIT.DES3DECRYPT
Alternatives Identified but unable to Proceed:
We have also identified that Oracle10G has also got a new Crypto logic updated as DBMS_CRYPTO package. From the articles read BLOB/CLOB encryption/decryption can be done using them, we are unable to get any working examples on the same. Please find the syntax of the functions attached.
DBMS_CRYPT.DECRYPT(
dst IN OUT NOCOPY CLOB CHARACTER SET ANY_CS,
src IN BLOB,
typ IN PLS_INTEGER,
key IN RAW,
iv IN RAW DEFAULT NULL);
DBMS_CRYPTO.ENCRYPT(
dst IN OUT NOCOPY BLOB,
src IN CLOB CHARACTER SET ANY_CS,
typ IN PLS_INTEGER,
key IN RAW,
iv IN RAW DEFAULT NULL); |
|
|
 Gitesh |
| Posted: Jun 26, 2008 12:57:46 AM |  |
|
Total Post: 322
Joined: May, 2005
|
You should better raise SR in metalink.
|
|
|
|
|
| SIV |
| Posted: Jun 26, 2008 04:17:21 AM | |
|
Total Post: 5
Joined: Feb, 2007
|
can you Please Mention at which place, should i raise SR ?
|
|
|
|
|
| Time Zone: EDT |
Send this thread to your friend |
|
|
|
|
Forum Rules & Description
Who Can Read The Forum? Any registered user or guest
Who Can Post New Topics? Any registered user
Who Can Post Replies? Any registered user
|
| |
Get FREE Magazines
|
Top 10 Forum User
|
| Murtuja Khokhar | 857 |
| Mohammed Taj | 746 |
| positive fanatic | 483 |
| Jayanta Sur | 479 |
| Vigyan Kaushik | 386 |
| Gopu Gopi | 327 |
| Gitesh Trivedi | 322 |
| Vinoth Kumar | 264 |
| neeraj sharma | 258 |
| Ramesh Jois | 246 |
|
|
