Re: Problems with DBMS_LOB.LOADFROMFILE ...

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 04 Sep 1999 21:18:19 -0400
Message-ID: <bsPRNw25Pwj0rVSCKIZjqNl3DAe6@4ax.com>

A copy of this was sent to thalabi_at_my-deja.com (if that email address didn't require changing) On Sat, 04 Sep 1999 22:18:42 GMT, you wrote:

>Hi,
>
>I am having trouble with calling DBMS_LOB.LOADFROMFILE.
>Here is the code I am using;
>
>DECLARE
> lobd BLOB;
> fils BFILE := BFILENAME('THALABI_HOME','login.sql');
> amt INTEGER := 4000;
>BEGIN
> DBMS_LOB.FILEOPEN(fils, dbms_lob.file_readonly);
> DBMS_LOB.LOADFROMFILE(lobd, fils, amt);
> COMMIT;
> DBMS_LOB.FILECLOSE(fils);
>END;
>
>I am getting the following error;
>
>DECLARE
>*
>ERROR at line 1:
>ORA-06502: PL/SQL: numeric or value error
>ORA-06512: at "SYS.DBMS_LOB", line 414
>ORA-06512: at line 7
>
>Line 7 is the DBMS_LOB.LOADFROMFILE statement.
>I know it has nothing to do with the directory name or the file name
>because the DBMS_LOB.FILEOPEN execute successfully.
>
>Anyone has an idea why I am getting this error?
>

there are 2 issues with the above.

issue 1: the numeric or value error is happening because you are dbms_lob.loadfromfile'ing into a uninitialized BLOB. In 8.0 blobs MUST live in the database (you have to insert an empty_blob(), select it back out and then you can use it.). In 8.1 you can use temporary lobs but you still have to initialize it with a call to create_temporary.

issue 2: if the file is less then 4000 bytes you'll get:

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

you'll want to set amt = dbms_lob.getlength(fils), not 4000 (or at least the least(4000,dbms_lob.getlength(fils))

Here is an example of how to load a file into the database:

tkyte_at_8.0> create table demo

  2  ( id            int primary key,
  3    theBlob       blob

  4 )
  5 /

Table created.

tkyte_at_8.0>
tkyte_at_8.0> create or replace directory my_files as '/export/home/tkyte/public_html';

Directory created.

tkyte_at_8.0>
tkyte_at_8.0> declare

  2          l_blob  blob;
  3          l_bfile bfile;
  4  begin
  5          insert into demo values ( 1, empty_blob() )
  6          returning theBlob into l_blob;
  7  
  7          l_bfile := bfilename( 'MY_FILES', 'aria.gif' );
  8          dbms_lob.fileopen( l_bfile );
  9  
  9          dbms_lob.loadfromfile( l_blob, l_bfile,
 10                                 dbms_lob.getlength( l_bfile ) );
 11  
 11          dbms_lob.fileclose( l_bfile );

 12 end;
 13 /

PL/SQL procedure successfully completed.

>Regards
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat Sep 04 1999 - 20:18:19 CDT