Document Type | Technical Information
Category | Tuning
Applicable Product Version | Tibero 6
Document Number | TTUTI023
Overview
In the Tibero6 FS06 environment, when converting Varchar type string data to a hash, the maximum string size is 4000 bytes. This document guides you on how to perform hash conversion up to the maximum Varchar type size of 65532 bytes.
Method
NoteThe following example shows test values in the environment below.
1. Check Maximum String Size for Hash Conversion (4000 bytes)
SQL> SELECT DBMS_CRYPTO.HASH(RPAD('A',4000,'B'),1) FROM DUAL;
DBMS_CRYPTO.HASH(RPAD('A',4000,'B'),1)
--------------------------------------------------------------------------------
2B6DA0BA4E070442B90F492899860F22
1 row selected.
SQL> SELECT DBMS_CRYPTO.HASH(RPAD('A',4001,'B'),1) FROM DUAL;
TBR-11025: Data is too long for the column.
2. Create User-Defined Function
CREATE OR REPLACE FUNCTION F_HASH(v_varchar2 varchar2) RETURN VARCHAR2 IS v_result varchar2(100); BEGIN v_result := DBMS_CRYPTO.HASH(TO_CLOB(v_varchar2),1); RETURN v_result; END; /
3. Perform Hash Conversion with Maximum Varchar Type Size of 65532 bytes
3.1. Generate a 65532 Byte String of Varchar Type Maximum Size
SQL> select lengthb(RPAD('A',(65532),'B')) FROM DUAL;
LENGTHB(RPAD('A',(65532),'B'))
------------------------------
65532
3.2. Verify Hash Conversion
Using the f_hash function created above, verify that a 65532 byte string is converted to a hash.
SQL> select f_hash (RPAD('A',(65532),'B')) FROM DUAL;
F_HASH(RPAD('A',(65532),'B'))
--------------------------------------------------------------------------------
B972C40F3ED59EB3B1449C56D0502CE8
Additionally, you can test with various values to confirm proper operation.
SQL> select f_hash (RPAD('A',(65532),'B')) FROM DUAL UNION ALL
2 select f_hash (RPAD('A',(65532),'C')) FROM DUAL UNION ALL
3 select f_hash (RPAD('B',(65532),'C')) FROM DUAL UNION ALL
4 select f_hash (RPAD('B',(65531),'C')) FROM DUAL;
F_HASH(RPAD('A',(65532),'B'))
--------------------------------------------------------------------------------
B972C40F3ED59EB3B1449C56D0502CE8
B55DC64917FD7CA4F855AE96EB6E2664
2ECCE37AD197694ECB7C90E310F96320
3CBCE91C0F95DE2B0147D96F50D40626