00911. 00000 - "invalid character"

Question

When I run this SQL I get this error

ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    The identifier name started with an ASCII character other than a
           letter or a number. After the first character of the identifier
           name, ASCII characters are allowed including "$", "#" and "_".
           Identifiers enclosed in double quotation marks may contain any
           character other than a double quotation. Alternate quotation
           marks (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters. For all other contexts, consult the SQL Language
           Reference Manual.
*Action:   Check the Oracle identifier naming convention. If you are
           attempting to provide a password in the IDENTIFIED BY clause of
           a CREATE USER or ALTER USER statement, then it is recommended to
           always enclose the password in double quotation marks because
           characters other than the double quotation are then allowed.
Error at Line: 2 Column: 1

select trim(ps_scrpt.M_NAME) "Processing Script", trim(actset.M_LABEL) "Batch of table feeders", trim(actset.M_FLTTEMP) "Global Filter", trim(actset.M_TAGDATA) "Label of data",
       trim(scanner_tmpl.M_TEMPLATE) "Scanner Template", trim(bat.M_LABEL) "Table Feeder", trim(feeder_ext.M_OUTPUT) "Reporting Table", trim(feeder_ext.M_MAIN) "M_MAIN", 
       case rep_def.M_TYPE
         when 0 then 'Dynamic Table Based REP table'
         when 1 then 'Data Dictionary Based REP table'
         when 2 then 'SQL Based REP table'
         else 'Unknown Type'
       end as "M_TYPE", 
       trim(rep_dyn.M_DYN_TABLE) "Dynamic Table"
  from ACT_SET_DBF actset, ACT_SETREP_DBF setrep, ACT_BAT_DBF bat, ACT_DYN_DBF feeder_ext, PROCESS#PS_ITEM_DBF ps_item, 
       PROCESS#PS_SCRPT_DBF ps_scrpt, RPO_DMSETUP_TABLE_DBF rep_def, RPO_DMSETUP_DYN_TABLE_DBF rep_dyn, SCANNERCFG_DBF scanner_tmpl
where actset.M_REF = setrep.M_REFSET
   and setrep.M_REFBAT = bat.M_REF
   and bat.M_REF = feeder_ext.M_REF
   and scanner_tmpl.M_REFERENCE (+)= actset.M_SCNTMPL
   and ps_item.M_PARAM_LAB2 (+)= actset.M_LABEL -- '(+)' incase the batch is not attached to a processing script
   and ps_scrpt.M_REF (+)= ps_item.M_REF -- '(+)' incase the batch is not attached to a processing script
   and rep_def.M_LABEL (+)= feeder_ext.M_OUTPUT -- '(+)' incase the feeder/ext does not have a reporting table
   and rep_dyn.M_REFERENCE (+)= rep_def.M_REFERENCE -- '(+)' incase the reporting table does not have a dynamic table
   --and feeder_ext.M_OUTPUT like '%REPORTING_TABLE%' -- reporting table
   --and rep_dyn.M_DYN_TABLE like '%DYN_TABLE%' -- dynamic table
   --and bat.M_LABEL like 'TABLE_FEEDER%' -- table feeder
   and actset.M_LABEL in
(
'E_CS_BF',
'E_DT_BF'
)

I want to get results

Answer 1

While there's nothing visibly wrong, and copying the query from the normal page view doesn't report the same error, if you copy it from the edit view instead the same error comes up.

You can examine that raw code in a hex editor, or use dump('...', 16) to see the characters you actually have. For example, dumping line 2 shows:

select dump('       trim(scanner_tmpl.M_TEMPLATE) "Scanner Template", trim(bat.M_LABEL) "Table Feeder", trim(feeder_ext.M_OUTPUT) "Reporting Table", trim(feeder_ext.M_MAIN) "M_MAIN", ', 16)
from dual;

Typ=96 Len=177: c2,a0,c2,a0,c2,a0,c2,a0,c2,a0,c2,a0,20,74,72,69,6d,28,73,63,61,6e,6e,65,72,5f,74,6d,70,6c,2e,4d,5f,54,45,4d,50,4c,41,54,45,29,20,22,53,63,61,6e,6e,65,72,20,54,65,6d,70,6c,61,74,65,22,2c,20,74,72,69,6d,28,62,61,74,2e,4d,5f,4c,41,42,45,4c,29,20,22,54,61,62,6c,65,20,46,65,65,64,65,72,22,2c,20,74,72,69,6d,28,66,65,65,64,65,72,5f,65,78,74,2e,4d,5f,4f,55,54,50,55,54,29,20,22,52,65,70,6f,72,74,69,6e,67,20,54,61,62,6c,65,22,2c,20,74,72,69,6d,28,66,65,65,64,65,72,5f,65,78,74,2e,4d,5f,4d,41,49,4e,29,20,22,4d,5f,4d,41,49,4e,22,2c,c2,a0

Examining that text shows that you have non-breaking spaces (unicode 00a0, UTF-8 hex c2a0) in quite a few places; six at the start of line 2 (ignoring the four spaces for Markdown formatting), one at the end of that line, six at the start of line 3, eight at the start of line 4, and so on. Those are causing the error, as Oracle isn't interpreting them as plain whitespace, as it would with a normal space or tab or newline.

You may have copied this from some editor that converted normal spaces to non-breaking, or an example that used them for formatting, or you could have typed them yourself though that seems less likely.

You need to either find-and-replace all those characters with a normal space, which you can do in a text editor; or just retype them all. Re-indenting every line would get rid of most of them, but you also need to get rid of the trailing ones on lines 2 and 8.

fiddle, before and after replacing all 88 non-breaking spaces with normal ones. (The 'after' still fails with ORA-00942 of course, as we don't have your tables, but that's fine - the ORA-00911 has gone.) The second line now dumps as:

Typ=96 Len=170: 20,20,20,20,20,20,20,74,72,69,6d,28,73,63,61,6e,6e,65,72,5f,74,6d,70,6c,2e,4d,5f,54,45,4d,50,4c,41,54,45,29,20,22,53,63,61,6e,6e,65,72,20,54,65,6d,70,6c,61,74,65,22,2c,20,74,72,69,6d,28,62,61,74,2e,4d,5f,4c,41,42,45,4c,29,20,22,54,61,62,6c,65,20,46,65,65,64,65,72,22,2c,20,74,72,69,6d,28,66,65,65,64,65,72,5f,65,78,74,2e,4d,5f,4f,55,54,50,55,54,29,20,22,52,65,70,6f,72,74,69,6e,67,20,54,61,62,6c,65,22,2c,20,74,72,69,6d,28,66,65,65,64,65,72,5f,65,78,74,2e,4d,5f,4d,41,49,4e,29,20,22,4d,5f,4d,41,49,4e,22,2c,20