I am creating a stored procedure in Oracle database that's resulting in error "ORA-01858: a non-numeric character was found where a numeric was expected".

My procedure is as below:

create or replace procedure testProc(
id IN VARCHAR2,
user IN VARCHAR2,
sender IN VARCHAR2
)
as 

vCount number;
begin
     select count(*) into vCount from table1 where id='12345'
     
     if vCount=0
         insert into table1 (id, user, sender, status) values (id, user, partner, status);
     else
         update table1 set status='ERR' where id='12345'
     end if;
end procedure;

Error: ORA-01858: a non-numeric character was found where a numeric was expected

I tried replacing vCount as int that did not help. Also tried declaring vCount below sender IN VARCHAR2.

Can someone please tell what is correct way to use the above procedure.

CodePudding user response：

Use a MERGE statement then you can do it in a single statement (rather than SELECT followed by either INSERT or UPDATE):

CREATE PROCEDURE testProc(
  i_id     IN table1.id%TYPE,
  i_user   IN table1."USER"%TYPE,
  i_sender IN table1.sender%TYPE,
  i_status IN table1.status%TYPE
)
AS
BEGIN
  MERGE INTO table1 dst
  USING (
    SELECT '12345' AS id
    FROM   DUAL
  ) src
  ON (src.id = dst.id)
  WHEN MATCHED THEN
    UPDATE SET status = 'Err'
  WHEN NOT MATCHED THEN
    INSERT (id, "USER", sender, status)
    VALUES (i_id, i_user, i_sender, i_status);
END testProc;
/

db<>fiddle here

CodePudding user response：

This code can't possibly return error you specified because

1. procedure is invalid (mising statement terminators; column name can't be USER because it is a keyword, reserved for currently logged user)
2. that error code is related to date issues, while - in your code - there's nothing that looks like a date

Therefore, it is impossible to help you with error you stated. Otherwise, consider NOT naming procedure's parameters the same as column names because that leads to various problems.

Something like this would work, but it is not related to error you got.

Sample table:

SQL> CREATE TABLE table1
  2  (
  3     id        VARCHAR2 (5),
  4     c_user    VARCHAR2 (20),
  5     partner   VARCHAR2 (10),
  6     sender    VARCHAR2 (10),
  7     status    VARCHAR2 (5)
  8  );

Table created.

SQL>

Procedure:

SQL> CREATE OR REPLACE PROCEDURE testProc (p_id      IN VARCHAR2,
  2                                        p_user    IN VARCHAR2,
  3                                        p_sender  IN VARCHAR2)
  4  AS
  5     vCount  NUMBER;
  6  BEGIN
  7     SELECT COUNT (*)
  8       INTO vCount
  9       FROM table1
 10      WHERE id = p_id;
 11
 12     IF vCount = 0
 13     THEN
 14        INSERT INTO table1 (id,
 15                            c_user,
 16                            sender,
 17                            status)
 18             VALUES (p_id,
 19                     p_user,
 20                     NULL,
 21                     'NEW');
 22     ELSE
 23        UPDATE table1
 24           SET status = 'ERR'
 25         WHERE id = p_id;
 26     END IF;
 27  END testproc;
 28  /

Procedure created.

Testing:

SQL> EXEC testproc('12345', 'Little', 'Foot');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM table1;

ID    C_USER               PARTNER    SENDER     STATU
----- -------------------- ---------- ---------- -----
12345 Little                                     NEW

SQL> EXEC testproc('12345', 'Little', 'Foot');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM table1;

ID    C_USER               PARTNER    SENDER     STATU
----- -------------------- ---------- ---------- -----
12345 Little                                     ERR

SQL>

CodePudding user response：

Thanks MT0, Littlefoot.

First apologies for not giving entire table insert columns.

I had a column createTS with timestamp data type but declared it as VARCHAR2 like below.

While inserting the data it did not like varchar2 being inserted for timestamp datatype and hence the error said a non-numeric was found in place of numeric.