Home » SQL & PL/SQL » SQL & PL/SQL » How do I ignore row giving error? (Oracle 12c)
How do I ignore row giving error? [message #677666] Fri, 04 October 2019 02:01 Go to next message
Buchas
Messages: 98
Registered: March 2006
Member
Hello,

There is a select that gives error: "ORA-01841: (full) year must be between -4713 and +9999, and not be 0".
The select is this:
  select
    t_pas_msg_rec(
      msg2.msgid,
      msg2.receiver,
      extract(second from msg2.created_ts - edc.created_ts))
  bulk collect into
    g_send_msg_data
  from
    pas_sepa_trans_references str,
    pas_sepa_group_header sgh1,
    pas_sepa_transactions stn1,
    pas_sepa_group_header sgh2,
    pas_sepa_transactions stn2,
    pas_messages msg1,
    inq_edoc edc,
    (select
      mes.msgid,
      mes.receiver,
      edc.created_ts
    from
      pas_messages mes,
      inq_edoc edc
    where
      mes.msgtype = 'IFCCTRNS' and
      mes.actl_msg_status = 'SWIFTsaug' and
      mes.system = 'LITAS-INST' and
      mes.sender = 'CTX_LB_BIC' and
      mes.edoc_id = edc.edoc_id 
      and mes.msgs_date between sysdate - l_observ_interval and sysdate and
      mes.msgs_date + 1/24  >= l_financial_time
      ) msg2
  where
    str.ref_type = 'Persiuntimas' and
    str.from_id = stn1.id and
    sgh1.id = stn1.sgh_id and
    str.to_id = stn2.id and
    sgh2.id = stn2.sgh_id and
    sgh2.grp_hdr_msg_id = msg1.msgid and
    msg1.edoc_id = edc.edoc_id and
    sgh1.grp_hdr_msg_id = msg2.msgid;

Unfortunately I do not have access to the database the error is encountered (it is production environment) and I cannot reproduce it in development environment. If I cannot reproduce, I cannot fix it. Now the management says "just ignore the error and let the query retrieve the other rows". But how do I do this? E.g. if I have a query
SELECT ROWNUM / (rownum-1) FROM DUAL CONNECT BY LEVEL <= 5 
that gives "ORA-01476: divisor is equal to zero" error, can I ignore it and display the other 4 rows?
Re: How do I ignore row giving error? [message #677667 is a reply to message #677666] Fri, 04 October 2019 02:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66714
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The error is in the data or in default date/timestamp format; what is the data type of mes.msgs_date, msg2.created_ts, edc.created_ts, l_financial_time?

Quote:
Now the management says "just ignore the error and let the query retrieve the other rows". But how do I do this?

You can't.
Re: How do I ignore row giving error? [message #677669 is a reply to message #677667] Fri, 04 October 2019 02:32 Go to previous messageGo to next message
Buchas
Messages: 98
Registered: March 2006
Member
mes.msgs_date is DATE
msg2.created_ts is TIMESTAMP(6)
edc.created_ts is TIMESTAMP(6)
l_financial_time is DATE
Re: How do I ignore row giving error? [message #677671 is a reply to message #677669] Fri, 04 October 2019 04:00 Go to previous message
cookiemonster
Messages: 13747
Registered: September 2008
Location: Rainy Manchester
Senior Member
Re-run the query with all references to one of those columns removed.
Do it again with another column removed.
Keep going until the error goes away.

That should tell you which one is the problem.
Then you are going to have to find the invalid data in that column and fix it.
Previous Topic: question on using materialized view that will be based on table having billions of rows
Next Topic: ORA-29279: SMTP permanent error: 550 domain.
Goto Forum:
  


Current Time: Sat Dec 07 04:20:20 CST 2019