Results 1 to 7 of 7

Thread: PL/SQL question

  1. #1
    Join Date
    Feb 2004
    Posts
    16

    Question Unanswered: PL/SQL question

    Dear friends,

    I am trying to insert values in a table which has a LONG datatype column.

    Issue:
    -------
    There are 20,000 records to be inserted, PL/SQL procedure works fine for rownum < 3000 rows without any errors and then I get a numeric or value error (ORA-06502: PL/SQL: numeric or value error).

    I tried to trap the exact record where error occurred and inserted the
    same record by hardcoding it in the procedure, it works fine and the
    same error repeats again.

    Approach taken:
    --------------------
    Declared a cursor which select LONG column besides couple of other columns.

    In the body, for all the records fetched by the above cursor, inserting the values in the same table for different ID.

    --
    Sample code:
    --

    set long 500000
    DECLARE
    CURSOR c1 is
    SELECT a.id,
    b.id to_be_inserted_id,
    a.value,
    a.name -- LONG
    FROM type_class a, body_class b
    WHERE a.id = b.id;


    BEGIN

    dbms_output.enable(100000);
    dbms_output.put_line('Start time '|| to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'));

    DELETE FROM type_class where id IN (select id from body_class);

    COMMIT;

    dbms_output.put_line('Deleted Sucessfully..');

    for c1rec in c1 loop

    dbms_output.put_line('Length is '|| length(c1rec.name));

    dbms_output.put_line('Inserting ID in type_class: '||to_char(c1rec.id));

    INSERT INTO type_class(id,value,name)
    VALUES(c1rec.to_be_inserted_id,c1rec.value,c1rec.n ame);

    end loop;

    COMMIT;

    dbms_output.put_line('End time '|| to_char(sysdate,'dd/mon/yyyy hh24:mi:ss'));

    exception
    when others then
    dbms_output.put_line(sqlerrm);

    END;
    /

    Is there any size restriction or parameter setting on the retrieved result set of a cursor with LONG column?

    I am just trying to figure out why I am able to insert records in chunks not in one shot.

    Any thought shared would be highly appreciated.

    Regards,
    namoh
    PS: I get the same error ORA-06502: PL/SQL: numeric or value error inspite of commenting the whole INSERT statement in the code. Looks like its something to do in the cursor selection.

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Just an idea ... what happens if you put a COMMIT into the loop, right after an INSERT statement? And enhance EXCEPTION handler such as

    PHP Code:
          ...
          
    INSERT INTO type_class
                      
    (IDVALUENAME
                      
    )
               
    VALUES (c1rec.to_be_inserted_idc1rec.VALUEc1rec.NAME
                      
    );

          
    COMMIT;
       
    END LOOP;

       
    DBMS_OUTPUT.put_line 
       
    ('End time ' || TO_CHAR (SYSDATE'dd/mon/yyyy hh24:mi:ss'));
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT
    .put_line (SQLERRM);
          
    DBMS_OUTPUT.put_line
          
    ('Error on to_be_inserted_id ' || TO_CHAR c1rec.to_be_inserted_id));                   );
    END;

    You said it works allright if inserting in chunks of some 3000 records. Oracle error doesn't seem to be this, but - 3000 * something_of_a_long_type perhaps extends rollback segment to its limit ...

  3. #3
    Join Date
    Feb 2004
    Posts
    16
    Originally posted by Littlefoot
    Just an idea ... what happens if you put a COMMIT into the loop, right after an INSERT statement? And enhance EXCEPTION handler such as

    PHP Code:
          ...
          
    INSERT INTO type_class
                      
    (IDVALUENAME
                      
    )
               
    VALUES (c1rec.to_be_inserted_idc1rec.VALUEc1rec.NAME
                      
    );

          
    COMMIT;
       
    END LOOP;

       
    DBMS_OUTPUT.put_line 
       
    ('End time ' || TO_CHAR (SYSDATE'dd/mon/yyyy hh24:mi:ss'));
    EXCEPTION
       WHEN OTHERS
       THEN
          DBMS_OUTPUT
    .put_line (SQLERRM);
          
    DBMS_OUTPUT.put_line
          
    ('Error on to_be_inserted_id ' || TO_CHAR c1rec.to_be_inserted_id));                   );
    END;

    You said it works allright if inserting in chunks of some 3000 records. Oracle error doesn't seem to be this, but - 3000 * something_of_a_long_type perhaps extends rollback segment to its limit ...

    Thanks for your reply Little foot. I tried to put a COMMIT and the variable declared to capture the value in the exception handler. I still get the same numeric or value error. As mentioned if I try to insert the erroneous record or as long as the result set is less than or equal to 3000 there is no problem.

  4. #4
    Join Date
    Jan 2004
    Posts
    370
    Is there any size restriction or parameter setting on the retrieved result set of a cursor with LONG column?

    The maximum length for a PL/SQL LONG type is 32760 bytes.

    If you have a LONG value in the database greater than 32760 bytes you will get an error when you try to select into the PL/SQL LONG.


    HTH

  5. #5
    Join Date
    Feb 2004
    Posts
    16
    Originally posted by SkyWriter
    The maximum length for a PL/SQL LONG type is 32760 bytes.

    If you have a LONG value in the database greater than 32760 bytes you will get an error when you try to select into the PL/SQL LONG.


    HTH
    Thanks. I did check the length it is within the permissible limit.

  6. #6
    Join Date
    Apr 2004
    Posts
    2
    ...so convert your LONGs to CLOBs!

    create table tab1 (a number, aa long); <-- your old table
    create table tab2 (b number, bb clob); <-- your new table


    insert into tab2 select a, to_lob(aa) from tab1;

    -nick

  7. #7
    Join Date
    Feb 2004
    Posts
    16
    Originally posted by nattah
    ...so convert your LONGs to CLOBs!

    create table tab1 (a number, aa long); <-- your old table
    create table tab2 (b number, bb clob); <-- your new table


    insert into tab2 select a, to_lob(aa) from tab1;

    -nick
    Thanks for your suggestion Nick but it is a big process to get this change done in Production.

Posting Permissions

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