Home » SQL & PL/SQL » SQL & PL/SQL » invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE (Oracle 10G Sun Solaris)
invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE [message #298203] Tue, 05 February 2008 05:54 Go to next message
Messages: 59
Registered: January 2007

I am trying to load a BLOB in a table . My pl/sql is as follows .

CREATE OR REPLACE PROCEDURE load_files(p_file_name IN VARCHAR2, p_asc_gen_code IN VARCHAR2) IS

src_file BFILE;
dst_file BLOB;
file_name VARCHAR2(100);

file_name := 'agreemnt.gz'; // Hard coded just for test purpose

src_file := bfilename('DWNLD_DIR', file_name);
DBMS_OUTPUT.PUT_LINE('p_file_name ' || p_file_name);
DBMS_OUTPUT.PUT_LINE('file_name ' || file_name);
DBMS_OUTPUT.PUT_LINE('asc_gen_code ' || p_asc_gen_code );

SELECT asc_gen_data
INTO dst_file
FROM ascii_gen
WHERE asc_gen_code = p_asc_gen_code FOR UPDATE;

DBMS_LOB.LOADFROMFILE(dst_file, src_file, DBMS_LOB.GETLENGTH(src_file));
DBMS_OUTPUT.PUT_LINE('before update');
SET asc_gen_data = dst_file
WHERE asc_gen_code = p_asc_gen_code ;


DBMS_OUTPUT.PUT_LINE('No file was present');

END load_files;

When I execute the pl/sql

set serveroutput on

execute load_files('agreemnt.gz','ATT');

I get the following errors
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 637

Can you please tell me where is the mistake , because I tried a simple program provided by TOM its working .

Re: invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE [message #298211 is a reply to message #298203] Tue, 05 February 2008 06:15 Go to previous message
Michel Cadot
Messages: 63911
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code.
Use the "Preview Message" button to verify.
Always post your Oracle version (4 decimals).

ORA-22275: invalid LOB locator specified
 *Cause:  There are several causes:  (1) the LOB locator was never
          initialized; (2) the locator is for a BFILE and the routine
          expects a BLOB/CLOB/NCLOB locator; (3) the locator is for a
          BLOB/CLOB/NCLOB and the routine expects a BFILE locator;
          (4) trying to update the LOB in a trigger body -- LOBs in
          trigger bodies are read only; (5) the locator is for a
          BFILE/BLOB and the routine expects a CLOB/NCLOB locator;
          (6) the locator is for a CLOB/NCLOB and the routine expects
          a BFILE/BLOB locator;
 *Action: For (1), initialize the LOB locator by selecting into the locator
          variable or by setting the LOB locator to empty.  For (2),(3),
          (5) and (6)pass the correct type of locator into the routine.
          For (4), remove the trigger body code that updates the LOB value.

Previous Topic: stuck with output
Next Topic: Stop executing trigger
Goto Forum:

Current Time: Sat Oct 22 19:25:34 CDT 2016

Total time taken to generate the page: 0.05158 seconds