ora-01722 when bulk collection through procedure
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: ora-01722 when bulk collection through procedure

Hybrid View

  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,448
    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,448
    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,448
    I am saying your data contains characters, are you sure your data comes clean?

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    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