Rounding of Timestamp(9) in Oracle 11g stored procedure

The following procedure rounds the Timestamp(9) to a Timestamp(6):

CREATE
    TABLE MY_TABLE
    (
        MY_TIMESTAMP TIMESTAMP(9) NOT NULL
    )

CREATE OR REPLACE PROCEDURE "DB"."INSERT_ROW"
(myTimestamp IN TIMESTAMP)
AS
v_sys_error NUMBER := 0;
v_err INTEGER;
v_rc INTEGER;
BEGIN

BEGIN
    INSERT
INTO
    DB.INSERT_ROW
    (
        MY_TIMESTAMP
    )
    VALUES
    (
        myTimestamp
    );
EXCEPTION
WHEN OTHERS THEN
v_sys_error := SQLCODE;
v_err := v_sys_error;
v_rc := SQL%ROWCOUNT;
RAISE;
END;

END;

The call below will insert: 2007-12-12 12:23:45.123457000

@call DB.INSERT_ROW(TIMESTAMP '2007-12-12 12:23:45.123456789');

Whereas the code below will insert this: 200开发者_如何学Python7-12-12 12:23:45.123456789

INSERT
INTO
    DB.MY_TABLE
    (
        MY_TIMESTAMP
    )
    VALUES
    (
        TIMESTAMP '2007-12-12 12:23:45.123456789'
    );

How do I prevent this? Using (myTimestamp IN TIMESTAMP(9)) fails to validate.

CREATE
    TABLE MY_TABLE
    (
        MY_TIMESTAMP TIMESTAMP(9) NOT NULL
    )

CREATE OR REPLACE PROCEDURE "DB"."INSERT_ROW"
(myTimestamp IN MY_TABLE.MY_TIMESTAMP%TYPE)
...

This should do the trick. Numerically constrained types are not permitted in a parameter list.

One way to work around precision issues is to convert it (concat with '') or use to_char with a format when calling the PROC.

Take the input as VARCHAR2?

The conversion can take place in the INSERT within the proc to TIMESTAMP(9)

EDITED: Obviously I have assumed that your table field is correctly TIMESTAMP(9), but check that as well.

You can also use the timestamp_unconstrained type:

create or replace procedure insert_row ( myTimestamp timestamp_unconstrained )
is
begin
  insert into my_table ( my_timestamp ) values ( myTimestamp );
end insert_row;
/

Check http://docs.oracle.com/cd/E18283_01/appdev.112/e17126/predefined.htm :

subtype TIMESTAMP_UNCONSTRAINED     is TIMESTAMP(9);