Gestion heure été / hiver sql oracle

Version imprimable

Merci pour votre aide précieuse.

j'ai trouvé ce code sur le forum et j'aimerais l'adapter au language sql oracle mais je n'y arrive pas. Des problèmes de Returns, d'@ etc...

Voici l'erreur :

Code:

1
2
 
Erreur(2,31): PLS-00103: Symbole "@" rencontré à la place d'un des symboles suivants :     <identificateur> <identificateur entre guillemets> current    delete exists prior

Merci pour votre aide.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
 
CREATE FUNCTION dbo.F_CHANGEMENT_HEURE (@AN INT)  
 
   RETURNS @T TABLE (AN SMALLINT, JOUR DATETIME, SAISON VARCHAR(5), DECALAGE_UTC SMALLINT)  
 
AS  
 
BEGIN  
 
DECLARE @DD DATETIME, @AND SMALLINT, @ANF SMALLINT;  
 
SELECT @AND = COALESCE(@AN, 1976), @ANF = COALESCE(@AN, 2100)  
 
WHILE @AND <= @ANF  
 
BEGIN  
 
-- passage à l'heure d'été, le dernier dimanche de mars à 1h  
 
   SET @DD = CAST(CAST(@AND AS CHAR(4)) + '0331 00:01' AS DATETIME);  
 
   WHILE DATEPART(weekday, @DD) <> 7  
 
      SET @DD = DATEADD(day, -1, @DD);  
 
   INSERT INTO @T VALUES (@AND, @DD, 'Été', +2);        
 
   IF @AND < 1996 
 
   BEGIN 
 
   -- passage à l'heure d'été, le dernier dimanche de septembre à 1h  
 
      SET @DD = CAST(CAST(@AND AS CHAR(4)) + '0930 00:01' AS DATETIME);  
 
      WHILE DATEPART(weekday, @DD) <> 7  
 
         SET @DD = DATEADD(day, -1, @DD);  
 
      INSERT INTO @T VALUES (@AND, @DD, 'Hiver', +2);        
 
   END    
 
   ELSE 
 
   BEGIN 
 
-- passage à l'heure d'hiver, le dernier dimanche d'octobre à 1h  
 
      SET @DD = CAST(CAST(@AND AS CHAR(4)) + '1031 00:01' AS DATETIME);  
 
      WHILE DATEPART(weekday, @DD) <> 7  
 
         SET @DD = DATEADD(day, -1, @DD);  
 
      INSERT INTO @T VALUES (@AND, @DD, 'Hiver', +1);              
 
   END    
 
   SET @AND = @AND + 1;     
 
END     
 
RETURN;  
 
END     
 
GO

04/11/2016, 12h50
al1_24

Ces noms de variables commençant par un @ me font immédiatement penser à du TSQL (Sybase, SQL Server).
Tu vas avoir pas mal de travail pour convertir tout cela en PL/SQL. :aie:
04/11/2016, 13h54
cragoroth

oui j'ai trouvé ici :

http://blog.developpez.com/sqlpro/p7...nts_d#comments

merci pour la précision mais oui j'avais remarqué le sql server c'est bien là mon problème le create procédure d'Oracle est différent dans l'expression des variables.

une petite aide ? quelque chose existe peut-être déjà en oracle ou java, c'est pour utiliser sous Talend.

Merci pour l'aide

Voici aujourd'hui comment je ferais cela en 1 seule requête, plus facile à traduire :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
DECLARE @AN SMALLINT = 2000;
 
WITH 
T_DATE_HE AS
(SELECT DATEFROMPARTS(@AN, 3, 1) AS JOUR, DATEPART(WEEKDAY, DATEFROMPARTS(@AN, 3, 1)) AS JOUR_NUM
 UNION ALL
 SELECT DATEADD(day, 1, JOUR),  DATEPART(weekday, DATEADD(day, 1, JOUR))
 FROM   T_DATE_HE
 WHERE  MONTH(DATEADD(day, 1, JOUR))  = 3
 ),
T_DATE_EH AS
(SELECT DATEFROMPARTS(@AN, CASE WHEN @AN < 1996 THEN 9 ELSE 10 END, 1) AS JOUR, 
        DATEPART(weekday, DATEFROMPARTS(@AN, CASE WHEN @AN < 1996 THEN 9 ELSE 10 END, 1)) AS JOUR_NUM
 UNION ALL
 SELECT DATEADD(day, 1, JOUR),  DATEPART(weekday, DATEADD(day, 1, JOUR))
 FROM   T_DATE_EH
 WHERE  MONTH(DATEADD(day, 1, JOUR)) = CASE WHEN @AN < 1996 THEN 9 ELSE 10 END
 ),
T_DATE AS
(
SELECT 'Été-Hiver' AS CHANGEMENT, *, RANK() OVER(PARTITION BY JOUR_NUM ORDER BY JOUR DESC) AS N 
FROM   T_DATE_EH
UNION ALL
SELECT 'Hiver-Été' AS CHANGEMENT, *, RANK() OVER(PARTITION BY JOUR_NUM ORDER BY JOUR DESC) AS N 
FROM   T_DATE_HE
)
SELECT CHANGEMENT, JOUR 
FROM   T_DATE
WHERE  JOUR_NUM = 7 AND N = 1
ORDER BY JOUR;

A +

04/11/2016, 16h13
cragoroth

Merci pour ta réponse.

Mais comment imbriques-tu cela dans une fonction ou procédure. je connais un peu mais je galère un max pour intégrer cela.

En PL/SQL ça donnerait ça :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
create or replace type typ_changement_heure as object
( jour          date
, saison        varchar2(5 char)
, decalage_utc  varchar2(2 char)
);
/
-- Elément Type TYP_CHANGEMENT_HEURE compilé
 
create or replace type tbl_changement_heure
as table of typ_changement_heure;
/
-- Elément Type TBL_CHANGEMENT_HEURE compilé
 
create or replace function f_changement_heure (p$_annee integer)
return tbl_changement_heure pipelined
as
    c$_annee_dt constant date := trunc(to_date(to_char(p$_annee), 'yyyy'), 'yyyy') + interval '1' hour;
begin
    for c$_cur in
    (
    with cte_dates (jour, saison, decalage_utc) as
    (
        select c$_annee_dt + interval '3' month - level
             , 'Hiver'
             , '+2'
          from dual
    connect by level <= 7
         union all
        select c$_annee_dt + interval '10' month - level
             , 'Été'
             , '+1'
          from dual
    connect by level <= 7
    )
    select jour, saison, decalage_utc
      from cte_dates
     where to_char(jour, 'Day', 'nls_date_language=French') = 'Dimanche'
    )
    loop
        pipe row (typ_changement_heure(c$_cur.jour, c$_cur.saison, c$_cur.decalage_utc));
    end loop;
end;
/
-- Elément Function F_CHANGEMENT_HEURE compilé
 
show errors;
-- Aucune erreur.

Et la récupération des données :

Code:

1
2
3
4
5
6
select * from table(f_changement_heure(2016));
 
JOUR                SAISON DECALAGE_UTC
------------------- ------ ------------
2016-03-27 01:00:00 Hiver  +2
2016-10-30 01:00:00 Été    +1

04/11/2016, 16h22
cragoroth

je comprends mieux le fonctionnement.

tu crée un objet fictif comme une table en mode object
a partir de cet objet tu crée ta table

et ensuite en avant le code.

merci beaucoup, il me reste plus qu'à faire la comparaison de date dans Talend, la mettre dans ma variable globale et le tour est joué.

Tu m'as fais gagner un temps fou.

merci et bonne soirée alors et bon weekend

Plus simple, nul besoin d'un changement de contexte avec le moteur SQL

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> create or replace function f_changement_heure2 (annee In integer)
  2  return tbl_changement_heure pipelined
  3  as
  4      cstDatEte    Date := To_Date('31/03/'||annee,'DD/MM/YYYY');
  5      cstDatHiver  Date := To_Date('31/10/'||annee,'DD/MM/YYYY');
  6  begin
  7     pipe row (typ_changement_heure(cstDatEte   - mod(To_Number(To_Char(cstDatEte,'D')),7) + 1/24, 'Été', '+2'));
  8     pipe row (typ_changement_heure(cstDatHiver - mod(To_Number(To_Char(cstDatHiver,'D')),7) + 1/24, 'Hiver', '+1'));
  9  end;
 10  /
Function created
 
SQL> select * from table(f_changement_heure2(2016))
  2  /
JOUR        SAISON DECALAGE_UTC
----------- ------ ------------
27/03/2016  Été    +2
30/10/2016  Hiver  +1

2 pièce(s) jointe(s)

Merci pour ces réponses. Les deux codes fonctionne.
j'ai cependant un problème sous Talend quand j'execute les codes. peut-être auriez-vous une piste ?

Pièce jointe 224458

Code:

1
2
3
4
5
6
7
8
Démarrage du job J_Init_Changement_Heure_Ete_Hiver a 15:56 09/11/2016.
[statistics] connecting to socket on port 3718
[statistics] connected
2016-11-09 15:56:44|YLKp51|YLKp51|YLKp51|NEWTON_INFOMART_MULTIMEDIA|J_Init_Changement_Heure_Ete_Hiver|Local|4|tWarn|tWarn_2|Déb création Chg Heure|42
2016-11-09 15:56:45|YLKp51|YLKp51|YLKp51|NEWTON_INFOMART_MULTIMEDIA|J_Init_Changement_Heure_Ete_Hiver|Local|4|tWarn|tWarn_23|Déb create typ_changement_heure|42
ORA-02303: impossible de supprimer ou de remplacer un type dont dépendent des types ou des tables
2016-11-09 15:56:45|YLKp51|YLKp51|YLKp51|NEWTON_INFOMART_MULTIMEDIA|J_Init_Changement_Heure_Ete_Hiver|Local|4|tWarn|tWarn_1|Fin create typ_changement_heure OK|42

voila le zip :

Pièce jointe 224465

09/11/2016, 16h26
Waldar

N'utilisez pas un ETL pour faire des opérations DDL sur votre base, utilisez un vrai client SQL ou envoyez les requêtes à un DBA.

Les types et la fonction ne sont à créer qu'une fois, après vous pourrez les utiliser tant que vous voulez.
09/11/2016, 16h38
cragoroth

2 pièce(s) jointe(s)
En fait ce job permet de créer simplement les fonctions. Donc ok pour l'erreur elle n'a pas lieu d'être traité car tout se créé correctement.
C'est juste un façon simple de tout regénérer si besoin.

ok pour ce point.

Par contre une fois tout ça créer. Comment utilise cela sous Talend ?
Code:

1 2 select * from table(f_changement_heure(2016));
Je m'explique, j'aimerais dans mon job global, qu'en fin de traitement je test si la date de demain sera la date de changement d'heure été ou hiver.
Si c'est le cas j'aimerais prendre la valeur du tableau +2 ou +1 et la mettre dans une variable global qui existe déjà dans mon projet.
Comme ça à la prochaine ouverture et lancement de mon job, ma variable de context en entré de projet talend(via fichier .txt) sera initialiser à 1 ou 2 en fonction du jour. Cette variable ne bougera pas pendant 6 mois et changement que 2 fois par an.
Une idée du test à faire ? en flux si dateJour +1 est comprise entre les date de cette req

Code:

select * from table(f_changement_heure(2016));

alors j'écris dans mon fichier de context +1 ou +2 sinon je fait mon flux normalement ?

Merci de ton aide

Pièce jointe 224467
10/11/2016, 11h19
mnitu

Si c'est pour exécuter la procédure pipelined pour l'exercice courante vous pouvez caché son appel derrière une vue puis utiliser cette vue dans Talend si cela s'avère plus simple.
14/11/2016, 14h44
cragoroth
Merci mnitu.
Désolé pour la réponse tardive !

Comment tu utiliserais cela dans une vue ? cela voudrait dire que tu appel juste le rendu de ta vue sous Talend ? comme la lecture d'une table quoi ?
Mais comment crés-tu la vue car il me semble que tu peux juste faire appel à une table ou vue dans une vue et non une fonction ? c'est l'exécution de la création de la fonction. Je ne vois pas trop
Code:

1 2 select * from table(f_changement_heure(2016));
?

Quelque chose comme (en supposant que c'est juste l'année courante qui vous intéresse).

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
 
SQL> 
SQL> Create Or replace view w_dst_days as
  2  select jour, saison, decalage_utc
  3  from table(f_changement_heure(To_Char(Sysdate,'YYYY')))
  4  /
View created
 
SQL> Select jour, saison, decalage_utc 
  2  from w_dst_days
  3  /
JOUR        SAISON DECALAGE_UTC
----------- ------ ------------
27/03/2016  Hiver  +2
30/10/2016  Été    +1
 
SQL>

14/11/2016, 15h10
cragoroth
Salut,

C'est énorme de pouvoir faire ce genre de chose en un minimum de temps. J'ai essayé des synthax de mon côté et j'avais quelques erreurs que je n'arrive pas encore à déceler mais tout fonctionne parfaitement.
Encore merci beaucoup.

je vais pouvoir utiliser cela
Code:

1 2 Select jour, saison, decalage_utc from w_dst_day;
dans un input Talend et faire des tests sur la valeur JOUR et intégrer la valeur DECALAGE_UTC dans une variable de context global.
Je vous tiens au courant pour la solution côté Talend.

Merci sujet tjs ouvert pour la solution final de l'utilisation

1 pièce(s) jointe(s)

Alors voila ce que j'ai pu tester de mon côté :
J'ai rajouté pour la saison été la date de fin pour avoir un début et une fin afin de pouvoir voir si ma date du jour est comprise entre ces deux dates.
Corrigez moi si je me trompe ou si plus simple il y a mais ça à l'air de fonctionner.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 select case when sysdate between t.date_deb_chg_Heure and t.date_fin_chg_Heure then 1 else 0 end as testgfh from( Select jour as date_deb_chg_Heure , case when saison = 'Été' then (select JOUR from w_dst_day where saison ='Hiver') end as date_fin_chg_Heure , saison , decalage_utc from w_dst_day ) t where t.saison ='Été' ;
Ensuite je test la valeur de mon champ sur Talend dans un tmap ou autre pour écrire dans un fichier de context
- ouvrir un fichier txt, chercher l'ID à modifier et changer la valeur

J'ai trouvé ce composant mais je ne sais pas trop comment l'utiliser.
tFileInputFullRow que je mets dans un tMap et une sortie tLogRow toute simple afin de voir ce qu'il me sort. C'est plutôt bien mais j'aimerais changer la valeur 1 qui est dans ce fichier et mettre la valeur qui sort de la requête précédente(1 ou 2)

Pièce jointe 224884

Si quelqu'un sait comment écrire dans un fichier de context je suis preneur car je galère un peu.

En tout cas merci au personne qui réponde car ca m'aide bcp.

16/11/2016, 10h37
cragoroth

1 pièce(s) jointe(s)

Bonjour messieurs,

tout fonctionne parfaitement sous Talend. Voici la solution :
Pièce jointe 224955

du coup il faut adapter cité plus haut pour que ce soit pris en compte le 26/03/2017 c'est à 2h et non à 1h du matin.

Voici le code final

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
 
create or replace function f_changement_heure (p$_annee integer)
return tbl_changement_heure pipelined
as
    c$_annee_dt constant date := trunc(to_date(to_char(p$_annee), 'yyyy'), 'yyyy') + interval '2' hour;
begin
    for c$_cur in
    (
    with cte_dates (jour, saison, decalage_utc) as
    (
        select c$_annee_dt + interval '3' month - level
             , 'Été'
             , '+2'
          from dual
    connect by level <= 7
         union all
        select c$_annee_dt + interval '10' month - level + interval '1' hour
             , 'Hiver'
             , '+1'
          from dual
    connect by level <= 7
    )
    select jour, saison, decalage_utc
      from cte_dates
     where to_char(jour, 'Day', 'nls_date_language=French') = 'Dimanche'
    )
    loop
        pipe row (typ_changement_heure(c$_cur.jour, c$_cur.saison, c$_cur.decalage_utc));
    end loop;
end;