Home » SQL & PL/SQL » SQL & PL/SQL » URGENT PLS. HELP
URGENT PLS. HELP [message #222607] Mon, 05 March 2007 11:21 Go to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
Here is the code I am using to update a blob to a table and I am getting the following errror:
ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 637
ORA-06512: at "APPS.XXCA_CNOTE_BLOB", line 50
ORA-06512: at line 7

Could someone pls. help me on this. I need it to work by end of today.
Thanks in advance.
The following is the code:

CREATE OR REPLACE PROCEDURE APPS.Xxca_cnote_blob ( retcode OUT NUMBER)
AS
v_blob BLOB;
v_flob BFILE;
v_comm_name varchar2(30);
v_path_name varchar2(80);


cursor c1 is select dir_name,filename
from xxca_cnote_temp_table
where filename='sj_test1.txt';

l_errormessage VARCHAR2 (6000);
c1_cursor c1%ROWTYPE ;


begin
for c1_cursor in c1
loop
exit when c1%notfound;
dbms_output.put_line('New '|| c1_cursor.dir_name);
dbms_output.put_line('File '|| c1_cursor.filename);
select substr(c1_cursor.dir_name,instr(c1_cursor.dir_name,'/',1,4)+1,6) into v_comm_name from dual;

dbms_output.put_line('File is'|| v_comm_name);


begin
execute immediate 'create or replace directory ACH_CNOTE_FILES as '||''''||c1_cursor.dir_name||'''';

end;
update xxca_ach_summary set cnote_blob= empty_blob()
where entity_no=trim(v_comm_name)
RETURN cnote_blob
INTO v_blob;


v_flob := BFILENAME ('ACH_CNOTE_FILES',c1_cursor.filename );
DBMS_LOB.fileopen (v_flob, DBMS_LOB.file_readonly);
DBMS_LOB.loadfromfile (
v_blob,
v_flob,
DBMS_LOB.getlength (v_flob)
);
DBMS_LOB.fileclose (v_flob);
commit;

end loop;

end xxca_cnote_blob;
/
Re: URGENT PLS. HELP [message #222610 is a reply to message #222607] Mon, 05 March 2007 11:41 Go to previous messageGo to next message
sjcp21
Messages: 16
Registered: February 2007
Junior Member
Please ignore the query I had sent regarding the cnote blob. It was a directory issue. It has been resolved.

Thanks and sorry about the confusion.
Re: URGENT PLS. HELP [message #222626 is a reply to message #222610] Mon, 05 March 2007 12:44 Go to previous message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Luckily, with a topic title like yours more than 50% of the people ignore it anyway..
Previous Topic: Age Calculation?
Next Topic: data model design
Goto Forum:
  


Current Time: Wed Dec 07 10:54:25 CST 2016

Total time taken to generate the page: 0.09562 seconds