6 Replies Latest reply: Jun 28, 2013 10:36 AM by T. Morton RSS

    Forms 10g truncates data when NLS_LANG=UTF8

    T. Morton

      Hello,

       

      I'm facing a strange behaviour with Forms 10g and NLS_LANG=UTF8.

      DB 11G withcharset in AL32UTF8 on Linux

      Forms 10G on Linux

       

      We are currently upgrading our application form DB 10G to DB 11G + UTF8.

      After coping DB data from 10G to 11G, we changed columns sementics from BYTE to CHAR in DB 11G.

      Migration worked fine.

       

      Now we try to connect our application in Forms 10G to the new DB 11G + UTF8.

      First we faced the error ORA-01461"can bind a long value only for insert into a long column".

      To solve this error, we specified the NLS_LANG=american_america.utf8 in default.env.

      This problem is fixed now.

       

      But, when testing further, I realized that forms truncate the data in my fields.

      For exemple, with an item set with CHAR, length=10, I can enter 10 ASCII char like "xxxxxxxxxx" (10 byte long) but only 5 UTF8 char like "ééééé" (10 byte long too) because the char "é" is encoded with 2 bytes.

       

      Forms truncate the value to match the length in BYTE !

       

      I searched on Web and Metalink but find nothing to explain my problem.

       

      Thanks in advance.

      Tim.

        • 1. Re: Forms 10g truncates data when NLS_LANG=UTF8
          Matthew D.

          Did you try to set NLS_LENGTH_SEMANTICS=CHAR before compiling forms?

           

          Regards

          • 2. Re: Forms 10g truncates data when NLS_LANG=UTF8
            T. Morton

            Thanks for the help. It works !

             

            I created two system variables in my compile script:

             

            NLS_LANG=AMERICAN_AMERICA.UTF8

            Export NLS_LANG

            NLS_LENGTH_SEMANTICS=CHAR

            Export NLS_LENGTH_SEMANTICS

             

            Now I get an another error "ora-01461 can bind a long value only for insert into a long column" because some of my columns use VARCHAR2(4000 CHAR) which have a limit of 4000 BYTES !

            I was not aware of this limit. Here is the explanation of the problem I face: http://stackoverflow.com/questions/5230346/char-semantics-and-ora-01461

             

            I will try, either to fix the length of ITEM to VARCHAR2(2000) In my forms, either to use CLOB with FORMS.

            The first solution should avoid the error because I work with European characters which generally require 2 BYTES. So even if the user enters 2000 "é" which is 2-bytes length in UTF8, that should do the job.

            The second solution is new for me and I'm not sure FORMS will do the job correctly. I will investigate on the web.

            An another solution, could be to use a trigger to check lengthB of new data and raise a custom exception catched/managed inside forms.

             

            But first I will try to force/specify the length semantics = BYTE at FORMS ITEM level for VARCHAR2(4000).

            I saw that FORMS, in this case, will automatically limit the value to 4000 BYTES.

             

            I will give feedback of what I do.

             

            Regards,

            • 3. Re: Forms 10g truncates data when NLS_LANG=UTF8
              Christian Erlinger
              I will try, either to fix the length of ITEM to VARCHAR2(2000) 

              This will only partially fix your problem, there are characters which can have more then 2 bytes.

              If the values you'd like to insert don't exceed the 4000 BYTE limit this should actually work. In some tables we have the same situation; however I am not sure if those tables are accessed via standard forms block behaviour.

               

              anyway; to conclude:

              - your database column is VARCHAR2(4000 CHAR)?

              - you either specified CHAR data length semantics on your forms item or set it to NULL and compiled with NLS_LENGTH_SEMANTICS=CHAR?

              if you specify a data length semantics on the item property this item won't be affected by setting the environment variable at compile time.

              did you also add

               

              NLS_LENGTH_SEMANTICS=CHAR
              

               

              to the default.env?

              This shouldn't affect your PL/SQL code or your items as they have the data length semantics fixed at compile time; however I vaguely remember having a problem with NLS_LENGTH_SEMANTICS at runtime with forms and it won't hurt you if you set it.

              cheers

              • 4. Re: Forms 10g truncates data when NLS_LANG=UTF8
                T. Morton

                Hi Christian,

                 

                Yes my database column is varchar2(4000 CHAR) and NLS_LENGHT_SEMANTICS=CHAR in DB.

                In the forms "Data length semantics" is specified as NULL, so it takes the NLS_LENGTH_SEMANTICS specified in the environment variable at compile time.

                 

                I did a test by forcing/specifing the "Data Length Semantics" to BYTE in my form for a text item with VARCHAR2(4000).

                It works perfectly! Forms automatically limits the length in BYTE so it don't exceed 4000 BYTE and avoid the error "ora-01461 can bind a long value only for insert into a long column".

                 

                Now I will modify the "Data Length Semantics" for all my forms using JDAPI.

                 

                For the NLS_LENGTH_SEMANTICS=CHAR in default.env, I will do some tests to check if it's really needed even if it wont' hurt.

                 

                Thanks for your help.

                Regards,

                • 5. Re: Forms 10g truncates data when NLS_LANG=UTF8
                  Christian Erlinger

                  I wouldn't change the data length semantics property of all items, as most certainly it will work if you have columns << varchar2(4000). If it is the same as in PL/SQL where forms assumes the maximum of 4 bytes for each character when you have an output variable of type varchar2 you should be save with items below varchar2(1000).

                   

                  Just another trap I encountered:

                   

                  create or replace procedure get_data(ovData out varchar2) is
                  begin
                    ovData := lpad('a', 32767, 'a');
                  end;
                  /
                  

                   

                  and in forms

                   

                  declare
                    outvar varchar2(32767);
                  begin
                    get_data(outvar);
                  end;
                  

                   

                  (compiled with NLS_LENGTH_SEMANTICS=CHAR which makes your outvar a varchar2(32767 CHAR)) will result in a numeric or value error once the called procedure returns more then 8191 characters (*NOT* bytes!); however this

                   

                  declare
                    outvar varchar2(32767 BYTE);
                  begin
                    get_data(outvar);
                  end;
                  

                   

                  will work. The reason is simply because forms doesn't know what data is returned from the procedure and simply assumes the maximum of 4 bytes for each character. I guess the same will apply to forms items.

                   

                  cheers

                  • 6. Re: Forms 10g truncates data when NLS_LANG=UTF8
                    T. Morton

                    Thanks for the advice with varchar2(32767). I will check my code for this. Normally I only use Varchar2(32767) in rare cases. so It won't be an issue for me.

                     

                    I finished the JDAPI program to convert all items with CHAR and >=2000 to BYTES.

                    I will try it last week.

                     

                    Regards,