Home » SQL & PL/SQL » SQL & PL/SQL » ORA-22275
ORA-22275 [message #132239] Thu, 11 August 2005 08:52 Go to next message
femo_je
Messages: 2
Registered: August 2005
Junior Member
Hello folks,

I encountered the error invalid LOB locator specified: ORA-22275 when I ran the following procedure to load signatures into an Oracle table. The procedure and table are below:

create or replace procedure alpha_sig_load (v_coy_code varchar2, v_security_code varchar2) is
cursor c1 is
select distinct accnt_no from sample_shareholder
where company_code = v_coy_code
and security_code = v_security_code;

temp_blob blob;
temp_os_file bfile;
existss number :=0;

begin --1st begin
--dbms_lob.fileclose(temp_os_file);
for available_accounts in c1 loop
begin
temp_os_file := BFILENAME ('SIGNATURE_DIR',available_accounts.accnt_no||'.gif');
existss := dbms_lob.fileexists(temp_os_file);
if existss = 1 then
dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
dbms_lob.loadfromfile(temp_blob,temp_os_file, dbms_lob.getlength(temp_os_file));
insert into TEST_SHARES_SIGNATURE2 (accnt_no,signature)
values (available_accounts.accnt_no,temp_blob);
else
dbms_output.put_line('Signature of Shareholder With Account Number

'||available_accounts.accnt_no||' Does Not Exist!');
end if;
exception when others then
DBMS_OUTPUT.PUT_LINE('OTHERS Exception ' || sqlerrm);
end;
end loop;
commit;
end; --1st begin
/


create table TEST_SHARES_SIGNATURE2
(ACCNT_NO VARCHAR2(15) not null,
SIGNATURE blob
)
/

Please note that 'SIGNATURE_DIR' is a directory, while 'LA01_NBL' and 'ORD' are the parameters to the procedure.

How do I resolve this problem?
Re: ORA-22275 [message #132242 is a reply to message #132239] Thu, 11 August 2005 09:11 Go to previous messageGo to next message
dmitry.nikiforov
Messages: 723
Registered: March 2005
Senior Member
You should initiate your BLOB locator using CREATETEMPORARY
procedure of DBMS_LOB package. Compare:

SQL> create or replace procedure alpha_sig_load 
  2  is
  3  
  4  temp_clob clob;
  5  temp_os_file bfile;
  6  ex number;
  7  
  8  begin --1st begin
  9  
 10  temp_os_file := BFILENAME ('SHARED_DIR','a.txt');
 11  ex := dbms_lob.fileexists(temp_os_file);
 12  if ex = 1 then
 13  dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
 14  dbms_lob.loadfromfile(temp_clob,temp_os_file, dbms_lob.getlength(temp_os_file));
 15  dbms_lob.fileclose(temp_os_file);
 16  end if;
 17  
 18  
 19  end;
 20  /

Procedure created.

SQL> exec alpha_sig_load;
BEGIN alpha_sig_load; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 646
ORA-06512: at "SCOTT.ALPHA_SIG_LOAD", line 14
ORA-06512: at line 1


SQL> create or replace procedure alpha_sig_load 
  2  is
  3  
  4  temp_clob clob;
  5  temp_os_file bfile;
  6  ex number;
  7  
  8  begin --1st begin
  9  
 10  DBMS_LOB.CREATETEMPORARY(temp_clob,true);
 11  
 12  temp_os_file := BFILENAME ('SHARED_DIR','a.txt');
 13  ex := dbms_lob.fileexists(temp_os_file);
 14  if ex = 1 then
 15  dbms_lob.fileopen(temp_os_file, dbms_lob.file_readonly);
 16  dbms_lob.loadfromfile(temp_clob,temp_os_file, dbms_lob.getlength(temp_os_file));
 17  dbms_lob.fileclose(temp_os_file);
 18  end if;
 19  
 20  
 21  end;
 22  /

Procedure created.

SQL> exec alpha_sig_load;

PL/SQL procedure successfully completed.


Rgds.
Re: ORA-22275 [message #260370 is a reply to message #132239] Sun, 19 August 2007 04:45 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

What about this?
In lob_test there is two columns. I want to copy the contents of
c_lob column into B_LOB column. I have created temporary lob but the error remains.

Can you give me a suggestion about this scenario. I have a table
which contains CLOB data type. I need to convert it's datatype to BLOB. What is the easiest way to do this?
In this example I created an extra column B_LOB and wanted to map
C_LOB colum data into B_LOB. then want to drop C_LOB column and
rename B_LOB column to C_LOB.

SQL> desc lob_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 B_LOB                                              BLOB
 C_LOB                                              CLOB

SQL> select c_lob from lob_test;

C_LOB
--------------------------------------------------------------------------------


hai. This is 1st line
This is 2nd Line

SQL> create or replace procedure a is
   v_blob Blob;
   v_in Pls_Integer := 1;
   v_out Pls_Integer := 1;
   v_lang Pls_Integer := 0;
   v_warning Pls_Integer := 0;
begin
for num in ( select c_lob from lob_test)loop
dbms_lob.createtemporary(v_blob, TRUE);
DBMS_LOB.convertToBlob(v_blob, num.c_lob, DBMS_LOB.getlength(num.c_lob), v_in, v_out,DBMS_LOB.default_csid,
v_lang, v_warning);
insert into lob_test(b_lob) values(v_blob);
end loop;
--commit;
end;  2    3    4    5    6    7    8    9   10   11   12   13   14   15
 16  /

Procedure created.

SQL> exec a;
BEGIN a; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 696
ORA-06512: at "PROD7.A", line 10
ORA-06512: at line 1

Re: ORA-22275 [message #260376 is a reply to message #260370] Sun, 19 August 2007 06:23 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Is there any lob in your table?

Regards
Michel
Re: ORA-22275 [message #260378 is a reply to message #132239] Sun, 19 August 2007 06:29 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

CLOB or BLOB. No BLOB Exist.
Re: ORA-22275 [message #260379 is a reply to message #132239] Sun, 19 August 2007 06:36 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

It BLOB exist it also does not work.
Re: ORA-22275 [message #260381 is a reply to message #260379] Sun, 19 August 2007 07:12 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You read CLOB.
CLOB must exist.

Regards
Michel
Re: ORA-22275 [message #260382 is a reply to message #132239] Sun, 19 August 2007 07:15 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes, CLOB already Exist.
SQL> desc lob_test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 B_LOB                                              BLOB
 C_LOB                                              CLOB

SQL> select c_lob from lob_test;

C_LOB
--------------------------------------------------------------------------------


hai. This is 1st line
This is 2nd Line
Re: ORA-22275 [message #260384 is a reply to message #260382] Sun, 19 August 2007 07:22 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
How many lines in the table?

Regards
Michel
Re: ORA-22275 [message #260385 is a reply to message #132239] Sun, 19 August 2007 07:24 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

SQL> select count(*) from lob_test;

  COUNT(*)
----------
         2
Re: ORA-22275 [message #260386 is a reply to message #132239] Sun, 19 August 2007 07:31 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I make it but still same results.
create or replace procedure a is
    v_clob Clob;
    v_blob Blob;
    v_in Pls_Integer := 1;
    v_out Pls_Integer := 1;
    v_lang Pls_Integer := 0;
    v_warning Pls_Integer := 0;
 begin
 for num in ( select c_lob from lob_test)
 loop
  if num.c_lob is null
    then v_blob:=null;
  else
    v_clob:=num.c_lob;
    --dbms_lob.createtemporary(v_blob, TRUE);
    DBMS_LOB.convertToBlob(v_blob, v_clob, DBMS_LOB.getlength(v_clob), v_in, v_out,DBMS_LOB.default_csid,
    v_lang, v_warning);

    insert into   2    3    4    5    6    7    8    9   10   11   12   13  lob_test(b_lob) values(v_blob);
 end if;

 end loop;
 commit;
 end;
 14   15   16   17   18   19   20   21   22   23   24   25
 26  /

Procedure created.

SQL> exec a;
BEGIN a; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified:
ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 696
ORA-06512: at "PROD7.A", line 16
ORA-06512: at line 1

Re: ORA-22275 [message #260389 is a reply to message #132239] Sun, 19 August 2007 07:39 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Try again and again and same error!!!!!!!!!!
Re: ORA-22275 [message #260390 is a reply to message #132239] Sun, 19 August 2007 07:44 Go to previous messageGo to next message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

I could not understand it worked !!! but in previous it did not.!!!

create or replace procedure a is
   v_clob Clob;
   v_blob Blob;
   v_in Pls_Integer := 1;
   v_out Pls_Integer := 1;
   v_lang Pls_Integer := 0;
   v_warning Pls_Integer := 0;
begin
for num in ( select c_lob from lob_test)
loop
 if num.c_lob is null
   then v_blob:=null;
 else
   v_clob:=num.c_lob; 
   dbms_lob.createtemporary(v_blob, TRUE);
   DBMS_LOB.convertToBlob(v_blob, v_clob, DBMS_LOB.getlength(v_clob), v_in, v_out,DBMS_LOB.default_csid,  
   v_lang, v_warning);

   insert into lob_test(b_lob) values(v_blob);
end if;
--dbms_output.put_line(v_clob);
end loop;
--commit;
end;



Procedure created.

SQL> exec a;

PL/SQL procedure successfully completed.
Re: ORA-22275 [message #260398 is a reply to message #260390] Sun, 19 August 2007 09:45 Go to previous messageGo to next message
Michel Cadot
Messages: 58489
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your procedure can work if you have 1 and only 1 not null c_lob in your table.
You have to reinitialize the variable at each row. Insert the following lines before convert call:
  v_in := 1;
  v_out := 1;

Regards
Michel
Re: ORA-22275 [message #260481 is a reply to message #132239] Mon, 20 August 2007 01:56 Go to previous message
Arju
Messages: 1554
Registered: June 2007
Location: Dhaka,Bangladesh. Mobile:...
Senior Member

Yes Michel, Thanks you so much.
Previous Topic: Beginning Date & End Date of the Month
Next Topic: Inventory Transfer Query
Goto Forum:
  


Current Time: Tue Jul 22 16:52:32 CDT 2014

Total time taken to generate the page: 0.09745 seconds