Solved

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

Posted on 2015-02-06
5
520 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Turn Insights into Action

Communication across every corner of your business is essential to increase the velocity of your application delivery and support pipeline. Automate, standardize, and contextualize your communication processes with xMatters.

Question has a verified solution.

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

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 post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
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
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

717 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