I have following sample code:
create or replace
PROCEDURE xml_gen(cvar OUT CLOB) AS
CURSOR cur IS
SELECT * from projects;
BEGIN
dbms_lob.write(cvar,19,1, '<root><book><title>');
FOR c IN cur LOOP
dbms_lob.writeappend(cvar, 19, '<root><book><title>');
dbms_lob.writeappend(cvar, length(c.p_name), c.p_name);
dbms_lob.writeappend(cvar, 14, '</title><desc>');
dbms_lob.writeappend(cvar, length(c.project_id), c.project_id);
dbms_lob.writeappend(cvar, 27, '</desc></book><author_name>');
END LOOP;
END xml_gen;
/
DECLARE
cvar CLOB ;
BEGIN
xml_gen(cvar);
--dbms_output.put_line(cvar);
END;
/
The code compiles but on running it I get error as "invalid LOB locator specified: ORA-22275". Please advice how to modify it? I read different articles where temporary LOB is created,
lob is opened etc, but not really sure what sequence of steps is used. My requirement is to have a stored procedure with CLOB output which will be called by Java. Please advice.
Thanks,