Oracle error: ORA-01847: day of month must be between 1 and last day of month

Sadman ZIhan 0
Viewed 53

I tried the following anonymous block and get ORA-01847 error at line 14.

  1. Is there anything wrong?
  2. What is the definition of line 14th 'SSSS.FF' into TO_CHAR FUNCTION?

Thanks in advance.

DECLARE
PROCEDURE_NAME VARCHAR2(100) := 'CONV_CIB';
TIME_START TIMESTAMP;
TIME_END TIMESTAMP;
EXECUTION_TIME TIMESTAMP;
BEGIN
dbms_output.enable;
TIME_START := SYSTIMESTAMP;

select sysdate into procedure_name from dual;

TIME_END := SYSTIMESTAMP;

EXECUTION_TIME := TO_CHAR (TIME_END - TIME_START, 'SSSS.FF');

--dbms_output.put_line ('Start: ' || TIME_START);
--dbms_output.put_line ('  End: ' || TIME_END);
dbms_output.put_line (PROCEDURE_NAME ||'    PROCEDURE EXECUTION TIME: ' || TO_CHAR (TIME_END - TIME_START, 'SSSS.FF'));

DBMS_OUTPUT.PUT_LINE (EXECUTION_TIME);
EXECUTION_TIME := (EXECUTION_TIME || ' HRS');
DBMS_OUTPUT.PUT_LINE (EXECUTION_TIME);

END;
oracle
2 Answers

Yes. There is an error. You have declared variable EXECUTION_TIME as TIMESTAMP but you are assigning character value in it. As you changed the datatype of this variable to VARCHAR2, This will work like charm -

DECLARE
PROCEDURE_NAME VARCHAR2(100) := 'CONV_CIB';
TIME_START TIMESTAMP;
TIME_END TIMESTAMP;
EXECUTION_TIME VARCHAR2(50);
BEGIN
dbms_output.enable;
TIME_START := SYSTIMESTAMP;

select sysdate into procedure_name from dual;

TIME_END := SYSTIMESTAMP;

EXECUTION_TIME := TO_CHAR (TIME_END - TIME_START, 'SSSS.FF');

--dbms_output.put_line ('Start: ' || TIME_START);
--dbms_output.put_line ('  End: ' || TIME_END);
dbms_output.put_line (PROCEDURE_NAME ||'    PROCEDURE EXECUTION TIME: ' || TO_CHAR (TIME_END - TIME_START, 'SSSS.FF'));

DBMS_OUTPUT.PUT_LINE (EXECUTION_TIME);
EXECUTION_TIME := (EXECUTION_TIME || ' HRS');
DBMS_OUTPUT.PUT_LINE (EXECUTION_TIME);

END;

Demo.

Ankit Bajpai0

Is there anything wrong?

Yes, as you state in the question, you get an ORA-01847 error at line 14.

Apart from that:

  • You overwrite the procedure name with the current time.
  • If you subtract two TIMESTAMPs you get an INTERVAL DAY TO SECOND data type as the result and not another TIMESTAMP.
  • You cannot use EXECUTION_TIME := (EXECUTION_TIME || ' HRS'); as the right-hand side results in a string and not a TIMESTAMP.

This will run without syntax errors:

DECLARE
  PROCEDURE_NAME VARCHAR2(100) := 'CONV_CIB';
  value          DATE;
  TIME_START     TIMESTAMP;
  TIME_END       TIMESTAMP;
  EXECUTION_TIME INTERVAL DAY TO SECOND(9);
BEGIN
  dbms_output.enable;
  TIME_START := SYSTIMESTAMP;
  SELECT sysdate INTO value FROM DUAL;
  TIME_END := SYSTIMESTAMP;
  EXECUTION_TIME := TIME_END - TIME_START;
  dbms_output.put_line ('Start: ' || TIME_START);
  dbms_output.put_line ('  End: ' || TIME_END);
  dbms_output.put_line (
    PROCEDURE_NAME
    ||'    PROCEDURE EXECUTION TIME: ' || execution_time
  );
END;
/

and outputs:

Start: 06-SEP-22 12.14.01.551337
  End: 06-SEP-22 12.14.01.553333
CONV_CIB    PROCEDURE EXECUTION TIME: +00 00:00:00.001996000

DB<>Fiddle here

MT00
Related