Home » SQL & PL/SQL » Client Tools » SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114
SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283541] Tue, 27 November 2007 07:44 Go to next message
bmpeter
Messages: 4
Registered: November 2007
Junior Member
Hi

I have a problem when the sql is longer than 1152 signs/letters (without spaces). It started yesterday where a lot of views etc. suddenly didn't work. They have worked fine severel weeks now, but something is suddently wrong or something has changed?!

The error messages I get is ORA-03135 and ORA-3114. The funny thing is that it dosn't matter if letter no.1153 is outcomment or not...

EX:

Select
column1, column2....etc.

-- If I have 1150 letters in the hole sql-sentense and I
-- add the word test here it will fail... test

From
Table1

__________________________

I'm not a DBA, but have wrote my DBA, but he have no clue jet...
If anybody knows anything that could help, please help Surprised)

I'm connecting to the DB with Business Obejct and TOAD ver.8.6 and both systems works fine on any other oracle database...

Regards
bmpeter
Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283558 is a reply to message #283541] Tue, 27 November 2007 08:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is a problem from your client program not from Oracle.
Try the same with SQL*Plus.

Regards
Michel

[Updated on: Tue, 27 November 2007 08:12]

Report message to a moderator

Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283572 is a reply to message #283541] Tue, 27 November 2007 08:45 Go to previous messageGo to next message
bmpeter
Messages: 4
Registered: November 2007
Junior Member
Hi Michel

I did try with SQL* Plus (worksheet) and it was the same... Surprised(

But in the last five minutes we have flushed the database and recreating it with the same testdata, because we were a lot of people that could not develop, so our problem is solved, but not in a beautiful way, sorry...


Thanks for your time anyway Surprised)

Regards
bmpeter
Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283573 is a reply to message #283572] Tue, 27 November 2007 08:46 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

But in the last five minutes we have flushed the database and recreating it with the same testdata,
Can you explain us what you did?
Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283574 is a reply to message #283572] Tue, 27 November 2007 08:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
And what is your Oracle version (4 decimals)?
And the value of cursor_sharing parameter?

Regards
Michel

[Updated on: Tue, 27 November 2007 08:56]

Report message to a moderator

Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283580 is a reply to message #283541] Tue, 27 November 2007 09:21 Go to previous messageGo to next message
bmpeter
Messages: 4
Registered: November 2007
Junior Member
Hi

I just asked the DBA and there were a lot of open connections that where not closed (apox. 130 - he said). It was not the programs I used (BO, TOAD, SQL* Plus), but a VB.Net application that made all the connections...

I was mistaken about the "recreate-thing", The DBA simply turn off and later turned on the Oracle DB... and now it works fine!?

It is version 10.2.030... but he didn't know anything about cursor_sharing parameters?
(and I do not know what it is either)


So, now we will close all the connections in the VB.NET appl. and maybe the problem will not come again - hopefully...

regards
bmpeter
Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283581 is a reply to message #283580] Tue, 27 November 2007 09:25 Go to previous messageGo to next message
Mohammad Taj
Messages: 2412
Registered: September 2006
Location: Dubai, UAE
Senior Member

The DBA simply turn off and later turned on the Oracle DB...
Smile what do you mean exactly ?

Just connect with system user to database
and on sql> prompt
SQL> show parameter cursor_sharing 

Put output here whatever you got.

maybe the problem will not come again - hopefully

But what was the actual problem ?

[Updated on: Tue, 27 November 2007 09:25]

Report message to a moderator

Re: SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283593 is a reply to message #283581] Tue, 27 November 2007 10:19 Go to previous message
bmpeter
Messages: 4
Registered: November 2007
Junior Member
Mohammad Taj wrote on Tue, 27 November 2007 09:25

The DBA simply turn off and later turned on the Oracle DB...
Smile what do you mean exactly ?


He shut down the database and restarted it, i guess... that was what he told me... I didn't actually see it myself.

Quote:


Just connect with system user to database
and on sql> prompt
SQL> show parameter cursor_sharing 

Put output here whatever you got.



I have no system user available sorry Sad
(I will try to ask the DBA again another time, but his a really busy man, so it will not be right now...)

Quote:


maybe the problem will not come again - hopefully
But what was the actual problem ?


The problem should be described in the start of this thread... But I will describe it a little more here:

I use BO (Business Objects) to make reports and BO auto generates a lot of sql.
Suddenly I got errors in reports that did work fine yesterday, and the day before. Some of the reports though did work and others didn't - very strange...
I captured the sql and executed it in TOAD and SQL* Plus and found out that, if the sql sentence reached a lenght of exactly 1153 letters (counted without spaces) it failed!
If I only had 1152 letters in the sql sentence it worked just fine...

Furthermore I found out, that it didn't matter if letter, number 1153 was out-comment (don't know the word in english when you use -- to make a comment in your sql, to explain what you are doing)!?

It was the length of the sql sentence that made the error!?

The DBA said, that as far as he knows nothing was changed on the server from yesterday, until today...

But in the end, when we couldn't figure out what else to do, he just turned off the server application and restarted it again and by that also killed all the open connections in the database...

I hope that explained the problem in more detail, because I really can't describe it in any further detail...(I think)
(Naturally, I do not know if anybody is hiding something from me, but I really don't think so)

Regards
bmpeter
Previous Topic: Strange behaviour!!!!
Next Topic: Connecting to Oracle Server with TOAD
Goto Forum:
  


Current Time: Thu Dec 08 20:01:39 CST 2016

Total time taken to generate the page: 0.18420 seconds