Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!postnews.google.com!m7g2000cwm.googlegroups.com!not-for-mail
From: "Vladimir M. Zakharychev" <vladimir.zakharychev@gmail.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Question about triggers
Date: 9 Oct 2006 23:57:57 -0700
Organization: http://groups.google.com
Lines: 56
Message-ID: <1160463476.451782.128920@m7g2000cwm.googlegroups.com>
References: <1160461020.315069.156630@c28g2000cwb.googlegroups.com>
NNTP-Posting-Host: 217.15.147.190
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
X-Trace: posting.google.com 1160463482 12040 127.0.0.1 (10 Oct 2006 06:58:02 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 10 Oct 2006 06:58:02 +0000 (UTC)
In-Reply-To: <1160461020.315069.156630@c28g2000cwb.googlegroups.com>
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1; .NET CLR 1.1.4322; .NET CLR 2.0.50727),gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: m7g2000cwm.googlegroups.com; posting-host=217.15.147.190;
   posting-account=7srsFw0AAADBBht5vVKjvzMbApNGydkb
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:276888


MAYEKEUL wrote:
> Hello everybody,
>
> Petite question existentielle sur les triggers avec oracle.
>
> I want to format my value before insert
> In this case, the value is a timestamp
>
> the table name is "MT_LONGTERM" and the fields are
>
> LT_TIMESTAMP  - TIMESTAMP
> LT_VALUE - NUMBER(8,4)
>
> my f... trigger code ;o)
>
> create or replace trigger "MT_LONGTERM_T1"
> BEFORE
> insert on "MT_LONGTERM"
> for each row
> begin
> :NEW.LT_TIMESTAMP:=TO_TIMESTAMP(:NEW.LT_TIMESTAMP,'DD-MON-YYYY
> HH24:MI:SS');
> end;
>
> the reason is when  I try to load a CSV file into this table, all date
> are refused
> Even if a try manually, line by line in sql
> .. Exept if I format the value
>
> So of course, my trigger do not work (:(((
>
> How can i catch my value before insert and apply the format?
>
>
>
> Anyone can help me?
>
> Thanks
>
> Mayekeul
>
> Ps: sorry for my bad english, I am too bad!! :(

A trigger isn't going to help you here. How exactly are you loading the
CSV file? Using SQL*Loader? If so, you can specify the input timestamp
format in sqlldr control file. Check the docs for exact syntax and
examples. Otherwise you need to use explicit TO_TIMESTAMP call with
correct format. Or you can set the default format using ALTER SESSION
SET NLS_TIMESTAMP_FORMAT='DD-MON-YYYY HH24:MI:SS' command.

Hth,
    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)
    http://www.dynamicpsp.com