OraFAQ Forum: Client Tools » SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114

Home » Other » Client Tools » SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SQL sentense longer than 1152 signs, make an ORA-03135 and ORA-3114 [message #283541]

Tue, 27 November 2007 07:44

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

Report message to a moderator

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

Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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

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?

Report message to a moderator

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

Michel Cadot
Messages: 68734
Registered: March 2007
Location: Saint-Maur, France, https...

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

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

Report message to a moderator

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

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

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

Report message to a moderator

Previous Topic:	Strange behaviour!!!!
Next Topic:	Connecting to Oracle Server with TOAD

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Feb 12 00:54:19 CST 2025