Discussion :

[Goon]

Bonjour,

Existe-t-il une fonction permettant de changer le format d'un type timestamp sans changer de type ? (mon but étant de transformer un timestamp de type yyyy-mm-dd hh:mi:ss en type dd/mm/yy hh:mi:ss)

N'ayant pas trouvé cette fonction j'ai essayé un trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
 
create or replace trigger t_test
before insert on test for each row
declare
  vreste varchar(8);
  sdate varchar(20);
begin
 sdate := concat('-',substr(:new.MaDate,3,2));
 sdate := concat(sdate,substr(:new.MaDate,6,5));
 vreste := concat(' ',substr(:new.MaDate,12,19));
 sdate := concat(sdate,vreste);
 :new.MaDate := to_timestamp(sdate,'dd/mm/yy hh:mi:ss');
end;

Pas de problème lors de la creation, mais il plante sur la concatenation d'un espace visiblement(ligne 9). Pareil si j'essaie de feinter avec une fonction replace.
(Je travail sous SQL*Plus Worksheet)

[plaineR]

Un timestamp est un timestamp peu importe le format ! C'est comme une date.
Pourquoi veux-tu le transformer ? Pour de l'affichage ?

[Goon]

Parce que j'ai une appli qui fait des insert dans cette base, mais que les insert sont fait au format US alors que le format de la base est français...
donc impossible d'insérer

ORA-01830: données surnuméraires après la conversion correcte d'une chaîne en entrée

[plaineR]

ORA-01830: date format picture ends before converting entire input string
Cause: A valid date format picture included extra data. The first part of the format picture was converted into a valid date, but the remaining data was not required.
Action: Check the specifications for date format pictures and correct the statement.

Ce n'est pas la même chose : l'erreur t'indique juste que ton format n'est pas assez précis par rapport à tes données.

[Goon]

Justement, je me suis peut etre mal expliqué désolais:
Mon application tente d'insérer un typestamp avec un format de type yyyy-mm-dd hh:mi:ss

insert into test values (...'2005-01-01 12:12:12')

Mais aprés test, le format du timestamp de la base est de type:
dd-mm-yyyy hh:mi:ss

Je me suis donc dis que j'allais passer par le format chaine afin de decouper la date au bon format avant de la reinjecter avec un to_timestamp, mais faire un to_char sur le timestamp (format US) me renvoi une chaine vide :
(alors que si le timestamp est au format fr il n'y a aucun probleme pour le convertir)

Sinon est-il possible de modifier le format des dates oracles pour ma session ?

[plaineR]

Tu n'as qu'à faire un :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
insert into matable (..., coltimestamp,...) 
values (..., to_timestamp ('2005-01-01 12:12:12', 'YYYY-MM-DD HH24:MI:SS'), ...);

[Goon]

J'ai appliqué ton code sur un trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
create or replace trigger t_test
before insert on test for each row
begin
  :new.Madate := to_timestamp(:new.MaDate, 'YYYY-MM-DD HH24:MI:SS');
end;

Pas d'erreur de compilation.

L'insert se deroule bien: 1 ligne créée
insert into test values(...,to_timestamp('2005-01-01 12:12:12', 'YYYY-MM-DD HH24:MI:SS'),...)

Mais quand je consulte la table, le champs MaDate est vide.

[plaineR]

Pourquoi fais-tu un trigger ? puisque dans le insert tu fais déjà un to_timestamp ?

De toute façon dans le trigger c'est trop tard.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
SQL>  create table test_dvp (col1 varchar2(30), col2 timestamp);
Table created.
 
SQL> insert into test_dvp 
  2  values ('ligne 1', to_timestamp ('2005-01-01 12:12:12', 'YYYY-MM-DD HH24:MI:SS'));
1 row created.
 
SQL>  select col1, to_char (col2, 'DD/MM/YYYY HH24:MI:SS') from test_dvp;
COL1                           TO_CHAR(COL2,'DD/MM
------------------------------ -------------------
ligne 1                        01/01/2005 12:12:12

[Goon]

Le insert est généré automatiquement par le code de l'application.
Je fais des insert manuels pour faire des tests.

Je pense donc que le trigger est obligatoir afin d'intercepter les dates entrantes dans la base.

[plaineR]

Ca ne marchera pas au niveau du trigger car tu lui transmets une colonne qu'il ne reconnait pas comme étant un timestamp :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> create or replace trigger bi_test_dvp
  2  before insert on test_dvp for each row
  3  begin
  4  :new.col2 := to_timestamp (:new.col2, 'YYYY-MM-DD HH24:MI:SS');
  5  end;
  6  /
Trigger created.
 
SQL> insert into test_dvp values ('ligne2', '24-10-2005 10:20:52');
insert into test_dvp values ('ligne2', '24-10-2005 10:20:52')
                                       *
ERROR at line 1:
ORA-01401: inserted value too large for column

Tu n'as pas moyen d'intervenir au niveau de l'appli ?

[Goon]

Non l'appli est trop conséquente.

Mais peut-être que je peux modifier le format date directement au niveau d'oracle ?

[Goon]

Il y a t-il une fonction qui permet de transformer un timestamp en chaine de caracteres sans spécifier de format (donc pas la fonction to_char ) ?
Une sorte de fonction toString en quelque sorte.

[plaineR]

Avec to_char tu n'es pas obligé de préciser de format

[Goon]

Je viens de faire l'essai sur une table avec 3 champs, un numerique, un timestamp, et un dernier de type chaine.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
create or replace trigger t_test
before insert on test for each row
declare
  vtest varchar(20);
begin
  vtest := to_char(:new.MaDate);
  :new.MaChaine := vtest;
  :new.Madate := '01-01-2005 12:12:12';
end;

J'affecte à la chaine la date convertit en chaine.
Puis j'affecte à la date une date au bon format pour qu'il ne rejette pas l'insertion.
Mais apres insertion, le champs de type chaine est vide :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

insert into test values(1,'2005-01-01 12:12:12','')

[plaineR]

Je ne comprends pas ce que tu cherches à faire, j'aurais plutôt essayer quelque chose comme cela :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
create or replace trigger t_test 
before insert on test for each row 
declare 
vtest varchar(20); 
begin 
-- en supposant que DD-MM-YYYY HH24:MI:SS soit le format de la chaine
:new.Madate := to_timestamp (:new.MaChaine, 'DD-MM-YYYY HH24:MI:SS');
end;

Et de faire un insert comme ça :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

insert into test (col1, MaDate, MaChaine) values (1, '', '2005-01-01 12:12:12');

NB : merci de penser aux balises code

[Goon]

Ok je vais essayer de resumer:
J'ai une application qui construit et execute des insert de donnes dont des dates au format US (2005-01-01 12:12:12 par exemple).
L'ancienne base acceptait ce format(PostGreSql).
Mais la nouvelle (oracle) possede un format date de type FR
(01/01/05 12:12:12).

L'insert des données est donc impossible dans la nouvelle base, et je cherche un moyen, à l'aide de trigger, pour transformer ce format sans avoir besoin de modifier le code de l'application ni les paramètres oracle.

C'est pourquoi je souhaite dans le trigger convertir le timestamp en chaine de caractere, afin de pouvoir le remanier au format fr, et pouvoir apres le reinjecter grace à la fonction to_timestamp.

J'espère que tu y vois plus clair maintenant, et désolais pour les balises

[plaineR]

J'ai compris ton problème, mais si dans l'insert tu envoies dans une colonne timestamp une chaine dont le format n'est pas celui de ta base, tu vas droit au plantage peu importe que tu aies un trigger ou pas.

[Goon]

Ok, à ce moment là est-ce qu'au moment de creer mes tables je peux spécifier un format spécifique pour mon timestamp (qui correspondra au format US) ?

[plaineR]

Je ne vois que 2 solutions à ton problème :
1. Corriger toutes les requêtes de ton appli en utilisant to_timestamp avec le bon format.

2. Passer par une vue :
a) tu renommes ta table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
SQL> rename test to test_table;
Table renamed.

b) tu crées une vue ayant le nom de table auparavant mais dont la colonne timestamp est remplacee de la manière suivante :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
create view test as 
   (select col1, to_char (col2, 'DD-MM-YYYY HH24:MI:SS') col2
    from test_table);

c) tu crées un trigger sur ta vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create or replace trigger t_test 
instead of insert or update or delete on test
for each row 
declare
begin
if inserting then
insert into test_table (col1, col2)
values (:new.col1, to_timestamp (:new.col2, 'DD/MM/RRRR HH24:MI:SS'));
elsif updating then
...
elsif deleting then
...
end if;
end;
/

Cette solution est un peu lourde mais a le mérite de fonctionner

[Goon]

Sauf que si je fais un insert avec un format US je vais droit sur l'erreur:
ORA-01830: données surnuméraires après la conversion correcte d'une chaîne en entrée

De plus, si l'application tente de recupérer des infos dans cette table, il faut que je modifie le code pour que l'extraction se fasse sur test_table.

Bon, cette fois je crois que les carottes sont cuites
En tout cas je te remercie pour ta patience et la qualité de tes réponses.