How to convert ASCII strings and hexadecimal strings in Oracle

ASCII string ==> hexadecimal string

Create or replace function asctohex (sin in varchar2)
Return varchar2
Is
Stmp varchar2 (4000 );
I integer;
Begin
I: = 1;
Stmp: = '';
For I in 1 .. length (SIN) loop
Stmp: = stmp | trim (to_char (ASCII (substr (sin, I, 1), 'xxx '));
End loop;
Return stmp;
End;
/

Hexadecimal string ==> ASCII string
To distinguish character sets, take Chinese characters as an example. For database character sets that are Chinese, make sure they can be correctly converted back.

The database character set is Chinese.

Create or replace function hextoasc (sin in varchar2)
Return varchar2
Is
Stmp varchar2 (4000 );
I integer;
X integer;
Begin
I: = 1;
Stmp: = '';
Loop
Exit when I> length (SIN );
X: = to_number (substr (sin, I, 2), 'xxxx ');
If X> 128 then
Stmp: = stmp | CHR (to_number (substr (sin, I, 4), 'xxx '));
I: = I + 4;
Else
Stmp: = stmp | CHR (to_number (substr (sin, I, 2), 'xxx '));
I: = I + 2;
End if;

End loop;
Return stmp;
End;
/

The database character set is English

Create or replace function hextoasc (sin in varchar2)
Return varchar2
Is
Stmp varchar2 (4000 );
I integer;
Begin
I: = 1;
Stmp: = '';
Loop
Exit when I> length (SIN );
Stmp: = stmp | CHR (to_number (substr (sin, I, 2), 'xxx '));
I: = I + 2;
End loop;
Return stmp;
End;
/

The test is as follows:

SQL> select hextoasc (asctohex ('Hello ABC? ') from dual;
Hextoasc (asctohex ('Hello ABC '))
--------------------------------------------------------------------------------
Hello, ABC?