real vs reported file size?
598408Sep 25 2007 — edited Sep 25 2007the 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