Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

real vs reported file size?

598408Sep 25 2007 — edited Sep 25 2007
the following procedure is returning the error:

ERROR at line 1:
ORA-22993: specified input amount is greater than actual source amount
ORA-06512: at "SYS.DBMS_LOB", line 639
ORA-06512: at "BANINST1.LOAD_CLOB", line 14
ORA-06512: at line 1

I printed out the reported size from DBMS_LOB.GETLENGTH(in_file)
it returns 7262. Via trial and error, I found that the largest acceptable value I could set for 'AMOUNT' to be 7246. This is the same charactor count (7246) my editor shows for 'in_file'. Why is the function call DBMS_LOB.GETLENGTH(in_file) reporting 16 charactors more?



CREATE TABLE js_holder (
js_name varchar(30),
js_body CLOB );

CREATE DIRECTORY foos as 'USR$DISK:[CLEMENSD.SQL_EDIT]';

CREATE OR REPLACE PROCEDURE Load_CLOB IS
dest_clob CLOB;
in_file BFILE := BFILENAME('foos', 'supernote.js');
dst_offset number := 1 ;
src_offset number := 1 ;
lang_ctx number := DBMS_LOB.DEFAULT_LANG_CTX;
warning number;

BEGIN
DBMS_OUTPUT.ENABLE(100000);
INSERT INTO js_holder(js_name, js_body)
VALUES('supernote', empty_clob())
RETURNING js_body INTO dest_clob;
DBMS_LOB.OPEN(in_file, DBMS_LOB.LOB_READONLY);
DBMS_LOB.LoadCLOBFromFile(
DEST_LOB => dest_clob,
SRC_BFILE => in_file,
AMOUNT => DBMS_LOB.GETLENGTH(in_file),
DEST_OFFSET => dst_offset ,
SRC_OFFSET => src_offset,
BFILE_CSID => DBMS_LOB.DEFAULT_CSID,
LANG_CONTEXT => lang_ctx,
WARNING => warning );
DBMS_LOB.CLOSE(in_file);
COMMIT;
DBMS_OUTPUT.PUT_LINE('Loaded File using DBMS_LOB.LoadCLOBFromFile.');
END;


then the following:

SQL> set serveroutput on
SQL> exec load_clob
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 23 2007
Added on Sep 25 2007
1 comment
471 views