Results 1 to 3 of 3

Thread: CLOB Objects

  1. #1
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Post Unanswered: CLOB Objects

    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

  2. #2
    Join Date
    Jul 2003
    Posts
    1

    Re: CLOB Objects

    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;
    /



    Originally posted by aruneeshsalhotr
    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

  3. #3
    Join Date
    Jul 2003
    Location
    US
    Posts
    314

    Lightbulb CLOB updations

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •