Is it safe to use an output (to_char) date format string of more than 22 characters (ORACLE 11) The ORACLE SQL manual (Page 2-58 of the main 11.1 SQL Manual) states:


  The total length of a datetime format model cannot exceed 22 characters.


However, I've been able to use a format string for TO_CHAR with many more characters, almost all of which are text "literals".

Is this safe (ORACLE 11)? (Perhaps the limit does not include non-date literals?)

For example:

SELECT TO_CHAR(sysdate,
       '"SOMESTRING_"YYYYMMDD"_SOME LOTS MORE STRINGS OF A FILENAME PREFIX"') 
FROM DUAL;


Result:

SOMESTRING_20200226_SOME LOTS MORE STRINGS OF A FILENAME PREFIX


It also works with dates from tables.

The reason I want to do this is to build a filename template in a database column; otherwise I will need to put the date format in a separate field.

P.S. Please tell me if this should be asked in a different community. I've never asked a question before, so I have no reputation to lose :-). Be nice.
Just because you can jump off a cliff, doesn't mean you should.

Use REPLACE() with a token:

select REPLACE('SOMESTRING_##DATE##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX',
               '##DATE##',
               TO_CHAR(sysdate,'YYYYMMDD'))
from DUAL; 


You could take this a step further to allow different format strings:

with mytable as (
select 'SOMESTRING_##YYYYMMDD##_SOME LOTS MORE STRINGS OF A FILENAME PREFIX' as format_string
from DUAL
)
select REGEXP_REPLACE(format_string,'##(.*)##',TO_CHAR(sysdate,replace(
                    regexp_substr( format_string, '##(.*)##', 1, 1),'##','')))
  from mytable;


Note that there have been parsing bugs with long TO_CHAR identifiers that have caused ORA-600 errors &amp; session crashes.