Ora 01722 invalid number updating date

29-Dec-2017 05:45

Using bulk processing when it’s appropriate can save time and effort; writing a proper exception handler can help tremendously in troubleshooting any errors that may arise. Just recently a service request was opened with Oracle Support regarding the “when others then null” exception handler when it was found in an Oracle-supplied form for the Oracle Inventory application from the E-Business suite.

It appears the person who opened the SR believes (and rightly so) this is not the most informative of exception handlers nor is it proper coding practice; the request was opened in hopes of disallowing such constructs.

Dear Experts, Please excuse me if this is not a bug but I am getting the following error "ORA-01722: invalid number" at many places with oracle driver. PDOException: SELECT AS fid, AS uid, base.filename AS filename, AS uri, base.filemime AS filemime, base.filesize AS filesize, base.status AS status, base.timestamp AS timestamp FROM base WHERE (IN (:db_condition_placeholder_0)) (prepared: SELECT AS fid, base."UID" AS "UID", base.filename AS filename, AS uri, base.filemime AS filemime, base.filesize AS filesize, base.status AS status, base.timestamp AS timestamp FROM "FILE_MANAGED" base WHERE (IN (:db_condition_placeholder_0)) ) e: SQLSTATE[HY000]: General error: 1722 OCIStmt Execute: ORA-01722: invalid number (ext\pdo_oci\oci_statement.c:146) args: Array ( [:db_condition_placeholder_0] =Rohlt, I ran into this issue as well. I had this same error and was curious as to why this was marked "closed(won't fix)"?

Why is coding “when others then null” not the best idea?

Let’s look at some examples and see what could be hidden behind that glorious construct.

Creating a table used in a previous post: An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely.

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list.

A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown.

Why is coding “when others then null” not the best idea?

Let’s look at some examples and see what could be hidden behind that glorious construct.

Creating a table used in a previous post: An insert was intentionally attempted that violates the primary key constraint yet no error was thrown — how lovely.

Using BULK COLLECT in PL/SQL blocks and procedures can dramatically speed array processing, but it can, if the DBA isn’t prepared, ‘hide’ any errors that occur in the bulk processing list.

A ‘plain vanilla’ EXCEPTION handler may not report all errors that are thrown.

we cannot replace '' with NULL because you may be searching for empty string values in this condition...