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.
Best Answer
Just because you can jump off a cliff, doesn't mean you should.
Use
REPLACE()
with a token:You could take this a step further to allow different format strings:
Note that there have been parsing bugs with long
TO_CHAR
identifiers that have caused ORA-600 errors & session crashes.