Ora 06502 pl sql numeric or value error bulk bind truncated bind - Исправление ошибок и поиск оптимальных решений проблем

Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article.

ORA-06502 Cause

The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things:

A value is being assigned to a numeric variable, but the value is larger than what the variable can handle.
A non-numeric value is being assigned to a numeric variable.
A value of NULL is being assigned to a variable which has a NOT NULL constraint.

Let’s take a look at the solutions for each of these causes.

The solution for this error will depend on the cause.

Let’s see an example of each of the three causes mentioned above.

Solution 1: Value Larger than Variable (Number Precision Too Large)

In this example, we have some code that is setting a numeric variable to a value which is larger than what can be stored.

Let’s create this procedure which declares and then sets a variable:

CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
  testNumber NUMBER(3);
BEGIN
  testNumber := 4321;
END;

If we compile it, it compiles with no errors.

Procedure TESTLARGENUMBER compiled

Now, let’s run the procedure.

EXEC TestLargeNumber;

We get an error:

Error starting at line : 8 in command -
EXEC TestLargeNumber
Error report -
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "SYSTEM.TESTLARGENUMBER", line 5
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

The error we’ve gotten is “ORA-06502: PL/SQL: numeric or value error: number precision too large”. It also includes an ORA-06512, but that error just mentions the next line the code is run from, as explained in this article on ORA-06512.

This is because our variable testNumber can only hold 3 digits, because it was declared as a NUMBER(3). But, the value we’re setting it to a few lines later is 4 digit long (4321).

So, the value is too large for the variable.

To resolve it, increase the size of your variable, or manipulate your value to fit the size of the variable (if possible).

In our example , we can change the size of the variable.

CREATE OR REPLACE PROCEDURE TestLargeNumber
AS
  testNumber NUMBER(4);
BEGIN
  testNumber := 4321;
END;

Procedure TESTLARGENUMBER compiled

Now, let’s run the procedure.

EXEC TestLargeNumber;

PL/SQL procedure successfully completed.

The procedure runs successfully. We don’t get any output (because we didn’t code any in), but there are no errors.

Read more on the Oracle data types here.

Solution 2: Non-Numeric Value

Another way to find and resolve this error is by ensuring you’re not setting a numeric variable to a non-numeric value.

For example, take a look at this function.

CREATE OR REPLACE PROCEDURE TestNonNumeric
AS
  testNumber NUMBER(4);
BEGIN
  testNumber := 'Yes';
END;

Procedure TESTNONNUMERIC compiled

The procedure compiles successfully. Now, let’s fun the function.

EXEC TestNonNumeric;

Error starting at line : 8 in command -
EXEC TestNonNumeric
Error report -
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "SYSTEM.TESTNONNUMERIC", line 5
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

The error we get is “ORA-06502: PL/SQL: numeric or value error: character to number conversion error”.

This happens because our variable testNumber is set to a NUMBER, but a few lines later, we’re setting it to a string value which cannot be converted to a number

To resolve this error:

Ensure the value coming in is a number and not a string.
Convert your string to a number using TO_NUMBER (the conversion might happen implicitly but this may help).
Convert your string to the ASCII code that represents the string using the ASCII function.
Change the data type of your variable (but check that your code is getting the right value first).

The solution you use will depend on your requirements.

Solution 3: NOT NULL Variable

This error can appear if you try to set a NULL value to a NOT NULL variable.

Let’s take a look at this code here:

CREATE OR REPLACE PROCEDURE TestNonNull
AS
  testNumber NUMBER(4) NOT NULL := 10;
  nullValue NUMBER(4) := NULL;
BEGIN
  testNumber := nullValue;
END;

Procedure TESTNONNULL compiled

Now, the reason we’re using a variable to store NULL and not just setting testNumber to NULL is because we get a different error in that case. Besides, it’s probably more likely that your NULL value will come from another system or a database table, rather than a hard-coded NULL value.

Let’s run this function now.

Error starting at line : 9 in command -
EXEC TestNonNull
Error report -
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at "SYSTEM.TESTNONNULL", line 6
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    An arithmetic, numeric, string, conversion, or constraint error
           occurred. For example, this error occurs if an attempt is made to
           assign the value NULL to a variable declared NOT NULL, or if an
           attempt is made to assign an integer larger than 99 to a variable
           declared NUMBER(2).
*Action:   Change the data, how it is manipulated, or how it is declared so
           that values do not violate constraints.

We get the ORA-06502 error.

This error message doesn’t give us much more information. But, we can look at the code on line 6, as indicated by the message. We can see we have a variable that has a NOT NULL constraint, and the variable is NULL.

To be sure, we can output some text in our demo when it is null.

CREATE OR REPLACE PROCEDURE TestNonNull
AS
  testNumber NUMBER(4) NOT NULL := 10;
  nullValue NUMBER(4) := NULL;
BEGIN
  IF (nullValue IS NULL) THEN
    dbms_output.put_line('Value is null!');
  ELSE
    testNumber := nullValue;
  END IF;
END;

Now let’s call the procedure.

EXEC TestNonNull;

Value is null!

The output shows the text message, indicating the value is null.

ORA-06502 character string buffer too small

This version of the error can occur if you set a character variable to a value larger than what it can hold.

When you declare character variables (CHAR, VARCHAR2, for example), you need to specify the maximum size of the value. If a value is assigned to this variable which is larger than that size, then this error will occur.

For example:

DECLARE
  charValue VARCHAR2(5);
BEGIN
  charValue := 'ABCDEF';
END;

If I compile this code, I get an error:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 4

This happens because the variable is 5 characters long, and I’m setting it to a value which is 6 characters long.

You could also get this error when using CHAR data types.

DECLARE
  charValue CHAR(5);
BEGIN
  charValue := 'A';
  charValue := charValue || 'B';
END;

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 5

This error happens because the CHAR data type uses the maximum number of characters. It has stored the value of A and added 4 space characters, up until its maximum value of 5.

When you try to concatenate a value of B to it, the resulting value is ‘A B’, which is 6 characters.

To resolve this, use a VARCHAR2 variable instead of a CHAR, and ensure the maximum size is enough for you.

ORA-06502: pl/sql: numeric or value error: null index table key value

Sometimes you might get this error message with the ORA-06502 error:

ORA-06502: pl/sql: numeric or value error: null index table key value

This means that either:

Your index variable is not getting initialized, or
Your index variable is getting set to NULL somewhere in the code.

Check your code to see that neither of these two situations are happening.

ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind

You might also get this specific error message:

ORA-06502: pl/sql: numeric or value error: bulk bind: truncated bind

This is caused by an attempt to SELECT, UPDATE, or INSERT data into a table using a PL/SQL type where a column does not have the same scale as the column in the table.

For example, you may have declared a variable in PL/SQL to be VARCHAR2(100), but your table is only a VARCHAR2(50) field. You may get this error then.

You may also get this error because some data types in PL/SQL have different lengths in SQL.

To resolve this, declare your variables as the same type as the SQL table:

type t_yourcol is table of yourtable.yourcol%TYPE;

So, that’s how you resolve the ORA-06502 error.

Lastly, if you enjoy the information and career advice I’ve been providing, sign up to my newsletter below to stay up-to-date on my articles. You’ll also receive a fantastic bonus. Thanks!

Источник

Bulk Bind: Truncated bind

I recently had a good reminder on one of the subtleties of PL/SQL bulk fetches and inserts. I kept getting this error on the FETCH:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

The procedure purpose is to bulk insert data from a temporary staging table to the real, properly-sized column Load table. Simple, right? Except when I use the wrong table types.

My problem?

The elements I was selecting in my cursor did not logically fit into the table type target of my cursor fetch.

I defined my cursor using a query on the temporary staging table (larger VARCHAR2 columns), not the real table (exact-fit VARCHAR2, NUMBER and DATE columns).
But, I defined my table type to receive the cursor results on my real table:

CURSOR c_dl IS SELECT ... ... ... FROM my_staging_table stg WHERE action IN ( 'INSERT','UPDATE'); TYPE my_load_table_tt IS TABLE OF my_load_table%ROWTYPE INDEX BY PLS_INTEGER; load_data_arr my_load_table_tt;
BEGIN OPEN c_dl; LOOP FETCH c_dl BULK COLLECT INTO load_data_arr LIMIT 1000; ----- Error happens here! FORALL ii IN 1 .. load_data_arr.COUNT INSERT INTO my_load_table ... ...

The Bulk Bind: Truncated Bind error is because the cursor – with bigger elements, cannot logically fit in the defined array/table type – the columns are too big.

To correct this, I changed the definition of the table type to be a table of the staging table. Then the FETCH proceeds fine, as does the INSERT.

But wait, why didn’t the INSERT fail, since I am Inserting from a (potentially) large-column cursor into the exact-fit table?

The INSERT proceeds because the elements being inserted fit – the transforms and validations (processed previous to this point in the code) have ensured that all elements are indeed the size they should be in the exact-fit table. So the INSERT does not complain.

LOG ERRORS REJECT LIMIT UNLIMITED

Now, IF one column in one row was too large, or the wrong format for a date, the entire INSERT would have failed. Unless I used the LOG ERRORS clause, which I did.

IF one or more records is/are bad, I want to load all the good records, and single out the bad. To do this, I used the LOG ERRORS REJECT LIMIT UNLIMITED clause at the end of the INSERT statement:

INSERT INTO my_load_table VALUES ( ..load_data_arr(ii).col1, load_data_arr(ii).col2, load_data_arr(ii).col3, ... load_data_arr(ii).coln) LOG ERRORS REJECT LIMIT UNLIMITED;

This LOG ERRORS REJECT LIMIT UNLIMITED clause ensures that all clean rows will insert, and all “bad” rows get logged to an error table, in my case called ERR$MY_LOAD_TABLE.

I created the error log table previously, using the command:
BEGIN DBMS_ERRLOG.create_error_log (dml_table_name => 'MY_LOAD_TABLE'); END;

We define the staging tables with generous VARCHAR2s on purpose. We want to allow leeway in the incoming data, knowing we can strip punctuation, perform lookups, format dates, and perform other transformations in the validation stage. That means doing all that validation is on us, to make sure the data is in shape for the final insert. Just one way of doing things – there are many other approaches.

My reminder is – especially when writing things quickly – to pay attention to the nuances of the features in use. I won’t make that mistake for a while now :).

For a full treatment of bulk processing with BULK COLLECT and FORALL, see Steve Feuerstein’s article PL/SQL 101: Bulk Processing with BULK COLLECT and FORALL.

Источник

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Filed under: PL/SQL — hourim @ 3:18 pm

Have you ever been faced to the above error? I mean this bizarre Bulk Bind: Truncated Bind? This is a strange and unclear error message. Let me put you in the context that gave me this error. I am trying to load data into an 11gR2 (11.2.0.3.0) database from data coming via a db link from a 10gR2 (10.2.0.5.0). This is not more as what I have simplified to the maximum here below

11gR2> begin
2   for x in (
3    select v2_col
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

begin
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 2

And if instead I issue a straightforward select from a SQL windows

11gR2>
select v2_col
from  distant_table;

I have no errors.

I was struggling with this issue until one of my smart colleagues puts me into the right direction: there is a difference in the character set between the 11g (multi-byte) and the 10g database (single byte) as shown below

11gR2> select * from nls_database_parameters;
PARAMETER                      VALUE
------------------------------ ----------------------------
NLS_CHARACTERSET               AL32UTF8    --> here multibyte
NLS_LENGTH_SEMANTICS           CHAR        --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

10gR2> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ------------------------------------
NLS_CHARACTERSET               WE8ISO8859P1  --> here single byte
NLS_LENGTH_SEMANTICS           BYTE          --> here
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.5.0

I was thinking that the 11gR2 PL/SQL engine (because still I am not fetching anything explicitly, I am just selecting) could recognize this character set difference between the local and the distant database and implicitly declare its local variable so that it could have enough space to contain incoming column when this one will have special characters .

Having smart colleagues is always a lucky situation, because not only my colleague pointed me to the right direction but gave me a workaround that I am pleased to reproduce here below :

11gR2> begin
2   for x in (
3    select v2_col ||''
4    from  distant_table
5    )
6   loop
7     null;
8  end loop;
9  end;
10  /

PL/SQL procedure successfully completed.

The work around consists of concatenating the distant column v2_col with a null string. This magically overcome the Bulk Bind: Truncated Bind error (don’t tell me why the PL/SQL engine can overcome this error when I have used such a concatenation).

If you have already been faced to this error then I will be pleased to know how you managed to solved it

Источник

Содержание

TH TECHNOLOGY
Oracle Consulting Services, specializing in Application Express (APEX) and legacy development tool conversions.
Bulk Bind Reminder: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
Bulk Bind: Truncated bind
LOG ERRORS REJECT LIMIT UNLIMITED
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
Answers
Mohamed Houri’s Oracle Notes
September 17, 2013
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.
Answers
Jared Still’s Ramblings
Thursday, March 13, 2008
ORA-6502 «Bulk Bind: Truncated Bind» error

TH TECHNOLOGY

Oracle Consulting Services, specializing in Application Express (APEX) and legacy development tool conversions.

Bulk Bind Reminder: ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

Bulk Bind: Truncated bind

I recently had a good reminder on one of the subtleties of PL/SQL bulk fetches and inserts. I kept getting this error on the FETCH:

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

The procedure purpose is to bulk insert data from a temporary staging table to the real, properly-sized column Load table. Simple, right? Except when I use the wrong table types.

The elements I was selecting in my cursor did not logically fit into the table type target of my cursor fetch.

I defined my cursor using a query on the temporary staging table (larger VARCHAR2 columns), not the real table (exact-fit VARCHAR2, NUMBER and DATE columns).
But, I defined my table type to receive the cursor results on my real table:

CURSOR c_dl IS
SELECT .
.
.
FROM my_staging_table stg
WHERE action IN ( ‘INSERT’,’UPDATE’);
TYPE my_load_table_tt IS TABLE OF my_load_table%ROWTYPE
INDEX BY PLS_INTEGER;
load_data_arr my_load_table_tt;

BEGIN
OPEN c_dl;
LOOP
FETCH c_dl BULK COLLECT INTO load_data_arr LIMIT 1000; —— Error happens here!
FORALL ii IN 1 .. load_data_arr.COUNT
INSERT INTO my_load_table
.
.

The Bulk Bind: Truncated Bind error is because the cursor – with bigger elements, cannot logically fit in the defined array/table type – the columns are too big.

To correct this, I changed the definition of the table type to be a table of the staging table. Then the FETCH proceeds fine, as does the INSERT.

But wait, why didn’t the INSERT fail, since I am Inserting from a (potentially) large-column cursor into the exact-fit table?

LOG ERRORS REJECT LIMIT UNLIMITED

Now, IF one column in one row was too large, or the wrong format for a date, the entire INSERT would have failed. Unless I used the LOG ERRORS clause, which I did.

IF one or more records is/are bad, I want to load all the good records, and single out the bad. To do this, I used the LOG ERRORS REJECT LIMIT UNLIMITED clause at the end of the INSERT statement:

INSERT INTO my_load_table
VALUES ( ..load_data_arr(ii).col1,
load_data_arr(ii).col2,
load_data_arr(ii).col3,
.
load_data_arr(ii).coln)
LOG ERRORS REJECT LIMIT UNLIMITED;

This LOG ERRORS REJECT LIMIT UNLIMITED clause ensures that all clean rows will insert, and all “bad” rows get logged to an error table, in my case called ERR$MY_LOAD_TABLE.

I created the error log table previously, using the command:
BEGIN
DBMS_ERRLOG.create_error_log (dml_table_name => ‘MY_LOAD_TABLE’);
END;

W e define the staging tables with generous VARCHAR2s on purpose. We want to allow leeway in the incoming data, knowing we can strip punctuation, perform lookups, format dates, and perform other transformations in the validation stage. That means doing all that validation is on us, to make sure the data is in shape for the final insert. Just one way of doing things – there are many other approaches.

My reminder is – especially when writing things quickly – to pay attention to the nuances of the features in use. I won’t make that mistake for a while now :).

For a full treatment of bulk processing with BULK COLLECT and FORALL, see Steve Feuerstein’s article PL/SQL 101: Bulk Processing with BULK COLLECT and FORALL.

Источник

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

I’m facing following error while trying to take backup of a table having long column.
Here is my table for which I’m trying to create backup
I’ve created a backup table
But when I’m trying to insert using below code
I’m getting this error I can understand that this is just because of LONG datatype but how to overcome this??Pls help me

Answers

Guess is that the bulk collect and binding chokes on the LONG data type — that has long since been deprecated ifo LOBs and only exists for backwards compatibility.

But why are you reading data from the SQL engine, push it into the PL/SQL engine (via a bulk fetch) and then send that very same data back to the SQL engine via an insert?

A lot of work to be done, when the code and data can stay inside the SQL engine, without having to push and pull data to and from the PL/SQL engine — and thereby simply making this approach of your slow and unscalable.

Sorry,I didnt get you..I’m making use of bulk collect here..

I didnt get this point

What’s your database version?

Depending on the answer, you may want to consider this alternative :

1) Create your back-up table with CA_MEMO as CLOB instead of LONG datatype (deprecated as Billy said) : 2) Copy the data with a single, straightforward INSERT, using TO_LOB function for the conversion :

Yeah Its fine..now I did the same but I’m facing the below error
while trying for reversal with the following code
i.e again restoring data from cp_ca_memorandum_memo_tmp
to cp_ca_memorandum

My version of DB is

Use the COPY command in SQL*Plus. Although it’s quite old-fashioned, it supports the LONG datatype.

I cant make use of copy command just because of

This is no magic that makes data processing faster. Bulk processing does a single thing only for performance gain. It reduces the context switching between the PL/SQL and SQL engines.

What is better than this? Not having to context switch at all.

You are sorely mistaken if you think that bulk processing automagically makes data processing faster. It does not.

The data is read using SQL, via the SGA’s buffer cache. You use a PL/SQL bulk collect call. The data is copied from the SGA buffer cache to a local variable in dedicated PGA memory. You now make a SQL insert call. The very same data you just copied from the buffer cache and the SQL engine, now needs to be copied back as bind variable in order to create the new rows.

This is a slow approach.

It is better and faster and more scalable to use only SQL to do the copy. No need to step into PGA space and into the PL/SQL engine.

Maximize SQL. Minimize PL/SQL.

Источник

Mohamed Houri’s Oracle Notes

September 17, 2013

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind

And if instead I issue a straightforward select from a SQL windows

I have no errors.

Having smart colleagues is always a lucky situation, because not only my colleague pointed me to the right direction but gave me a workaround that I am pleased to reproduce here below :

If you have already been faced to this error then I will be pleased to know how you managed to solved it

Источник

ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.

I’m running a table conversion for some changes

It is pulling data from an existing table and placing it in a _PRJ version with no changes other than creating a new account number. The source table has already had it’s data validated without error before this conversion happens.

I get through 357,402 rows and then get ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.

I can’t find any values out of whack in the source table (it was already validated when it was loaded).

Any ideas what can cause this?

Here’s the loop that’s doing it. CUST is the cursor, I set c_FetchLimit to 1 to find the row doing it.

LOOP
fetch CUST BULK COLLECT into CUSTH limit c_FetchLimit;
exit FETCH_LOOP when CUSTH.count = 0;
forall D in CUSTH.first..CUSTH.last
insert into CHBW_PRJ values CUSTH(D)
;

i_TotInsertCnt := i_TotInsertCnt + CUSTH.count;
commit;

Answers

user7385944 wrote:
I’m running a table conversion for some changes

I get through 357,402 rows and then get ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind.

I can’t find any values out of whack in the source table (it was already validated when it was loaded).

Any ideas what can cause this?

Here’s the loop that’s doing it. CUST is the cursor, I set c_FetchLimit to 1 to find the row doing it.

LOOP
fetch CUST BULK COLLECT into CUSTH limit c_FetchLimit;
exit FETCH_LOOP when CUSTH.count = 0;
forall D in CUSTH.first..CUSTH.last
insert into CHBW_PRJ values CUSTH(D)
;

i_TotInsertCnt := i_TotInsertCnt + CUSTH.count;
commit;

>
Here’s the loop that’s doing it. CUST is the cursor, I set c_FetchLimit to 1 to find the row doing it.
>
And did you find the row doing it?

Then try a simple ‘INSERT INTO myTarget SELECT * FROM mySource’ that only selects that one row.

Also compare the DDL for the source and target tables to see if they match exactly. The exception you are getting is one that often occurs when people store data in a column of the wrong datatype: date values in VARCHAR2 columns, numbers in VARCHAR2 columns. Then when you, or Oracle, do a conversion like TO_NUMBER or TO_DATE on the data either explicitly or implicitly it fails.
>
The source table has already had it’s data validated without error
>
Maybe your validation process has a ‘hole’ in it or someone altered the data AFTER it was validated.
>
Could this be an issue with the DB?
>
Not likely. But then you didn’t provide your 4 digit Oracle version so we have no idea what version you are even using.

The data types match as the target is a copy of the source with one exception, a new account number, which is handled by data form another table.

The original account number was an 11 character string the new account number is a 16 character string made up of a customer code and customer sequence from the customer description table matched on the old account number.

The source data is not accessed after the weekend load so the initial validation is valid.

Edited by: user7385944 on May 31, 2013 9:08 AM

So did you do this?
>
And did you find the row doing it?

Then try a simple ‘INSERT INTO myTarget SELECT * FROM mySource’ that only selects that one row.
>
You said you changed the code to find the problem row so tell us what happened?
>
The original account number was an 11 character string the new account number is a 16 character string made up of a customer code and customer sequence from the customer description table matched on the old account number.
>
Then you now have the possibility that the new string is longer than 16 characters or that ‘customer sequence’ value isn’t really a number. You also have a new table ‘customer description table that might have dirty data in it.

Post the table DDL for the tables involved, the data for the problem row and the data for that ‘new account number’.

Small actually in today’s databases.

It is still conceptually wrong to commit inside a cursor loop. Does not make sense.

Write a single INSERT..SQL to do the job.

Then determine how to create smaller workloads for the SQL in order to create smaller more manageable processes. Change the SQL to accommodate processing only a specific workload. E.g.

From this: To something like this: Where the column can be a rowid predicate receiving a physical range of data blocks to process, or a date predicate that processes all invoice orders received for a time period.

Wrap this SQL into a PL/SQL block (procedure) if, for example, additional SQL steps are needed for dealing with that workload (e.g. updating another table’s row for the same workload).

Then use DBMS_PARALLEL_EXECUTE to create the workloads and execute (in serial, parallel, or staggered) these workloads.

Источник

Jared Still’s Ramblings

Here you will find posts that are mostly about my work as an Oracle DBA. There may occasionally be other topics posted, but by and large this blog will be about Oracle and other geeky IT topics. Perl will likely be mentioned from time to time.

Thursday, March 13, 2008

ORA-6502 «Bulk Bind: Truncated Bind» error

ORA-6502 is an error that is apparently not well documented when it occurs in conjunction with the use of PL/SQL tables, and possibly bulk binds.

I ran into this problem recently when some custom code that had worked well for several years suddenly started failing.

As it turns out, and you will see just a little later here, the error is rather easy to fix. What makes it diffucult is if you’ve never encountered an ORA-6502 under these circumstances. There is precious little about it via MetaLink or Google. Writing about it here may be of help to the next innocent victim of ORA-6502 with bulk binds.

My suspicion was that new data loaded into the system from another database had something to do with the problem, the problem was determining where it was occurring and why.

The problem went unnoticed for some time due to a quirk in error handling built into the package. (Tom Kyte would likely agree)
Why Do People Do This?»
Built into the package is detailed profiling information. This is a good thing. Also built into the package is an error handling routine. This might be a good thing to have if the package is only run from the command line as it provides a call stack and the error message.

There is an attribute of this type of error handling that does not lend itself well to use in a job that is run via DBMS_JOB. The error message is printed, but the error is not raised.

The consequence of this type of error handling is that regardless of any errors encountered during the execution of the job, they are hidden from DBMS_JOB. No error is ever raised. After a user reports that production data is not propagating to the system, the DBA (me) checks the DBA_JOBS view and find that it is executing successfully.

Wondering what is going on, the erstwhile DBA (me again) runs the job manually and discovers that it does not actually work at all, but no error was raised.

The printed error message was «PL/SQL: numeric or value error» — bulk bind truncated.

The PL/SQL package that generated this is 3000 lines long and has some level of abstration. Translation: hard to debug when you don’t know what the code is doing.

Before showing a more relevant example, I will show a simple one. If you found this page via google, this is enough to help you understand what is going on.

The problem is simply a mismatch of the data type scale. Obvious once you see it. This is a 9i example (10g would take less code) because the database it was written on was 9.2 at the time.

If you are accustomed to working with the data dictionary the problem will likely be obvious to you.

The fix is simple: change length of object_name_type from 20 to 30. The data dictionary (DBA_OBJECTS.OBJECT_NAME) actually specifies this as 128, but this particular database has no objects with names longer than the standard length of 30 characters.

Where the mystery of what caused this error becomes difficult to unravel is when there are a few levels of indirection.

There is a staging table that is the temporary home for data from another system. The PL/SQL code gets its specifications for data types from the staging table, not the source table. You probably already know what the problem is.

This example is similar to what was happening.

First create the staging table.

Now run a similar, but somewhat different PL/SQL block.

Here’s the offending bit of code:

Hmmmm. The specification for the PL/SQL tables came straight from the
table via %TYPE, what could be the problem? The problem is that you
need to look at the cursor, and compare the datatypes in the table
referenced by the cursor with those of the TBIND_TEST table.