Click here to Skip to main content
15,904,346 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
SQL
i created a stored procedure to receive some input parameter and return an output value.

but i'm getting "ORA-01858: a non-numeric character was found where a numeric was expected"

and i can't get where that error is coming from.


my Stored procedure below:
create or replace PROCEDURE STAN_INST 
(
  INP_BRA_CODE IN NUMBER 
, INP_CUS_NUM IN NUMBER 
, INP_CUR_CODE IN NUMBER 
, INP_LED_CODE IN NUMBER 
, INP_SUB_ACCT_CODE IN NUMBER 
, INP_PAY_TYPE IN NUMBER 
, INP_PAY_FREQ IN NUMBER 
, INP_FST_PAY_DATE IN DATE  --ddmmyyyy 
, INP_FST_PAY_AMT IN NUMBER 
, INP_LAS_PAY_DATE IN DATE 
, INP_REMARKS IN VARCHAR2
, INP_TELL_ID IN NUMBER
, INP_CRE_ACCT IN VARCHAR2
, RETURN_STATUS IN OUT NUMBER
) 
IS 
     d_time varchar2(8);
     N_SEQ  NUMBER;
     N_date DATE;

BEGIN

        RETURN_STATUS  := 0;

  /*
  select lpad(&bra_code, 4, '0')||lpad(&cus_num, 7, '0')||lpad(&cur_code, 3, '0')||lpad(&led_code, 4, '0')||lpad(&sub_acct_code, 3, '0')
from dual
  */


SELECT NVL(MAX(INST_SEQ), 0) + 1
  INTO N_SEQ
  FROM stan_ins
WHERE BRA_CODE = INP_BRA_CODE
   AND CUS_NUM = INP_CUS_NUM
   AND CUR_CODE = INP_CUR_CODE
   AND LED_CODE = INP_LED_CODE
   AND SUB_ACCT_CODE = INP_SUB_ACCT_CODE;

select to_char(sysdate, 'hh24miss') into d_time from dual;

select trunc(sysdate) into N_date from dual;



INSERT INTO STAN_INS

  (BRA_CODE,
   CUS_NUM,
   CUR_CODE,
   LED_CODE,
   SUB_ACCT_CODE,
   INST_SEQ,
   INST_DATE,
   TELL_ID,
   INST_TYPE,
   PAY_TYPE,
   PAY_MODE,
   PAY_FREQ,
   FST_PAY_DATE,
   FST_PAY_AMT,
   LAS_PAY_DATE,
   TOT_AMT,
   NUM_OF_REM_PAY,
   PAY_AMT,
   COM_AMT,
   UP_TO_DATE_AMT,
   UPP_LOW_LIM,
   LAS_TRA_DATE,
   CRE_ACCT,
   DEB_ACCT,
   BANK_CODE,
   PAY_CUR_CODE,
   REMARKS,
   OVDN_FLAG,
   DR_CR_REMARKS,
   UPD_TIME)

VALUES
  (INP_BRA_CODE,
   INP_CUS_NUM,
   INP_CUR_CODE,
   INP_LED_CODE,
   INP_SUB_ACCT_CODE,
   N_SEQ,
   N_date,
   INP_TELL_ID,
   1,
   INP_PAY_TYPE,
   2,
   INP_PAY_FREQ,
   to_date(INP_FST_PAY_DATE, 'ddmmyyyy'),
   INP_FST_PAY_AMT,
   INP_LAS_PAY_DATE,
   0,
   1,
   0,
   0,
   0,
   0,
   N_date,
   INP_CRE_ACCT,
   0,
   100,
   1,
   INP_REMARKS,
   1,
   INP_REMARKS,
   d_time
   );

COMMIT;

END STAN_INST;


What I have tried:

my input parameters below

<pre>
INP_BRA_CODE	NUMBER	IN	202
INP_CUS_NUM	NUMBER	IN	13234
INP_CUR_CODE	NUMBER	IN	1
INP_LED_CODE	NUMBER	IN	3
INP_SUB_ACCT	NUMBER	IN	0
INP_PAY_TYPE	NUMBER	IN	2
INP_PAY_FREQ	NUMBER	IN	5
INP_PAY_DATE	DATE	IN	2020-02-11
INP_PAY_AMT	NUMBER	IN	1200
INP_PAY_DATE	DATE	IN	2020-02-11
INP_REMARKS	VARCHAR2(200)	IN	STAN INT
INP_TELL_ID	NUMBER	IN	9
INP_CRE_ACCT	VARCHAR2(200)	IN	020200132340010003018
Posted
Updated 20-Feb-20 1:17am
Comments
ZurdoDev 20-Feb-20 14:00pm    
There is no way we can tell you which column is the problem because it is your data causing the issue and we don't have your data.

You didn't include the stack trace for the error but one possibility is that the data types in your tables do not match the types of the parameters or variables. In other words check the definition of stan_ins

Another observation, why do you fetch the time and the date portions of SYSDATE beforehand, why not simply use them in the INSERT statement, better yet why separate the time and date after all. You can store SYSDATE in a single DATE column and separate time if needed.

Third obervation, the length of d_time is 8 while time time format you use is 6 characters. This could also lead to problems. Then again why separate the time at all.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900