Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> ORA-00911: invalid character, Multiple Update Set Where Statements
DarthVain
post Apr 4 2012, 03:11 PM
Post #1


Newbie
*

Group: Members
Posts: 3
Joined: 4-April 12
Member No.: 47,034



Why would this work just fine:

UPDATE AUTHORITY_STATUSES
SET EFFECTIVE_DATE = TO_DATE ('21/12/2011', 'DD/MM/YYYY')
WHERE ID = 303788;

While this doesn't work:

UPDATE AUTHORITY_STATUSES
SET EFFECTIVE_DATE = TO_DATE ('21/12/2011', 'DD/MM/YYYY')
WHERE ID = 303788;
UPDATE AUTHORITY_STATUSES
SET EFFECTIVE_DATE = TO_DATE ('28/11/2011', 'DD/MM/YYYY')
WHERE ID = 303552;
UPDATE AUTHORITY_STATUSES
SET EFFECTIVE_DATE = TO_DATE('02/11/2011', 'DD/MM/YYYY')
WHERE ID = '303328';

And gives me the following error message:

ORA-00911: invalid character

It's driving me nuts.
Go to the top of the page
 
+Quote Post
burleson
post Apr 4 2012, 04:52 PM
Post #2


Advanced Member
***

Group: Members
Posts: 11,099
Joined: 26-January 04
Member No.: 13



>> ORA-00911: invalid character


Thuis is usally due to a Widnwos tic mark (leabning tics) ir some other unprintable character: Please read:

http://www.dba-oracle.com/sf_ora_00911_invalid_character.htm


You have three updates there. Run them one at a time and re-post the one with the error!


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
burleson
post Apr 4 2012, 04:54 PM
Post #3


Advanced Member
***

Group: Members
Posts: 11,099
Joined: 26-January 04
Member No.: 13



Is this a number or a character?



You are treating it both ways:

WHERE ID = '303328';

WHERE ID = 303552;


--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post
DarthVain
post Apr 5 2012, 01:42 PM
Post #4


Newbie
*

Group: Members
Posts: 3
Joined: 4-April 12
Member No.: 47,034



QUOTE (burleson @ Apr 4 2012, 05:52 PM) *
>> ORA-00911: invalid character


Thuis is usally due to a Widnwos tic mark (leabning tics) ir some other unprintable character: Please read:

http://www.dba-oracle.com/sf_ora_00911_invalid_character.htm


You have three updates there. Run them one at a time and re-post the one with the error!



That is the problem. I can take any of those statements (of hundreds) and they all seem to run individually. However when I want to run the batch of them, it fails and gives me that error.

I'm trying to see if it is software dependent, as I am using an old version of TOAD, and an old version of SQL Plus, but I'm awaiting Oracle email for my forgotten password to their site (to download a new version of SQL Plus to try it on).
Go to the top of the page
 
+Quote Post
DarthVain
post Apr 5 2012, 01:45 PM
Post #5


Newbie
*

Group: Members
Posts: 3
Joined: 4-April 12
Member No.: 47,034



QUOTE (burleson @ Apr 4 2012, 05:54 PM) *
Is this a number or a character?



You are treating it both ways:

WHERE ID = '303328';

WHERE ID = 303552;


Yeah sorry about that. That was me playing around with what may or may not be going wrong. The original script has it all like:

WHERE ID = '303328';

I though maybe it was a data type text VS number type issue. Apparently it makes no difference, as both work just as well either way if I run them by themselves. It is only when I try to run multiple statements that it has a problem.
Go to the top of the page
 
+Quote Post
burleson
post Apr 6 2012, 08:03 AM
Post #6


Advanced Member
***

Group: Members
Posts: 11,099
Joined: 26-January 04
Member No.: 13



Hi Darth,

>> It is only when I try to run multiple statements that it has a problem.

Well, Oracle does not lie, he is encountering an invalid character.

Does it work if you only run two of them?



--------------------
Hope this helps. . .

Donald K. Burleson
Oracle Press author
Author of Oracle Tuning: The Definitive Reference
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 23rd April 2014 - 02:30 PM