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?
AWarrenMConnect With a Mentor Author 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

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;
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.

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.
Jinesh KamdarCommented:
You have to close this question yourself by selecting your final comment as a solution to get a refund.
All Courses

From novice to tech pro — start learning today.