Oracle Timestamp in Perl – How to Generate Current Oracle Timestamp in Perl

oracleperl

I use DBI. And I want to make some like

INSERT INTO ... VALUES (the_generated_timestamp, ...);

What is the command? How could it be created, the perl ocaltime scalar value has a very complex, for human eyes optimized format (f.e. Mon Apr 28 15:58:51 2014). My goal were some like as the unix gettimeofday() does (giving back the seconds since 1970.1.1), converted by some format string, which I can let eat by the Oracle.

But any simpler solution were also okay, if it exists.

It need to be generated in perl. INSERT ... (current_timestamp, ...) isn't okay.

Best Answer

Something like this will do:

my ($second, $minute, $hour, $day, $month, $year, $weekday, $dayofyear, $dst) = localtime();

my $oracledatestring = sprintf "TO_DATE('%04d/%02d/%02d %02d:%02d:%02d', 'yyyymmdd HH24:MI:SS')", $year+1900, $month+1, $day, $hour, $minute, $second;

.. giving something like:

TO_DATE('2014/04/28 16:34:34', 'yyyymmdd HH24:MI:SS')

... which can then be inserted directly into an Oracle DATE column.

All a bit of a long way round, to be honest. Use SYSDATE in your INSERT statements instead.

Related Solutions

Current isolation level in oracle

Using the query from the SO answer Vincent Malgrat referenced, here is how you can get the transaction isolation level for the transaction in progress:

SELECT s.sid, s.serial#,
   CASE BITAND(t.flag, POWER(2, 28))
      WHEN 0 THEN 'READ COMMITTED'
      ELSE 'SERIALIZABLE'
   END AS isolation_level
FROM v$transaction t 
JOIN v$session s ON t.addr = s.taddr AND s.sid = sys_context('USERENV', 'SID');

If you are not already in a transaction you can start one with the following:

declare 
   trans_id Varchar2(100);
begin
   trans_id := dbms_transaction.local_transaction_id( TRUE );
end;
/

It seems like there would be an easier way than this. I don't know how to get the default isolation level for the session if that is what you are looking for.

PostgreSQL pl/perl trigger, differentiate null vs empty

You did not specify PostgreSQL version. On 9.0.5 I have observed the same behavior (not sure if it's a bug, see comments below).

This is easy to workaround - you can first test for definedness to catch NULL, and if it passed, test for empty string.

if ( not defined $_TD->{ new }{ $column } ) {
    elog( NOTICE, "New value in column $column is not defined" );
}
elsif ( $_TD->{ new }{ $column } eq '' ) {
    elog( NOTICE, "New value in column $column is empty" );
}
if ( not defined $_TD->{ old }{ $column } ) {
    elog( NOTICE, "Old value in column $column is not defined" );
}
elsif ( $_TD->{ old }{ $column } eq '' ) {
    elog( NOTICE, "Old value in column $column is empty" );
}

Best Answer

Related Solutions

Current isolation level in oracle

PostgreSQL pl/perl trigger, differentiate null vs empty

Related Question