Solved

Oracle 11g - Query error since Oracle SQL Developper version 4.0.3.16

Posted on 2015-02-06
5
510 Views
Last Modified: 2015-02-07
Hi

I have thie below query that was working before with an older Oracle SQL DEV version, but since i have the new version 4.0.3.16, i get this error:

ORA-00911: invalid character
00911. 00000 -  "invalid character"
*Cause:    identifiers may not start with any ASCII character other than
           letters and numbers.  $#_ are also allowed after the first
           character.  Identifiers enclosed by doublequotes may contain
           any character other than a doublequote.  Alternative quotes
           (q'#...#') cannot use spaces, tabs, or carriage returns as
           delimiters.  For all other contexts, consult the SQL Language
           Reference Manual.
*Action:
Error at Line: 27 Column: 29

Do you know how i can fix this?

This error occurs when a special character is used in a SQL WHERE clause and the value is not enclosed in single quotations, but in my case, it is.

Thanks

SELECT LOAD_START_DATE_PERIOD,
  LOAD_START_DATE_YEAR,
  RATE_GROUP_ID,
  ORIGIN,
  DEST,
  BILL,
  LOAD_ID,
  LOAD_TYPE,
  TRIP_MODE_TYPE,
  CARRIER_ID,
  CARRIER_NAME,
  EQUIPMENT_TYPE_ID,
  NUM_STOPS,
  MILES,
  CUBE,
  WEIGHT,
  PCS,
  PALLETS,
  PLANNED_FUEL_ACCESS_COST,
  PLANNED_LINEHAUL_COST,
  PLANNED_TOTAL,
  PAID_FUEL_ACCESS_COST,
  PAID_LINEHAUL_COST,
  PAID_FUEL_ACCESS_COST ,
  PAID_LINEHAUL_COST
FROM TMMGR.TEMP_TRANS_FIN_ACT_RPT
WHERE LOAD_START_DATE_PERIOD = '1'
AND LOAD_START_DATE_YEAR     = '2015' 
AND STATUS                   = '70' ORDER  BY LOAD_START_DATE_PERIOD,
  LOAD_START_DATE_YEAR,
  RATE_GROUP_ID,
  bill;

Open in new window

0
Comment
Question by:Wilder1626
  • 3
  • 2
5 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 40593554
please check (with a hexdump) if you don't have the character ascii #160 in the sql somewhere ...
it displays like a space, but it is not a space and will give exactly that error
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40593569
Hi

I,m not to much of a pro on SQL as i normally do basic SQL queries.

Can you please guide me on how i can check with hexdump?
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 40593652
it has nothing to do with "sql", per se.

you put the sql "text" into a notepad, and open the file with a hexeditor (like Notepad++, ultraedit ...)
you will be able to see some "space" character that is ascii 160 ( hex code is A0 )
replace that with a regular space (ascii code 32, hex code is 20 ), and the sql should then run
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40593692
Funny thing happened. I rebooted my laptop and now, the same query works.
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40595879
Thanks again for your help.

always learning. :)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to take different types of Oracle backups using RMAN.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question