How to make procedure with timestamp typedata

Question

I have tried to make a procedure on PLSQL. The proble is "ORA-01843: not a valid month" when i to execute code as bellow.. My problem on TIMESTAMP

CREATE OR REPLACE PROCEDURE SP_InsertOvertimes
(
ov_id IN VARCHAR,
dro_id IN DATE,
sto_id IN TIMESTAMP,
eto_id IN TIMESTAMP,
acto_id IN Varchar,
otfk_id IN VARCHAR,
slfk_id IN VARCHAR,
SttsO_id IN VARCHAR,
empfk_id IN NUMBER
)
IS
BEGIN
  INSERT into TB_T_Overtimes VALUES (ov_id,dro_id,TO_CHAR(TO_TIMESTAMP(sto_id,'HH24:MI')),eto_id, acto_id,otfk_id,slfk_id,sttso_id,empfk_id);
END;

==== EXECUTE
BEGIN
  SP_InsertOvertimes(9, '07/08/2019','01:12', CURRENT_TIMESTAMP(), 'Kerja Lembur Bagai Kuda', '1', '1', 'Kampret' ,1);
END;

Answer 1

Input value '07/08/2019' is not a DATE, it is a string value. Oracle tries implicitly to convert this into a DATE value by using your current session NLS_DATE_FORMAT format. Use TO_DATE or TO_TIMESTAMP function or Datetime Literals for example DATE '2019-08-07'

According to your input parameters dro_id IN DATE, sto_id IN TIMESTAMP, and values '07/08/2019','01:12' you probably misunderstand DATE and TIMESTAMP data type.

Every DATE value has a date and a time component. Even if you provide only the date part, then the time part will be 00:00:00. There is no reason to have separate columns for data and time value.

DATE and TIMESTAMP data type are more or less the same. Both data types have date and time component. The major difference is TIMESTAMP provides also fractional seconds whereas DATE has only full seconds.

Answer 2

You should match datatypes. Here's an example which shows how you might have done that.

Sample table:

SQL> CREATE TABLE tb_t_overtimes(
  2    ov_id      VARCHAR2(20),
  3    dro_id     DATE,
  4    sto_id     TIMESTAMP,
  5    eto_id     TIMESTAMP,
  6    acto_id    VARCHAR2(20),
  7    otfk_id    VARCHAR2(20),
  8    slfk_id    VARCHAR2(20),
  9    sttso_id   VARCHAR2(20),
 10    empfk_id   NUMBER
 11  );

Table created.

Procedure:

SQL> CREATE OR REPLACE PROCEDURE sp_insertovertimes(
  2    ov_id      IN         VARCHAR,
  3    dro_id     IN         DATE,
  4    sto_id     IN         TIMESTAMP,
  5    eto_id     IN         TIMESTAMP,
  6    acto_id    IN         VARCHAR,
  7    otfk_id    IN         VARCHAR,
  8    slfk_id    IN         VARCHAR,
  9    sttso_id   IN         VARCHAR,
 10    empfk_id   IN         NUMBER
 11  )IS
 12  BEGIN
 13    INSERT INTO tb_t_overtimes VALUES(
 14      ov_id,
 15      dro_id,
 16      sto_id,
 17      eto_id,
 18      acto_id,
 19      otfk_id,
 20      slfk_id,
 21      sttso_id,
 22      empfk_id
 23    );
 24
 25  END;
 26  /

Procedure created.

Testing:

SQL> BEGIN
  2    sp_insertovertimes
  3      ('9',
  4       DATE '2019-08-07',
  5       to_timestamp('01:12', 'hh24:mi'),
  6       current_timestamp,
  7       'Kerja Lembur',
  8       '1',
  9       '1',
 10       'Kampret',
 11       1);
 12  END;
 13  /

PL/SQL procedure successfully completed.

Result:

SQL> select * From tb_t_overtimes;

OV_ID                DRO_ID
-------------------- -------------------
STO_ID
---------------------------------------------------------------------------
ETO_ID
---------------------------------------------------------------------------
ACTO_ID              OTFK_ID              SLFK_ID
-------------------- -------------------- --------------------
STTSO_ID               EMPFK_ID
-------------------- ----------
9                    07.08.2019 00:00:00
01.07.19 01:12:00,000000
19.07.19 14:59:08,320000
Kerja Lembur         1                    1
Kampret                       1


SQL>

Answer 3

Execute your procedure with the second argument with date'2019-07-08' instead of '07/08/2019' to remove the error, assuming your month is July.

Moreover, the first parameter should be quoted ('9'), because it's defined as varchar (not as numeric) in the table.