ORA-01483 Error: Invalid length for DATE or NUMBER bind variable

Morning All:

We're running into a random error that Oracle support (through some researching, not directly) indicated that we needed to increase the cursor_sharing, cached_cursor sizes and possiblly double the global shared area.

1. How do I check the current values for all of these?
2. How do I go about modifying these. I know the global shared area requires a restart.

Thanks in advance.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jinesh KamdarCommented:
When does this error exactly occur ?
   Connect to the database as a DBA i.e. using a DBA account and issue the following statements at the SQL prompt.
show parameter cursor_sharing
show prameter cached_cursor
and you can modify the values using
alter system set <parameter_name> = <value> scope=both;
AWarrenMAuthor Commented:
Hi Jinesh: the error occurs randomly on data inserts. Never the same statement twice, always in a different situation.

Badri, I  HAVE to be logged in under the DBA account to do this? There's no other way?

Also: what about the shared global area?

A follow on to this is to inquire as to how to clear out the cache as it appears to be part of the issue.

The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

AWarrenMAuthor Commented:
Given the lack of responses, maybe 200 points is too little?
Jinesh KamdarCommented:
ORA-01483: invalid length for DATE or NUMBER bind variable
Cause: A bind variable of type DATE or NUMBER is too long.
Action: Consult your manual for the maximum allowable length.


Refer Note # 444045.1 on Meta-Link for more details.
Jinesh KamdarCommented:
Sorry, I meant Note # 465497.1
AWarrenMAuthor Commented:
The insert statement isn't the issue, the values are allowable lengths.

It has to do with the cursor sharing and cached curors on the DB server along with the SGA allocated... I don't know how to check those or what appropriate values should be.

Jinesh KamdarCommented:
As Badri noted in his post, access to those parameters can be obtained only as a DBA.
Get ur DBA to check those parameters for u.
AWarrenMAuthor Commented:
Right, but noone confirmed that it was the only way; via DBA privelages.

Also, what's the guidance for the options in question? What are the typical settings for most servers? What's optimal?

When I asked this question yesterday it was in a rush. I've done some research on the side (given I had time post work hours yesterday) and understand what these settings are for conceptually; I just don't have a clue as to the best way to us/optimize them.
AWarrenMAuthor Commented:
Please close this question as the questions above were posed to workaround various Oracle 10.1 bugs that I'm no longer encountering since we upgraded to 10.2.

Oracle bugs related are:
3668164, 4056515, 4082347, 6085625


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jinesh KamdarCommented:
You have to close this question yourself by selecting your final comment as a solution to get a refund.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.