TRANSLATE having line breaks is giving error from sqlplus but not from TOAD
Hi
We have a view creation script that has a TRANSLATE with a line break
TRANSLATE(GL_JE_LINES.REFERENCE_2, '1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-_=+{}[]?~
@&;:/?>.<|!"?$^*()##?`,'' ', '1') INVOICE_ID
When I am executing the view creation script from sqlplus (unix or dos) I am getting an error.
SP2-0310: unable to open file "&;:/?>.<|!"?$^*()##?,''",''"
SP2-0310: unable to open file "&;:/?>.<|\!"?$^*()##?
, TRANSLATE(GL_JE_LINES.REFERENCE_2, '1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-_=+{}[]?~
*
Issue can be replicated simply by executing from sql prompt:
select TRANSLATE(dummy, '1ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-_=+{}[]?~
@&;:/?>.<|!"?$^*()##?`,'' ', '1') COL1 from dual;
Workaround:
1)If the script is executed from TOAD or SQL*Developer then the script is successfully creating the view (that is how it was originally created).