1 2 Previous Next 22 Replies Latest reply: Feb 20, 2014 8:12 AM by BSalesRashid RSS

    Trim abnormal Behavior.

    BSalesRashid

      Hello folks.

       

      I have the following situation here:

       

      DESC tablex

      OBJREF                       NUMBER(15)

      OBJREF1        NUMBER(15) 

      OBJREF2              NUMBER(15)

      OBJREF3       NUMBER(15)

      OBJREF4        NUMBER(15)

      PRMCALCULO                 VARCHAR2(4000)

      DTH1              DATE

      VLR1                      NUMBER(15,2)

      DSCNT                    NUMBER(15,2)

      VVPDR              NUMBER(15,2)   

      VVREA      NUMBER(15,2)

      WFK_USUINCL                  VARCHAR2(30)

      WFK_DTAINCL                  DATE

      WFK_USUALTE                  NUMBER(15)

      WFK_DTAALTE                  DATE

      OBJREF5        NUMBER(15)

      SITUACAO                     NUMBER(1)

      MEMCALC               VARCHAR2(4000)

       

      The user application prints a report with many blank spaces,

      On homologation environment, the user asked me to execute this script:

       

      DECLARE

         v_OBJREF    tablex.OBJREF%TYPE;

         V_QUATIDADECOMIIT INTEGER;

         CURSOR itemcobranca_cursor IS

            SELECT objref

            FROM tablex

            ORDER BY OBJREF DESC ;

      BEGIN

        V_QUATIDADECOMIIT := 0 ;

         OPEN itemcobranca_cursor;

         LOOP

            FETCH itemcobranca_cursor INTO v_OBJREF;

       

       

            UPDATE tablex SET  MEMCALC= Trim ( MEMCALC) WHERE objref = v_OBJREF;

       

       

            V_QUATIDADECOMIIT := V_QUATIDADECOMIIT + 1;

       

       

            IF V_QUATIDADECOMIIT = 1000 THEN

              COMMIT;

              V_QUATIDADECOMIIT := 0;

            END IF;

       

       

            EXIT WHEN itemcobranca_cursor%NOTFOUND;

       

       

         END LOOP;

       

       

         COMMIT;

         CLOSE itemcobranca_cursor;

      END;

       

      Well, looks like this script do a TRIM in all values at table tablex, in column MEMCALC, with objective to eliminate de blank spaces.

       

      It executed succesfully, but.. i read the alert.log:

       

      Errors in file /u01/xxxx/admin/udump/dbhom_ora_3668.trc:

      ORA-00600: internal error code, arguments: [kafspa:columnBuffer2], [4506], [4000], [], [], [], [], []

       

      Reading the .trc:

       

      ksedmp: internal or fatal error

      ORA-00600: internal error code, arguments: [kafspa:columnBuffer2], [4506], [4000], [], [], [], [], []

      Current SQL statement for this session:

      insert into itemcobranca2 (SELECT * FROM ownerx.tablex WHERE situacao = 2)

       

      Well, everytime this SELECT is executed or called, it shows this ORA-600 error.

       

      Researching around google, i figured it out that this ORA-0600 error means that in some fields the datatype limited has been trespassed. The maximum varchar2 is 4000 (bytes) and after the TRIM it gets 4506.

       

      Now my question: How the function TRIM (that suppose to reduce the number of characters eliminating blank spaces) could make the field trespass the datatype limit, if before the trim was working normally ?

       

      I hope i've been clear enough.

       

      Thanks in advance.

        • 1. Re: Trim abnormal Behavior.
          sb92075

          NEVER do in PL/SQL that which can be done in plain SQL

           

          UPDATE tablex

          SET    memcalc = Trim (memcalc)

          WHERE  objref IN (SELECT objref

                            FROM   tablex)


          or even simpler


          UPDATE tablex

          SET    memcalc = Trim (memcalc)

           

           

          • 2. Re: Trim abnormal Behavior.
            BSalesRashid

            Yeah, i could say this to them, but i didn't. Anyway.. this changes the behavior of TRIM function ?

            • 3. Re: Trim abnormal Behavior.
              jgarry

              Perhaps the data in there is being expanded due to character set issues?  What are all the NLS settings, server, client running the code, and original client that entered the data?

               

              The extra spaces having to be trimmed may be a clue.

              • 4. Re: Trim abnormal Behavior.
                BSalesRashid

                Hi,

                Sorry about the delay, i took some days off.

                 

                The NLS parameters:

                SELECT * FROM NLS_DATABASE_PARAMETERS;

                 

                PARAMETER                      VALUE                                

                ------------------------------ ----------------------------------------

                NLS_LANGUAGE                   AMERICAN                               

                NLS_TERRITORY                  AMERICA                                

                NLS_CURRENCY                   $                                      

                NLS_ISO_CURRENCY               AMERICA                                

                NLS_NUMERIC_CHARACTERS         .,                                     

                NLS_CHARACTERSET               WE8ISO8859P15                          

                NLS_CALENDAR                   GREGORIAN                              

                NLS_DATE_FORMAT                DD-MON-RR                              

                NLS_DATE_LANGUAGE              AMERICAN                               

                NLS_SORT                       BINARY                                 

                NLS_TIME_FORMAT                HH.MI.SSXFF AM                         

                NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM               

                NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                     

                NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR           

                NLS_DUAL_CURRENCY              $                                      

                NLS_COMP                       BINARY                                 

                NLS_LENGTH_SEMANTICS           BYTE                                   

                NLS_NCHAR_CONV_EXCP            FALSE                                  

                NLS_NCHAR_CHARACTERSET         UTF8                                   

                NLS_RDBMS_VERSION              10.2.0.1.0

                 

                The client whose ran the PL/SQL:

                SQL*Plus: Release 10.2.0.1.0

                 

                That's weird because before the TRIM it was ok (with lots of blank spaces, but working).

                 

                Thanks for replying.

                • 5. Re: Trim abnormal Behavior.
                  sybrand_b

                  You SHOULD say this to them, as this 3GL style code is unscalable and will result in ora-1555 errors, because of committing inside a loop.

                   

                  ----------

                  Sybrand Bakker

                  Senior Oracle DBA

                  • 6. Re: Trim abnormal Behavior.
                    BSalesRashid

                    Not sure if i understood you sir,

                    You mean that comitting inside a loop is causing this problem?

                    • 7. Re: Trim abnormal Behavior.
                      Eduardo Legatti

                      Hi,

                       

                      Researching around google, i figured it out that this ORA-0600 error means that in some fields the datatype limited has been trespassed. The maximum varchar2 is 4000 (bytes) and after the TRIM it gets 4506.

                       

                      ORA-0600 can be a bug or anything that is not normal. Did you tell what Oracle version are you using? I noticed that you are using SQL*Plus 10.2 version. If you have access to MOS (My Oracle Support) you can try to see the note ORA-00600 [kafspa:columnBuffer2] (Doc ID 1417053.1)

                       

                      Cheers

                       

                      Legatti

                      • 8. Re: Trim abnormal Behavior.
                        BSalesRashid

                        Hi,

                        Sorry not mention before, but the version of this database is a 10.2.0.1 Standard Edition.

                         

                        I've already checked this note.

                         

                        Thanks.

                        • 9. Re: Trim abnormal Behavior.
                          JuanM

                          Hi,

                           

                          Try reading the Oracle Docs. Maybe you can find a clue.

                           

                          TRIM

                           

                           

                          Edit:

                          Both trim_character and trim_source can be any of the datatypes CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is of VARCHAR2 datatype if trim_source is a character datatype and a LOB if trim_source is a LOB datatype. The return string is in the same character set as trim_source.

                          Maybe the returned String still overhead the 4000 characters.

                           

                          Can you post the Datatype and Length of your table column?

                           

                          Regards

                           

                          Juan M

                          • 10. Re: Trim abnormal Behavior.
                            Jonathan Lewis

                            The statement that you've listed as part of the error message in the alert log doesn't appear to be in the PL/SQL that you've posted. What makes you think it is related ?

                             

                             

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Trim abnormal Behavior.
                              jgarry

                              Try BLANK_TRIMMING

                               

                              Also, select max(length(memcalc)) from tablex; and run csscan on it to see if they give any clues.  The dump command may also be informative. DUMP

                               

                              It could very well be you are simply hitting a bug in your unpatched version.

                              • 12. Re: Trim abnormal Behavior.
                                BSalesRashid

                                Hi Juan:

                                You could see at the beginning of the post, but here it is:


                                MEMCALC               VARCHAR2(4000)

                                 

                                I am sure it still overhead 4000 characters, the problem is: Why this happened after the TRIM ? Before the TRIM it was ok.

                                Thanks for replying

                                • 13. Re: Trim abnormal Behavior.
                                  BSalesRashid

                                  Hi,

                                  Running that sql statement, after the PL/SQL (Trimming the column), invokes the error.

                                   

                                  Thanks for replying.

                                  • 14. Re: Trim abnormal Behavior.
                                    JuanM

                                    Hi,

                                     

                                    You could see at the beginning of the post, but here it is:


                                    MEMCALC               VARCHAR2(4000)

                                    Sorry, unformatted code is dificult to read it.

                                     

                                    insert into itemcobranca2 (SELECT * FROM ownerx.tablex WHERE situacao = 2)

                                     

                                    Can you post the describe of table itemcobranca2?

                                     

                                    Are the tables itemcobranca2 and tablex in the same database?

                                     

                                    Regards

                                     

                                    Juan M

                                    1 2 Previous Next