I've moved...

This blog now has a new home - please update your shortcuts and readers to: www.jeffkemponoracle.com. Sorry for any inconvenience!

Friday, March 30, 2007

ORA-06502: PL/SQL: numeric or value error: Bulk bind: Error in define

I came across an inexplicable error when bulk collecting into a PL/SQL table with the NOT NULL constraint the other day. What was confusing was that the code had been passing tests for quite some time.

[Full Article]

5 comments:

  1. It must be a bug in BULK COLLECT.
    DECLARE
    TYPE t IS TABLE OF DATE NOT NULL
    INDEX BY PLS_INTEGER;
    r t;
    BEGIN
    r ( 1 ) := NULL;
    END;
    Error at line 1
    ORA-06550: line 6, column 15:
    PLS-00382: expression is of wrong type
    ORA-06550: line 6, column 4:
    PL/SQL: Statement ignored

    ReplyDelete
  2. Interesting - and the bug is that the date version is not failing when it should; it cannot convert NULL to a date, but the bulk collect happily proceeds without error.

    ReplyDelete
  3. This comment has been removed by the author.

    ReplyDelete
  4. hello Jeffrey

    i have problem related to insert but don't know where to send. so writing in comment please do consider this.

    i am inserting into table xx by three different processes. insert query is simple insert () into using values(). all process are inserting for 1 hr and after 1 hr two process are aborting giving error "ORA-02049: timeout: distributed transaction waiting for lock" all keys of this table are indexed. this table does not have any triggers it only has check null constraints and 2 referential constraints and foreign keys are also indexed.
    i am using oracle on sun os.
    please guide me on this.
    your help is highly needed.

    thank you very much.

    ReplyDelete
  5. Hi Sachin, how's it going?

    The error you're getting indicates you are inserting across a database link somewhere. I don't know what is causing it in your situation, but there are some notes on the net that may be helpful.

    The process may be getting the timeout either at the insert or at the commit stage; and may not be related to the actual table you're inserting into; it may be getting a deadlock on the transaction lock.

    See Arap Nanda's comments here.

    Hope this helps a bit. Good to hear from you.

    ReplyDelete

Note: only a member of this blog may post a comment.