| ORA-22275 [message #132239] |
Thu, 11 August 2005 08:52  |
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  |
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 |
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 #260386 is a reply to message #132239] |
Sun, 19 August 2007 07:31 |
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 #260390 is a reply to message #132239] |
Sun, 19 August 2007 07:44 |
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.
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
