How to export multiple blob and character data into a text file? [message #679242] |
Tue, 18 February 2020 03:34 |
|
Ethan Caleb
Messages: 2 Registered: February 2020
|
Junior Member |
|
|
Hi!
I'm trying to export a table with blob (encrypted xml data) and character(unique identifier of blob column) data into a "|" delimited text file. I found a script using UTL_FILE to export blob data, but i'm having hard time to create a script getting all the blob data and it's identifier.
Here is my script:
CREATE OR REPLACE PROCEDURE GEN_BLOB
AS
v_lob_loc BLOB;
v_id_xi CHAR(100);
v_batchid CHAR(10);
v_buffer RAW(32767);
v_buffer_size BINARY_INTEGER;
v_amount BINARY_INTEGER;
v_offset NUMBER(38) := 1;
v_chunksize INTEGER;
v_out_file UTL_FILE.FILE_TYPE;
BEGIN
FOR i IN (SELECT ID_XML_IMAGE v_id_xi,
XML_IMAGE v_lob_loc
FROM TABLENAME)
LOOP
select XML_IMAGE
INTO v_lob_loc
from TABLENAME;
v_chunksize := DBMS_LOB.GETCHUNKSIZE(i.v_lob_loc);
IF (v_chunksize < 32767) THEN
v_buffer_size := v_chunksize;
ELSE
v_buffer_size := 32767;
END IF;
v_amount := v_buffer_size;
DBMS_LOB.OPEN( i.v_lob_loc,DBMS_LOB.LOB_READONLY);
v_out_file := UTL_FILE.FOPEN(
location => 'ARCH_DIR',
filename => 'XML_IMAGE_'||i.v_id_xi || '.txt',
open_mode => 'wb',
max_linesize => 32767);
WHILE v_amount >= v_buffer_size
LOOP
DBMS_LOB.READ(
lob_loc => i.v_lob_loc,
amount => v_amount,
offset => v_offset,
buffer => v_buffer);
v_offset := v_offset + v_amount;
UTL_FILE.PUT_RAW (
file => v_out_file,
buffer => v_buffer,
autoflush => true);
UTL_FILE.FFLUSH(file => v_out_file);
END LOOP;
UTL_FILE.FFLUSH(file => v_out_file);
UTL_FILE.FCLOSE(v_out_file);
DBMS_LOB.CLOSE(v_lob_loc);
END LOOP;
END;
/
|
|
|
|
Re: How to export multiple blob and character data into a text file? [message #679248 is a reply to message #679243] |
Tue, 18 February 2020 19:47 |
|
Ethan Caleb
Messages: 2 Registered: February 2020
|
Junior Member |
|
|
Hello! Noted on the rules for code tags.
The script produces this error:
Error report -
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "SCHEMA.GEN_FILE", line 24
ORA-06512: at line 1
01422. 00000 - "exact fetch returns more than requested number of rows"
*Cause: The number specified in exact fetch is less than the rows returned.
*Action: Rewrite the query or change number of rows requested
[Updated on: Tue, 18 February 2020 19:48] Report message to a moderator
|
|
|
|