Search code examples
sqloraclehexutl-file

How to return LOW VALUES HEX '00' in sql statement?

I need to write into file (in the middle of the string) a LOW VALUES HEX'00'.

I could do it using package utl_file using the next code utl_file.put_raw(v_file, hextoraw('000000')). But I may do it only in the beginning and end of file, not in the middle of string.

So, my question is: how to write a LOW VALUES HEX'00' in the select statement. I tried some variants like 

Select ‘blablabla’ Q, hextoraw('000000'), ‘blablabla’ w from dual;

save it into .dat file, then open it in hex-editor but the result was different when using utl_file.

Could anybody (if it's possible) write a correct sql statement.

Solution

  • If I understand you correctly, you're trying to add a null/binary zero to your output. If so, you can just use chr(0).

    eg. utl_file.putf(l_file, 'This is a binary zero' || chr(0));

    Looking at that in a hex editor will show you:

    00000000  54 68 69 73 20 69 73 20  61 20 62 69 6e 61 72 79  |This is a binary|
00000010  20 7a 65 72 6f 00 0a                              | zero..|