Starting from 12c, Oracle Database provides a way to control the max byte-size of a NVARCHAR2 column. To use up to 32767 bytes instead of 4000, one has to set the parameter max_string_size to EXTENDED.

Now, let's say I create dynamically tables from a Java application and I always want to use the max size for an NVARCHAR2 column, how can I get the max current value?

I know, I can do a select like

SELECT value
  FROM v$parameter
 WHERE name = 'max_string_size'

and check if the value is EXTENDED, but to me it looks more professional and robust if I can replace a statement like

CREATE TABLE mytable (mycolumn NVARCHAR2(32767))


CREATE TABLE mytable (mycolumn NVARCHAR2($system_var_for_max_size))

So, is there any system variable that stores the max size that I can use as in the previous example?

  • To my knowledge, checking max_string_size is the only way to do it. – a_horse_with_no_name Feb 19 '19 at 9:41
  • Have you checked to see if NVARCHAR2(max) behaves appropriately? – Philᵀᴹ Feb 19 '19 at 9:41
  • @Philᵀᴹ: NVARCHAR2(max) is invalid in Oracle – a_horse_with_no_name Feb 19 '19 at 10:18
  • I've been using other RDBMSes too much! 🤭 – Philᵀᴹ Feb 19 '19 at 10:55
  • Hey, thanks for all the feedbacks. It is a pity that the Oracle crew did not provide such a feature. That's it :) – JeanValjean Feb 19 '19 at 12:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.