Home » SQL & PL/SQL » SQL & PL/SQL » How to export multiple blob and character data into a text file? (PL/SQL)
How to export multiple blob and character data into a text file? [message #679242] Tue, 18 February 2020 03:34 Go to next message
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 #679243 is a reply to message #679242] Tue, 18 February 2020 04:18 Go to previous messageGo to next message
cookiemonster
Messages: 13894
Registered: September 2008
Location: Rainy Manchester
Senior Member
Welcome to the forum.
Please read and follow How to use [code] tags and make your code easier to read?

If your code isn't doing what you want it would help if you told us what it was doing instead.
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 Go to previous messageGo to next message
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

Re: How to export multiple blob and character data into a text file? [message #679249 is a reply to message #679248] Tue, 18 February 2020 21:32 Go to previous message
BlackSwan
Messages: 26703
Registered: January 2009
Location: SoCal
Senior Member
select XML_IMAGE INTO v_lob_loc from TABLENAME;
V_LOB_LOC is a scalar (holds only single value)
There is no WHERE clause so SELECT returns MANY rows which can't be jammed into single variable.
INTO V_LOB_LOC requires only single row to be returned.
Previous Topic: How To Show Non Matching rows in oracle
Next Topic: PLSQL: Execute Immediate is not working as expected in PLSQL anonymous block.
Goto Forum:
  


Current Time: Fri May 29 06:29:24 CDT 2020