PL/SQL (MOSC)

MOSC Banner

TRANSLATE having line breaks is giving error from sqlplus but not from TOAD

edited Nov 14, 2025 8:41AM in PL/SQL (MOSC) 2 commentsAnswered ✓

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).

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center