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

Tip of today: Switch to modern, explicit `JOIN` syntax. Easier to write (without errors), easier to read (and maintain), and easy to convert to outer join (as needed.) — jarlh, Feb 10 '23 at 10:00
1.1. Remove everything from `select` list, put a constant (`1`) and execute it. 1.2. Remove everything from `where` clause and execute it. 1.2. Remove everything from `from` list and execute it. This whay you'll at least find *a place* of error. Then go to step 2. 2. Remove an expression/table/condition depending on the step 1 result. This way you'll find erroneous text. Then you may verify it by your eyes and documentation or ask a question about **this exact** part of text — astentx, Feb 10 '23 at 10:05
Probably some character we don't see. Maybe it helps to merely put this into an editor and set the character set to ASCII7 or the like. Anyway, from the outdated syntax we can assume that we are looking at a query here that was probably written in the 1980s. Why not write a proper query from scratch now? That isn't much work, and you'll get a readable query from that. — Thorsten Kettner, Feb 10 '23 at 10:09
And a note on the above `join` remarks: you may use a feature of SQL developer to thansform this ancient syntax to the ANSI joins https://stackoverflow.com/a/69284420/2778710 — astentx, Feb 10 '23 at 10:14
This commonly happens when you copy SQL from somewhere else, like form a MS Teams chat or some email clients. And there's nothing wrong with your syntax... you just have to find the invisible non-printing character. Sometimes pasting it into NotePad and then copying it back out will remove it, sometimes not. It is sometimes easier to simply rewrite it from scratch. — Paul W, Feb 10 '23 at 12:23

Alex Poole · Answer 1 · 2023-02-10T10:29:38.913

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

00911. 00000 - "invalid character"

1 Answers1