Hi,
I recently started working on CLOBs, with no clue what I had in store. It took me quite a bit of time to get CLOBs mastered, but now in my code, i can do operations like insertions, updations, and searches on CLOB objects.

If anyone has any queries on CLOB objects, please do let me know, and I would be more than eager to share my experience and knowledge on the same.

Thanx and Regards
Aruneesh

Hi Aruneesh:

Thanks for your posting. I may be benefited...

I am facing two problems in using CLOBs in our Oracle database.
1. To process data of approximately 1MB, it takes 50 seconds. How can I improve the performance.
2. After the Oracle process finishes processing the procedure, it continue to take 100% CPU for few minutes. Why is this happening?

Here is what I am trying to do:
BEGIN
...
Process_Clob(oidFile);
END;

CREATE OR REPLACE PROCEDURE Process_Clob(fileoid NUMBER) IS
FStream clob;
row_end INTEGER := 0;
Row_ini INTEGER := 0;
row_line VARCHAR2(2000);
BEGIN
SELECT psv_stream INTO FStream FROM SAVED_FILES
WHERE oid_file = fileoid;

row_end := DBMS_LOB.INSTR(FStream, CHR(10), 1, 1);

dbms_output.put_line('start: ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));

WHILE row_end != 0
LOOP
row_line := TRIM(DBMS_LOB.SUBSTR(FStream,row_end-row_ini-1, row_ini+1));
row_line := REPLACE(row_line, CHR(10), NULL);
row_line := REPLACE(row_line, CHR(13), NULL);
row_ini := row_end;
row_end := DBMS_LOB.INSTR(FStream, CHR(10), row_ini+1, 1);
IF row_ini + 3 > row_end THEN
EXIT;
END IF;
END LOOP;
dbms_output.put_line('END: ' || TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss'));
END;
/

Hi
There are two things that I wanted to share with you on the CLOB updation. I didnt use stored procedure for the same.

But surely if you are writing that big an object into Oracle you would face the time gap. But 50 seconds is not expected. 2-4 seconds is what it should be.

I did the same from within a JSP.
I could share the JSP and then we could dicsuss about the stored procedures so what we reach a consensus.

In the JSP, there is a command called createStatement, and without passing it extra parameters for cursor, it used to take 1-2 minutes for me to update the same record, as a result of which, the next updation used to just kill my oracle.

So i passed parameters to the createStatement command, and it worked fine after that.

Please let me know how the stored procdure you have is working.
Aruneesh