7 Replies Latest reply: May 4, 2012 7:58 AM by user12104683 RSS

    insert into with problems

    stefanik
      Hy,
      i have a little question. Try this:

      create table strangetab (
      id varchar2(20),
      textfield varchar2(200)
      )
      /
      CREATE OR REPLACE PROCEDURE writestrangetab
      (
      id VARCHAR2,
      textfield varchar2 default null)
      IS
      pragma autonomous_transaction;
      BEGIN

      INSERT INTO strangetab
      (id, textfield
      ) VALUES
      (id, textfield
      );
      COMMIT;

      EXCEPTION
      WHEN OTHERS THEN
      dbms_output.put_line(sqlerrm);
      rollback;

      END;
      /

      After table and procedure creation, try to run this script (simple insert into in autonompus_transaction):

      set serveroutput on
      --
      declare
      vstr varchar2(10000);
      begin

      for i in 1..2000
      loop
      vstr := vstr||'r';
      end loop;

      dbms_output.put_line('write a');
      writestrangetab( 'idx' , 'a');
      --
      dbms_output.put_line('write big string');
      writestrangetab( 'idx' , vstr);
      --
      dbms_output.put_line('write b');
      writestrangetab('idx' , 'b');
      --
      end;
      /

      The script generate this output:

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      write a
      write big string
      ORA-12899: value too large for column "STRANGETAB"."TEXTFIELD" (actual:
      2000, maximum: 200)
      write b


      As i expect, the second write goes wrong but first and third no and it put string a and b into srangetable.

      Now try to run this:


      set serveroutput on
      --
      declare
      vstr varchar2(10000);
      begin

      for i in 1..5000
      loop
      vstr := vstr||'r';
      end loop;

      dbms_output.put_line('write a');
      writestrangetab( 'idx' , 'a');
      dbms_output.put_line('write big string');
      writestrangetab( 'idx' , vstr);
      dbms_output.put_line('write b');
      writestrangetab('idx' , 'b');

      end;
      /

      The only difference from the previous is the vstr dimension: first 2000 now 5000.
      In my db (10.2.0.4 on linux) the output of this, is:

      Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options

      write a
      write big string
      ORA-01461: can bind a LONG value only for insert into a LONG column
      write b
      ORA-01001: invalid cursor


      If i query the strangetable i am non able to find string b (Only string a was registered)!
      As if that were not enough, If you try to insert others writestrangetab, anything after wath that produce the error will be stored!
      It is normal?

      By
      Stefano
        • 1. Re: insert into with problems
          hitgon
          ORA-12899: value too large for column "STRANGETAB"."TEXTFIELD" (actual:
          2000, maximum: 200)

          http://www.dba-oracle.com/t_ora_12899_value_too_large_for_column.htm
          • 2. Re: insert into with problems
            stefanik
            Tanks for your reply but the problem is not the "value too large" but the invalid cursor of the third writestrangetable after passing over then 4000 chars to the stored!


            By
            Stefano
            • 3. Re: insert into with problems
              hitgon
              Hi,

              need to increase the size of column inside the table because maximum size in column is 200 and you trying to insert the data of 2000

              ALTER TABLE STRANGETAB
              MODIFY (TEXTFIELD VARCHAR2(4000));

              Edited by: hitgon on May 2, 2012 6:46 PM
              • 4. Re: insert into with problems
                stefanik
                Hy hitgon,
                if you look at my first post you will see that the problem that i'm trying to solve is not the ORA-12899.
                I expect the ORA-12899 and i deliberated cause this error to demostrate the problem of the second run: the invalid cursor of the third write, that causes the miss of the write string b (in the second run).


                Tanks
                Stefano
                • 5. Re: insert into with problems
                  25449
                  The difference has to do with the size of the string you are tying to pass. Varchar max size is 2000 characters. In your first run, you passed a valid varchar to your procedure which then generated the error about the field size and caught it with your when others clause.

                  The second run you are trying to pass 5000 characters to a varchar, which caused your error when trying to call your procedure, which caused the error outside of the procedure and thus outside of your when others clause. That is why your cursor because invalid.
                  • 6. Re: insert into with problems
                    stefanik
                    Tanks for your reply Jhon, but:

                    1) "The second run you are trying to pass 5000 characters to a varchar, which caused your error when trying to call your procedure"
                    Why you say "which caused". When i call a procedure can't pass more than 4000 Chars? I know that pl/sql varchar2 is up to 32000 Chars even in procedure calls!

                    2) "That is why your cursor because invalid"
                    Witch cursor? The strange thing is that there isn't any cursor in the autonomous procedure! The anly statement is an insert into!

                    The problem of the second miss in the second run is ok: that wont register the string. The main problem is the miss of the third in the seconf run.
                    Why it not register the third writestrangetab? In that case i pass only a 'b' char. Writestarngetab Is an autonomous transaction so i expect a miss if it fail, but the next, if ok, will be registerd. why not?


                    By
                    Stefano
                    • 7. Re: insert into with problems
                      user12104683
                      OK.
                      Today i opened a SR to oracle and they told me that the problem seems to be resolved applying the patch 10.2.0.5 for 10gR2 or switching to 11gR2.
                      On 10.2.0.4, 11.1.0.6 and 11.1.0.7 the problem probably persist.


                      By
                      Stefano