Question

Oracle Max_String_Size

  • 20 August 2020
  • 6 replies
  • 763 views
ctaylor56
Rank
Userlevel 5
Badge +14

Has anyone ever changed the Max String Size parameter in Oracle to Extended?  I know the standard VARCHAR2 limitation in Oracle is 4000 bytes.  I have had a few customers ask for Custom Fields and standard fields to be larger such as the external notes on the service quotation and Long Description for PM Work Orders.  Apparently in Oracle, if you change this parameter to extended it gives you a new max size of 32767 bytes.  I was curious if anyone in the community has done this successfully?  If so, was there any special items you might want to mention?  Did this also work when adding new custom fields after changing the parameter?

 

https://dbaclass.com/article/max_string_size-parameter-oracle-12c/

Don't Count the Days, Make the Days Count

6 replies

NickPorter
Rank
Userlevel 6
Badge +18

I personally wouldn’t risk making this change unless absolutely necessary - while you may be able to extend the length of the fields, depending on how IFS uses that field it could cause serious impacts in basic functionality.

If you do need to, you’ll have to very carefully test all areas use that/those field including reports etc to ensure the system doesn’t start generating bad data without it being immediately obvious (e.g. it might concatenate fields and end up truncating part of the longer data without generating any error)

Nick

ctaylor56
Rank
Userlevel 5
Badge +14

I personally wouldn’t risk making this change unless absolutely necessary - while you may be able to extend the length of the fields, depending on how IFS uses that field it could cause serious impacts in basic functionality.

If you do need to, you’ll have to very carefully test all areas that/those field including reports etc to ensure the system doesn’t start generating bad data without it being immediately obvious (e.g. it might concatenate fields and end up truncating part of the longer data without generating any error)

Nick

Thanks Nick.  I might see if I can do this in a Race environment and test what the outcome is. 

Don't Count the Days, Make the Days Count
NickPorter
Rank
Userlevel 6
Badge +18

I’d be interested to know the outcome or any issues you encounter; please add a follow up once you’ve tested it.  Thanks!

ctaylor56
Rank
Userlevel 5
Badge +14

For sure.  I’ll keep this thread updated.

Don't Count the Days, Make the Days Count
durette
Rank
Userlevel 7
Badge +18

This change moves the storage of the larger strings out-of-line from the other columns in the record, much like they do with LOBs. That will cause extra logical reads for the bigger records.

http://www.dba-oracle.com/t_max_string_size.htm

“WARNING:  You MUST run utl32k.sql immediately after changing max_string_size=extended, else you risk invalidating the database columns.”

If you find this forum useful, please consider paying back to the community in your own areas of expertise. -- Kevin Durette - https://www.linkedin.com/in/kevindurette
Jonas Feigl
Rank
Userlevel 5
Badge +17

This parameter is specifically not supported by IFS as is mentioned in the Foundation1 Documentation under “IFS Development Guide > Core Server > Base Server Development > Localization and Internationalization > Database Character Set”:

 

Note: Storing VARCHAR2 of size 32767 in the database by setting the MAX_STRING_SIZE to Extended is not supported.

Reply


Terms & ConditionsCookie settings