Solved

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

Posted on 2015-02-06
5
498 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 142

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 142

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
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 how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to take different types of Oracle backups using RMAN.

809 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