You Asked

declare
    raw_hash  RAW(20);
begin
    /* 4000 + 1. */
    select standard_hash(    [literal string of length 4000]
                          || '-'
                        )
      into raw_hash
      from dual
    ;
end;
/
-- ORA-01489: result of string concatenation is too long
-- ORA-06512: at line 6

declare
    str_4000  VARCHAR2(4000);
    str_4001  VARCHAR2(4001);
begin  
    str_4000 := [literal string of length 4000];

    /* 4000 + 1. */
    select    str_4000
           || '-'
      into str_4001
      from dual
    ;

end;
/
-- ORA-01489: result of string concatenation is too long
-- ORA-06512: at line 8

declare
    raw_hash  RAW(20)       ;
    str_4000  VARCHAR2(4000);
begin
    str_4000 := [literal string of length 4000];

    /* 4000 + 1. */
    select standard_hash(    str_4000
                          || '-'
                        )
      into raw_hash
      from dual
    ;

end;
/
PL/SQL procedure successfully completed.

Why does the last block not raise an exception when the first two blocks raise an exception?

Note: [literal string of length 4000] is to be replaced with a literal string of length 4000 single byte characters.

Note: The Live SQL with 19c is actually generating the exception for all three blocks, but I cannot figure out how to create a shareable link. Live SQL with 23ai is not generating any error for any of the three blocks. Live SQL Classic is not generating any error and that was the only shareable link I could figure out how to create. Additionally, for some reason this form would not accept the Share Link: https://livesql.oracle.com/ords/livesql/s/dwj01z7skbs7oml6jdjgdlpwq .

The purpose of this question is to learn about the concepts of PL/SQL illustrated in the question and not so much about how to perform a specific task.

and Connor said...

Most likely this is due to database settings, eg

SQL> declare
  2      raw_hash  RAW(20);
  3  begin
  4      /* 4000 + 1. */
  5      select standard_hash(    rpad('x',4000)
  6                            || '-'
  7                          )
  8        into raw_hash
  9        from dual
 10      ;
 11  end;
 12  /
declare
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 5


SQL>
SQL> declare
  2      str_4000  VARCHAR2(4000);
  3      str_4001  VARCHAR2(4001);
  4  begin
  5      str_4000 := rpad('x',4000);
  6
  7      /* 4000 + 1. */
  8      select    str_4000
  9             || '-'
 10        into str_4001
 11        from dual
 12      ;
 13
 14  end;
 15  /
declare
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 8


SQL>
SQL> declare
  2      raw_hash  RAW(20)       ;
  3      str_4000  VARCHAR2(4000);
  4  begin
  5      str_4000 := rpad('x',4000);
  6
  7      /* 4000 + 1. */
  8      select standard_hash(    str_4000
  9                            || '-'
 10                          )
 11        into raw_hash
 12        from dual
 13      ;
 14
 15  end;
 16  /
declare
*
ERROR at line 1:
ORA-01489: result of string concatenation is too long
ORA-06512: at line 8


SQL>
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
max_string_size                      string      STANDARD

versus this

SQL> declare
  2      raw_hash  RAW(20);
  3  begin
  4      /* 4000 + 1. */
  5      select standard_hash(    rpad('x',4000)
  6                            || '-'
  7                          )
  8        into raw_hash
  9        from dual
 10      ;
 11  end;
 12  /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2      str_4000  VARCHAR2(4000);
  3      str_4001  VARCHAR2(4001);
  4  begin
  5      str_4000 := rpad('x',4000);
  6
  7      /* 4000 + 1. */
  8      select    str_4000
  9             || '-'
 10        into str_4001
 11        from dual
 12      ;
 13
 14  end;
 15  /

PL/SQL procedure successfully completed.

SQL>
SQL> declare
  2      raw_hash  RAW(20)       ;
  3      str_4000  VARCHAR2(4000);
  4  begin
  5      str_4000 := rpad('x',4000);
  6
  7      /* 4000 + 1. */
  8      select standard_hash(    str_4000
  9                            || '-'
 10                          )
 11        into raw_hash
 12        from dual
 13      ;
 14
 15  end;
 16  /

PL/SQL procedure successfully completed.

SQL>
SQL> show parameter max_string_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------
max_string_size                      string      EXTENDED

Rating

(1 rating)

Comments

My Mistake

A reader, August 26, 2025 - 12:59 am UTC

Thank you, Connor. My max_string_size is STANDARD. I am able to reproduce your results with rpad() where all three blocks raise an exception. This morning I am not able to reproduce my results with the literal where the third block did not raise an exception. That is, all three of my blocks with the literal are (now) also raising an exception. Not sure what I was seeing/doing yesterday causing me to think there was no exception being thrown for the third block. I thought I was seeing some difference between concatenating a literal versus concatenating a variable (or rpad). I am glad to see that this is not the case and that all three raise an exception as would be expected in the max_string_size=STANDARD case. Sorry for taking up your time and thanks for your assistance in resolving this drama for me.

August 27, 2025 - 2:53 am UTC

Glad to help

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

My Mistake

More to Explore

PL/SQL demos

PL/SQL docs

standard_hash() Concatenated Argument

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

My Mistake

More to Explore

PL/SQL demos

PL/SQL docs