There are blob fields in the database. They store information. Can sql be used to see what is inside?

  • four
    And what is in them? Blob - it is slightly non-typed, in one record there can be "War and Peace" in fb2, in the other - Moonlight Sonata during flac, in the third - "Hello world" in CP1251 ... - Zufir
  • @Zufir numbers, probably decimal but I think it doesn't matter here. There are just numeric data. - Andrei
  • @Zufir maybe, how can I upload all this bunch of information to a file? using sql? Or something else to display a query in something more than varchar2? - Andrey

2 answers 2

If the text in the blob is simply text - use UTL_RAW.CAST_TO_VARCHAR2 (blob_field) Example

  • Just like this select UTL_RAW.CAST_TO_VARCHAR2(THICKLEAD) from PIPE_GEOMETRY where PIPE = 614478; and error ORA-22835: Π Π°Π·ΠΌΠ΅Ρ€ Π±ΡƒΡ„Π΅Ρ€Π° нСдостаточСн для прСобразования CLOB Π² CHAR ΠΈΠ»ΠΈ BLOB Π² RAW (фактичСски: 5760, максимум: 2000) Perhaps there is too much information, there is an overflow as far as I understand as much as 3760 more possible, how this info can be break what I saw her all the same? Or shove something more than that. - Andrey

Use the UTL_FILE package.

An example from @StevieG :

 DECLARE l_file UTL_FILE.FILE_TYPE; l_buffer RAW(32767); l_amount BINARY_INTEGER := 32767; l_pos NUMBER := 1; l_blob BLOB; l_blob_len NUMBER; BEGIN SELECT blobcol INTO l_blob FROM table WHERE rownum = 1; l_blob_len := DBMS_LOB.getlength(l_blob); -- Open the destination file. l_file := UTL_FILE.fopen(<location>,<filename>,'wb', 32767); WHILE l_pos < l_blob_len LOOP DBMS_LOB.read(l_blob, l_amount, l_pos, l_buffer); UTL_FILE.put_raw(l_file, l_buffer, TRUE); l_pos := l_pos + l_amount; END LOOP; -- Close the file. UTL_FILE.fclose(l_file); END; /

PS <location> is the name of the directory ( directory object ), which is created using create or replace directory ...

Example:

 SQL> create directory MY_BLOBS as '<full_directory_path_on_Oracle_server>';

after that you can use this trace. in the following way:

 l_file := UTL_FILE.fopen('MY_BLOBS','file.txt','wb', 32767);
  • Please tell me in what format you need to specify the path and file name? specified the path 'D \:' and the name 'file.txt' swears that the path is incorrect. - Andrey
  • @ Andrey, <location> is the directory name ( directory object ), which is created using the create or replace directory ... and must be located on the server - MaxU
  • And then what should there be indicated? Just the blob itself is in the database. What should I specify the path and name? And by the way, can I run this code simply in worsheet as a normal query? - Andrey