|invalid LOB locator specified: ORA-22275 - using DBMS_LOB.LOADFROMFILE [message #298203]
||Tue, 05 February 2008 05:54
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
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 );
IF DBMS_LOB.FILEEXISTS(src_file) = 1 THEN
WHERE asc_gen_code = p_asc_gen_code FOR UPDATE;
DBMS_LOB.LOADFROMFILE(dst_file, src_file, DBMS_LOB.GETLENGTH(src_file));
SET asc_gen_data = dst_file
WHERE asc_gen_code = p_asc_gen_code ;
DBMS_OUTPUT.PUT_LINE('No file was present');
When I execute the pl/sql
set serveroutput on
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
Registered: March 2007
Location: Nanterre, France, http://...
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.