CODEWITHSUNDEEP
sqloracle-databasetime
Jack
Jack

Reputation: 805

Want to insert timestamp through procedure in oracle

I have this procedure

PROCEDURE insertSample
  (
    return_code_out OUT VARCHAR2,
    return_msg_out OUT VARCHAR2,
    sample_id_in IN table1.sample_id%TYPE,
    name_in IN table1.name%TYPE,
    address_in IN table1.address%TYPE
  )
  IS

  BEGIN
    return_code_out := '0000';
    return_msg_out := 'OK';

    INSERT INTO table1
    sample_id, name, address)
    VALUES
    (sample_id_in, name_in, address_in);


  EXCEPTION
    WHEN OTHERS
    THEN
      return_code_out := SQLCODE;
      return_msg_out := SQLERRM;


  END insertSample;

I want to add 4th column in table1 like day_time and add current day timestamp in it.. ho can i do that in this procedure.. thank you

Upvotes: 1

Views: 2950

Answers (3)

Kevin Burton
Kevin Burton

Reputation: 11936

SYSDATE will give you the current data and time.

and if you add the column with a default value you can leave your procedure as it is

ALTER TABLE table1 ADD when_created DATE DEFAULT SYSDATE;

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

Assuming you you have (or add) a column to the table outside of the procedure, i.e.

ALTER TABLE table1
  ADD( insert_timestamp TIMESTAMP );

you could modify your INSERT statement to be 

   INSERT INTO table1
    sample_id, name, address, insert_timestamp)
    VALUES
    (sample_id_in, name_in, address_in, systimestamp);

In general, however, I would strongly suggest that you not return error codes and error messages from procedures. If you cannot handle the error in your procedure, you should let the exception propagate up to the caller. That is a much more sustainable method of writing code than trying to ensure that every caller to every procedure always correctly checks the return code.

Upvotes: 2

Eddie
Eddie

Reputation: 10138

Using Sysdate can provide all sorts of manipulation including the current date, or future and past dates.

http://edwardawebb.com/database-tips/sysdate-determine-start-previous-month-year-oracle-sql

Upvotes: 0

Related Questions