RE: ORA-01722 until shared pool is flushed

Okay, the problem recurred today and I was surprised to see that flushing the shared pool resolved the problem again. Since the package which is generating the error is now pinned it would not be cleaned out by the shared pool flush. I'm stumped.            

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]
Sent: Friday, March 31, 2006 1:51 PM
To: oracle-l_at_freelists.org
Subject: RE: ORA-01722 until shared pool is flushed  

Thanks Dennis,  

Several people have responded privately, including a few who experienced similar problems but everyone either upgraded or retired the database. One person had a resolution for the infrequent database link error that we experience (setting an event on the 9i side) but since I haven't seen that error in over 6 months I'm not going to bother implementing it.  

I pinned the package two days ago and added it to our startup package pinning routine so I'll see if that helps. We don't usually get the error more than once every week or two so it will be a while before I can see if it helped and the database won't be restarted for another 6 weeks for it be pinned on startup (which I greatly prefer to pinning a package after the database has been open for a long time).  

What I find most interesting from the article you linked to is "It generally happens in SQL only
(during a query) not in plsql (plsql throws a different exception for this
error). " since it's definitely happening from a pl/sql package.        

Thanks,

Jay Miller

Sr. Oracle DBA

x68355  

-----Original Message-----

From: Dennis Williams [mailto:oracledba.williams_at_gmail.com] Sent: Friday, March 31, 2006 11:33 AM
To: Miller, Jay
Cc: oracle-l_at_freelists.org
Subject: Re: ORA-01722 until shared pool is flushed  

Jay,  

I don't see where anyone responded to your posting, so I'll give less than expert opinion.

So you're getting an "invalid number" error? Review this AskTom discussion. Apparently this ORA-01772 can occur in some strange ways.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:45012 348053
Since you say that flushing the shared pool seems to alleviate the problem for awhile, this problem might somehow be related to the shared pool getting fragmented. You might consider pinning this stored procedure in memory.  

Dennis Williams  

This message is confidential and sent by TD Waterhouse solely for use by the intended recipient. If you are not the intended recipient, you are hereby notified that any use, distribution or copying of this communication is strictly prohibited. This should not be deemed as an offer or solicitation, to buy or sell any product. Any 3rd party information contained herein was prepared by sources deemed reliable, but is not guaranteed. TD Waterhouse does not accept electronic instructions that would require an original signature. Information received by or sent from TD Waterhouse is stored, subject to review, and may be produced to regulatory authorities or others with a legal right to such.

--
http://www.freelists.org/webpage/oracle-l