DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ora-01722 when bulk collection through procedure

  1. #1
    Join Date
    Sep 2005
    Posts
    21

    Exclamation ora-01722 when bulk collection through procedure

    I m executing my procedure on version of 9.2.0.4 on IBM-AIX server sometimes it is giving me ora-01722 invalid number while bulk collect. I have gone through all work arounds it shows me cause of this error because of conversion of character to number but I am not doing any type conversion.

    I have attached my procedure with this thread.

    Please help me regard this issue. Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    how about tell us which line is giving the error...?

  3. #3
    Join Date
    Sep 2005
    Posts
    21
    It is giving me error after this statement

    EXECUTE IMMEDIATE strCurQuery BULK COLLECT INTO V_FILEID, V_FILENAME, V_EXTERNAL_ID, V_TRANS_DT,
    V_TRANS_DT_TIME, V_SECOND_UNITS, V_FEDERAL_TAX, V_STATE_TAX, V_COUNTRY_TAX, V_CITY_TAX,
    V_OTHER_TAX, V_DUAL_IMSI_FLAG, V_CALL_TYPE, V_MSC_ID, V_RATED_UNITS, V_LATE_CDR, V_AMOUNT,
    V_ORIG_TYPE_ID_USG, V_POINT_ORIGIN, V_RECORD_TYPE, V_HOME_BID, V_SERVE_BID, V_ERROR_STATUS,
    V_RECON_STATUS, V_PROCESS_DATE, V_IS_RCF, V_CALL_END_TIME, V_LOCAL_CALLTIME;

    Actually error is not occuring every time of execution it is throwing error some times and some times my procedure is executed successfully

  4. #4
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    You have rubbish in your temptable, i.e varchar2 fields used to store numbers?

  5. #5
    Join Date
    Sep 2005
    Posts
    21
    Please Mr. Pando let me know actual where i have done mistake in my code?

    Thanks in advance..

    Dhaval

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    I am saying your data contains characters, are you sure your data comes clean?

  7. #7
    Join Date
    Sep 2005
    Posts
    21
    Quote Originally Posted by pando
    I am saying your data contains characters, are you sure your data comes clean?
    Mr. Pando in my temp table i am storing data in varchar2 format whether it comes number or string but error comes when it goes for bulk collection, whatever datatype of my procedure's variables has been selected in that query is same as table's datatype.

    i.e. DUAL_IMSI_FLAG varchar2(20) contains numeric value.In my procedure i have declare it like below.

    TYPE s19 IS TABLE OF customer_rec.DUAL_IMSI_FLAG%TYPE INDEX BY BINARY_INTEGER;

    V_DUAL_IMSI_FLAG S19;

    if it is issue of numeric value to be inserted/selected in varchar2 value than it comes when it is inserting record. but it is showing me when i m selecting it.i m not changing its datatype in my procedure.

    dhaval

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    what I am saying is

    are you sure all the data you have is correct and there are no mixture of varchar and number?

    Look

    Code:
    create table x (a varchar2(4))
    
    Table created.
    
    insert into x values(1);
    
    1 row created.
    
    insert into x values(2);
    
    1 row created.
    
    create table y (a number);
    
    Table created.
    
    insert into y values(1);
    
    1 row created.
    
    insert into y values(2);
    
    1 row created.
    
    commit;
    
    Commit complete.
    
    select *      
    from x, y
    where x.a = y.a;
    
    
    A             A
    ---- ----------
    1             1
    2             2
    
    insert into x values('a');
    
    1 row created.
    
    
    select *      
    from x, y
    where x.a = y.a;
    
    where x.a = y.a
          *
    ERROR at line 3:
    ORA-01722: invalid number
    
    select sum(a) from x;
    select sum(a) from x
               *
    ERROR at line 1:
    ORA-01722: invalid number

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width