6 Ответы Последний ответ: 15.11.2019 4:47, автор: Charles Qi

    Assigning value to a column of an explicit cursor results appended space

    Charles Qi

      Hi

           As title suggested, here's a demo code to reproduce the behavior:

      declare
      BEGIN
      for rec in (select 'aaa' col1, '' col2 from dual)
      loop
          dbms_output.put_line(lengthb(rec.col1)); --3
          dbms_output.put_line(rec.col1); --aaa
          rec.col1 := 'a';
          dbms_output.put_line(lengthb(rec.col1)); --expect 1, get 3
          dbms_output.put_line(rec.col1); --2 white spaces appended after 'a'
          dbms_output.put_line(lengthb(rec.col2)); --null
          dbms_output.put_line(rec.col2); --null
          rec.col2 := 'a';
          dbms_output.put_line(lengthb(rec.col2)); --expect 1, will return 4000 in 11g and 32767 in 12c
          dbms_output.put_line(substrb(rec.col2,1,100)); --3999(11g) or 32766(12c) white spaces appended after 'a', substrb is for output clarity only
        end loop;
      end;
      /

      The question is, what is the expected behavior of assigning value to a column of an explicit cursor, is there any documentation about this? (Since I've googled and found nothing about this topic)

      Thanks in advance.

        • 1. Re: Assigning value to a column of an explicit cursor results appended space
          mathguy

          Are you familiar with the difference between the CHAR and the VARCHAR2 data types?

           

          In Oracle, text literals, like 'aaa', are of CHAR data type; specifically, in this case, CHAR(3). If you then assign 'a' to your cursor variable, it will be right-padded with spaces to a length of three, as you have seen. This is really not related to "cursors" - it's the normal behavior of the CHAR data type. (Indeed, if you try to assign the value 'abcd', you will get an error - because CHAR(3) doesn't have enough room to accommodate this longer string.)

           

          The only interesting point is about the treatment of NULL.  If you tried to create a table using the same SELECT statement, you would get an error telling you that columns of zero length are not allowed in such definitions. In a cursor like yours, apparently the length assigned to '' is not zero, but 4000 or 32767 (depending on Oracle version and specific settings.)

           

          What you show as your expectations suggest that you thought your COL1 and COL2 would be VARCHAR2.  You can achieve that result easily, with the CAST function:  (Note - I added a  '<'  at the end of PUT_LINE to show where each string ends, to confirm that no spaces are added to the right.)

           

          declare

          BEGIN

          for rec in (select cast('aaa' as varchar2(4000)) col1, cast('' as varchar2(4000)) col2 from dual)

          loop

              dbms_output.put_line(lengthb(rec.col1)); --3

              dbms_output.put_line(rec.col1 || '<'); --aaa

              rec.col1 := 'a';

              dbms_output.put_line(lengthb(rec.col1)); --expect 1, get 3

              dbms_output.put_line(rec.col1 || '<'); --2 white spaces appended after 'a'

              dbms_output.put_line(lengthb(rec.col2)); --null

              dbms_output.put_line(rec.col2 || '<'); --null

              rec.col2 := 'a';

              dbms_output.put_line(lengthb(rec.col2)); --expect 1, will return 4000 in 11g and 32767 in 12c

              dbms_output.put_line(substrb(rec.col2,1,100) || '<'); --3999(11g) or 32766(12c) white spaces appended after 'a', substrb is for output clarity only

            end loop;

          end;

          /

           

          3

          aaa<

          1

          a<

           

          <

          1

          a<

           

           

          PL/SQL procedure successfully completed.

          1 пользователь считает эту информацию полезной
          • 2. Re: Assigning value to a column of an explicit cursor results appended space
            Charles Qi

            Thank you for your quick and nice response.

            In fact the problem we encountered is the behavior change between 11g and 12c in this situation.

            As you noted the text literal '' in cursor has properties of CHAR(4000) in 11g and CHAR(32767) in 12c, while the parameter of max_string_size in 12c is set to STANDARD.

            This is not consistent with the rule of a text literal in SQL is only allowed up to 4000 bytes while max_string_size=STANDARD.

            What is the purpose of this behavior change?

            • 3. Re: Assigning value to a column of an explicit cursor results appended space
              Paulzip

              From the documentation...

               

              MAX_STRING_SIZE controls the maximum size of VARCHAR2, NVARCHAR2, and RAW data types in SQL.

               

              I don't see any reference to CHAR, do you?

              • 4. Re: Assigning value to a column of an explicit cursor results appended space
                Cookiemonster76

                I think you're looking at a version specific bug actually. I've got a 12.1.0.2.0 instance and max_string_size is set to standard. I get 4000 from your code:

                SQL> select banner from v$version;

                 

                 

                BANNER

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

                Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production

                PL/SQL Release 12.1.0.2.0 - Production

                CORE    12.1.0.2.0      Production

                TNS for Linux: Version 12.1.0.2.0 - Production

                NLSRTL Version 12.1.0.2.0 - Production

                 

                 

                SQL> show parameter max_string_size

                 

                 

                NAME                                 TYPE        VALUE

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

                max_string_size                      string      STANDARD

                SQL> set serveroutput on

                SQL> declare

                  2  BEGIN

                  3  for rec in (select 'aaa' col1, '' col2 from dual)

                  4  loop

                  5      dbms_output.put_line(lengthb(rec.col1)); --3

                  6      dbms_output.put_line(rec.col1); --aaa

                    rec.col1 := 'a';

                  7    8      dbms_output.put_line(lengthb(rec.col1)); --expect 1, get 3

                  9      dbms_output.put_line(rec.col1); --2 white spaces appended after 'a'

                    dbms_output.put_line(lengthb(rec.col2)); --null

                10   11      dbms_output.put_line(rec.col2); --null

                    rec.col2 := 'a';

                    dbms_output.put_line(lengthb(rec.col2)); --expect 1, will return 4000 in 11g and 32767 in 12c

                12   13   14      dbms_output.put_line(substrb(rec.col2,1,100)); --3999(11g) or 32766(12c) white spaces appended after 'a', substrb is for output clarity only

                  end loop;

                15   16  end;

                17  /

                3

                aaa

                3

                a

                4000

                a

                 

                 

                PL/SQL procedure successfully completed.

                 

                 

                SQL>

                • 5. Re: Assigning value to a column of an explicit cursor results appended space
                  mathguy

                  Charles Qi wrote:

                   

                  Thank you for your quick and nice response.

                  In fact the problem we encountered is the behavior change between 11g and 12c in this situation.

                  As you noted the text literal '' in cursor has properties of CHAR(4000) in 11g and CHAR(32767) in 12c, while the parameter of max_string_size in 12c is set to STANDARD.

                  This is not consistent with the rule of a text literal in SQL is only allowed up to 4000 bytes while max_string_size=STANDARD.

                  What is the purpose of this behavior change?

                   

                   

                  Um...  If indeed that is the problem you encountered, you found a funny way to describe it.  Your title says "results appended space", your examples show inputs of length three (in addition to length zero), you even mentioned your expectation in THAT case, and there is no place in your original message where you point specifically to the change in the handling of NULL input between Oracle versions as being the real issue you had questions on. It is very clear to me that your problem was the whole thing about CHAR vs VARCHAR2, not just the specific part about the handling of NULL input. So, why pretend otherwise?

                   

                   

                  Now to your question:

                   

                  Here is what the documentation says about the maximum length of text literals:

                   

                  Text literals have properties of both the CHAR and VARCHAR2 data types

                   

                  .......

                   

                  • A text literal can have a maximum length of 4000 bytes if the initialization parameter MAX_STRING_SIZE=STANDARD, and 32767 bytes if MAX_STRING_SIZE=EXTENDED.

                   

                  (Note to @Paulzip - this is why MAX_STRING_SIZE matters for text literals, even though it does not matter for CHAR.)

                   

                   

                  So - either you are mistaken, and you ran your code when MAX_STRING_SIZE was set to EXTENDED (what that parameter is NOW does not matter, what matters is what it was when you ran the code) - or if indeed you got that behavior while MAX_STRING_SIZE was STANDARD, as you say, then you ran into a bug, as has been suggested already. What is your exact (five parts) Oracle version? Mine, for example, is 12.2.0.1.0. What's yours?

                   

                  Now - I pointed to the documentation where they show the max size of text literals. I don't know where the documentation says that NULL in the definition of a cursor is interpreted as CHAR(MAX_STRING_SIZE); that strikes me as an odd choice - Oracle being Big Brother again, instead of throwing an error as it should.

                   

                  Regardless, you can avoid all these questions by using CAST, as I demonstrated already. You can also cast to CHAR of desired length, if that's what you are after - not just to VARCHAR2.

                  • 6. Re: Assigning value to a column of an explicit cursor results appended space
                    Charles Qi

                    Thanks to everyone's input and sorry to being ambiguous at the beginning.

                     

                    I tried to confirm the settings again and the results as following:

                    1. Oracle version and patch level
                    Patch description:  "Database Jan 2019 Release Update : 12.2.0.1.190115 (28822515)"

                    2. max_string_size value
                    VALUE
                    STANDARD


                    yet, the '' text literal in cursor is still implied as CHAR(32767), maybe indeed is a bug?

                     

                    Since overwrite a value in cursor is not a good practice in my opinion, I'll tell developers not to do so in the future.