revo1059
asked on
Oracle parse error
Our developer wrote a program that is dealing with a large amount of data from one DB and pushing it to another DB (after converting it from MSSQL. The statement she is using is:
SELECT MYFIELD_SEQ.NEXTVAL NEW_ID FROM DUAL;
We've noticed that the program that is issuing this statement slows down after some time. Looking in the DB logs we see:
2019-12-04T10:34:18.937766 -06:00
MO(4):WARNING: too many parse errors, count=1026276 SQL hash=0x23acadbc
MO(4):PARSE ERROR: ospid=9860, error=904 for statement:
2019-12-04T10:34:18.937766 -06:00
MO(4):SELECT "NEW_ID", ROWID InternalRowid FROM DUAL
MO(4):Additional information: hd=0000012B2BB4CF00 phd=0000012B869366F8 flg=0x20 cisid=110 sid=110 ciuid=110 uid=110
It looks like Oracle is adding the " " to NEW_ID. The command works without the quotes
The client is 11.2.0.4 (because we are still using oo4o) and the database we are converting into is 12.2. How do we resolve so it doesn't add the quotes?
SELECT MYFIELD_SEQ.NEXTVAL NEW_ID FROM DUAL;
We've noticed that the program that is issuing this statement slows down after some time. Looking in the DB logs we see:
2019-12-04T10:34:18.937766
MO(4):WARNING: too many parse errors, count=1026276 SQL hash=0x23acadbc
MO(4):PARSE ERROR: ospid=9860, error=904 for statement:
2019-12-04T10:34:18.937766
MO(4):SELECT "NEW_ID", ROWID InternalRowid FROM DUAL
MO(4):Additional information: hd=0000012B2BB4CF00 phd=0000012B869366F8 flg=0x20 cisid=110 sid=110 ciuid=110 uid=110
It looks like Oracle is adding the " " to NEW_ID. The command works without the quotes
The client is 11.2.0.4 (because we are still using oo4o) and the database we are converting into is 12.2. How do we resolve so it doesn't add the quotes?
ASKER
It's translating the statement into the one that is generating the parse error in the log. If I run that statement it complains about in SQL without the quotes it returns the expected value
The too many parse errors is a new "warning" that shows up in the alert log. If you read up on it, it was likely happening on previous versions just not being recorded in the log. Everything I've read says it can be ignored most of the time.
The original sql to get the nextval from the sequence must be working or you would know it.
The "bad" sql might be from the optimizer just trying different things out. Might be from something completely unrelated.
I don't think the parse error warnings has anything to do with your slowdown in performance.
The original sql to get the nextval from the sequence must be working or you would know it.
The "bad" sql might be from the optimizer just trying different things out. Might be from something completely unrelated.
I don't think the parse error warnings has anything to do with your slowdown in performance.
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
What problems do you thing the double quotes is causing? Don't see how they will affect performance.