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 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