Oracle date data type problem - I get wrong format as result in table-CodePudding

I need to set a column with date data type TO_TIMESTAMP to correct format to insert some data with merge into declaration.

The table.column = settings.data The insert value as example: '28.01.2022 14:07' But column is set as TIMESTAMP(6) format. So need this to insert as: '28.01.2022 14:07:00,000000'

DECLARE
PROCEDURE settings_import
(
in_id IN NUMBER
in_data IN TIMESTAMP
)
IS
BEGIN
MERGE INTO settings a
using (select in_id AS ID, in_data AS data from dual) b
on (a.ID = b.ID)
when not matched
then
INSERT into settings (ID, data) values (ID, data)
when matched
update set a.data = b.data
END settings_import;
BEGIN
settings_import (12, '28.01.2022 14:07');
END;
/

When I try this I get in column data this entry, but it's wrong: 28.01.2020 22:14:07,000000 right should be: '28.01.2022 14:07:00,000000'

When I try in the values (b.ID, TO_TIMESTAMP(b.data, 'DD.MM.YYYY HH24:MI:SS.FF') I get this error: ORA-01830: date format picture ends before converting entire input string

How can I fix this? thank you!

CodePudding user response：

Your code has many, many syntax errors (missing commas, missing semi-colons, missing keywords, extra keywords)... If you fix those then:

'28.01.2022 14:07' is not a TIMESTAMP data type it is a string literal.

Either use a TIMESTAMP literal:

DECLARE
  PROCEDURE settings_import(
    in_id   IN SETTINGS.ID%TYPE,
    in_data IN SETTINGS.DATA%TYPE
  )
  IS
  BEGIN
    MERGE INTO settings a
    USING DUAL
    ON (a.ID = in_id)
    WHEN NOT MATCHED THEN
      INSERT (ID, data) values (in_ID, in_data)
    WHEN MATCHED THEN
      UPDATE SET a.data = in_data;
  END settings_import;
BEGIN
  settings_import (
    12,
    TIMESTAMP '2022-01-28 14:07:00'
  );
END;
/

Or explicitly use TO_TIMESTAMP:

DECLARE
  PROCEDURE settings_import(
    in_id   IN SETTINGS.ID%TYPE,
    in_data IN SETTINGS.DATA%TYPE
  )
  IS
  BEGIN
    MERGE INTO settings a
    USING DUAL
    ON (a.ID = in_id)
    WHEN NOT MATCHED THEN
      INSERT (ID, data) values (in_ID, in_data)
    WHEN MATCHED THEN
      UPDATE SET a.data = in_data;
  END settings_import;
BEGIN
  settings_import (
    12,
    TO_TIMESTAMP('28.01.2022 14:07', 'DD.MM.YYYY HH24:MI')
  );
END;
/

Which, if you have the table:

CREATE TABLE settings (id NUMBER, data TIMESTAMP);

Both will give a table with the value (with the NLS_TIMESTAMP_FORMAT of YYYY-MM-DD HH24:MI:SS.FF):

ID DATA

12 2022-01-28 14:07:00.000000

ID	DATA
12	2022-01-28 14:07:00.000000

db<>fiddle here

CodePudding user response：

I've had some syntax error as I wanted to simplify my code for better understanding, sorry, see below the corrected code which now works.

Thanks pmdba & MT0!

DECLARE 
PROCEDURE settings_import 
( 
in_id IN NUMBER, 
in_data IN varchar2 
) 
IS 
BEGIN 
MERGE INTO settings a 
using (select in_id AS ID, in_data AS data from dual) b 
on (a.ID = b.ID) 
when not matched 
then 
INSERT (ID, data) values (b.id, to_timestamp(b.data, 'DD/MM/YYYY HH24:MI:SS.FF')) 
when matched 
then 
update set a.data = to_timestamp(b.data, 'DD/MM/YYYY HH24:MI:SS.FF'); 
END settings_import; 
BEGIN 
settings_import (12, '11/11/2022 14:07'); 
 
END;
/