Problème de traitement de données après chargement dans une table temporaire [Résolu]

[Djene]

Bonjour,

Des données sont chargées dans les colonnes d'une table temporaire depuis une table externe. Certains traitements sont à effectuer sur certaines colonnes avant chargement dans la table cible définitive.

Je souhaiterais savoir comment implémenter le tout.
Le chargement table externe vers table temporaire est ok!
Simplement je souhaite savoir si je dois:
1/ Effectuer les calculs (=traitements) sur les colonnes une fois que les données sont chargées dans la table temporaire ?
2/ Ou alors dois-je implémenter les calculs à effectuer sur les colonnes en même temps qu'elles sont chargées depuis la table externe ?

J'ai essayé de faire le traitement après chargement mais le programme ne semble pas aimer. Voilà la structure de mon code :

1/ Chargement dans table temporaire depuis la table externe:

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
 
 BEGIN
          IF is_INDICES THEN
                EXECUTE IMMEDIATE '
                INSERT INTO toto_temp 
          (
                    key_id,
                    evaluation_date,
                     ... etc...
                    event_type,
                    event_amount,
                    terms,
                    termsdate,
                    status     
               )
                SELECT
                    keyidseq.NEXTVAL,
                     evaluation_date,
                     ... etc...
                    event_type,
                    event_amount,
                    terms,
                    termsdate,
                    status
                FROM '||extTableName;    
         ELSE
                EXECUTE IMMEDIATE '
                INSERT INTO toto_temp                  
               (
                    key_id,
                    evaluation_date,
                     ... etc...
                    event_type,
                    event_amount,
                    terms,
                    termsdate,
                    status 
                )
                  SELECT 
                keyidseq.NEXTVAL,
                     evaluation_date,
                    ... etc...
                    event_type,
                    event_amount,
                    terms,
                    termsdate,
                    status
               FROM '||extTableName; 
          end IF;
Exception
...
end;

2/ Ma solution de traitement suivait ainsi dans la même procédure :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
Begin 
   IF IS_NOT_INDICES THEN
      SELECT ecs.event_type INTO v_event_type FROM TOTO_TEMP ecs
     WHERE ecs.termsdate = ecs.evaluation_date;
      AND ecs.STATUS = 'IMPLEMENTED';
 
        IF v_event_type IS NOT NULL THEN 
        SELECT REPLACE(regexp_substr(ecs.event_type, '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),'SPECIAL','')
          INTO v_event_amount
          FROM TOTO_TEMP ecs
         WHERE ecs.termsdate = ecs.evaluation_date
           AND ecs.STATUS = 'IMPLEMENTED';
         END IF;
 
        IF v_event_amount IS NOT NULL 
        THEN
         UPDATE toto_temp  SET 
               special_div_in_prov_currency =  v_event_amount;  
       END IF;

Mais cela ne fonctionne pas.
J'ai eu alors dans l'idée d'implémenter ces traitements directement sur les colonnes durant le processus de chargement mais cela me semble lourd et surtout je ne saurais comment faire.

J'ai donc nesoin d'être guidée.

Merci pour votre aide.

[mnitu]

L’idée d’utiliser une table de travail en plus de la table externe est très probablement erronée.
Utiliser votre table externe comme une table normale et dites nous qu’est-ce que vous voulez faire avec vos données. Avec un exemple concret SVP.
Sinon insert into provoque une exception NO_DATA_FOUND quand aucun enregistrement ne corresponde aux critères de sélection.

[Djene]

Bonjour Mnitu et merci pour votre réponse,

Simplement, l'environnement telle que décrite est implémenté de la sorte.
Les données sont chargées depuis des fichier CSV dans des tables externes à partir desquelles elles sont chargées dans une table de travail temporaire. Et la table temporaire à son tour charge la table de travail définitive après différents traitements.
A mon niveau, je dois faire des traitements sur des données au moment où elles sont insérées dans la table temporaire avant que le traitement général ne les insère dans la table cible définitive.
Un fois la table temporaire rensignée, et avant qu'elle ne soit droppée, je dois :
1/ extraire des montants à calculer depuis certaines colonnes de la table temporaire,
2/ faire des calculs sur ces montants (appliquer des taux de change)
3/ et ensuite renseigner 3 colonnes de cette même table par un update avec le résultat des calculs avant qu'elles n'aillent vers la table de destination.

J'espère que mes explications sont assez claires.

Merci pour votre aide.

[Djene]

Par rapport à mon code précédent, suis-je sur la bonne voie en faisant un select ... into ... from ma table temporaire ?
Dois-je intégrer une boucle :

Code :

Begin Loop ... end loop; EXCEPTION END;

pour la lecture et le traitement des données depuis la table temporaire ?


Merci d'avance.

[Djene]

Bonjour,

Une fois que ma table temporaire EQUITY_TMP est chargée,
Je lance le traitement suivant :

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
 
 
  Begin
 
  IF IS_INDICES 
  THEN
    FOR C IN (SELECT ecs.event_amount INTO v_event_amount 
                       FROM toto_tmp ecs
                    WHERE ecs.termsdate = ecs.evaluation_date
                        AND ecs.STATUS = 'IMPLEMENTED'
                        AND ecs.event_amount IS NOT NULL) 
     LOOP
 
             IF v_event_amount IS NOT NULL 
             THEN 
                SELECT REPLACE(regexp_substr(ecs.event_amount, '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),'SPECIAL','')
                 INTO v_event_amount
                 FROM toto_tmp ecs
                 WHERE ecs.termsdate = ecs.evaluation_date
                 AND ecs.STATUS = 'IMPLEMENTED';
               END IF;
 
              IF v_event_amount IS NOT NULL THEN 
              UPDATE toto_tmp ecs  SET 
              special_div =  v_event_amount;
 
      END IF;              
 END LOOP;

Simplement, le taritement de l’UPDATE ne se fait pas.
Comment faire en sorte que cela fonctionne ?

Merci pour votre aide.

[Rams7s]

Ca depend de comment vous creez votre table temporaire peut-etre.

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
TMP@minilhc >DROP TABLE tmp;
 
TABLE dropped.
 
TMP@minilhc >CREATE global TEMPORARY TABLE tmp (n number);
 
TABLE created.
 
TMP@minilhc >INSERT INTO tmp SELECT level FROM dual connect BY level<11;
 
10 rows created.
 
 
TMP@minilhc >SELECT * FROM tmp;
 
         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected.
 
TMP@minilhc >commit;
 
Commit complete.
 
TMP@minilhc >SELECT * FROM tmp;
 
no rows selected

Et le comportement pas par defaut:

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
 
TMP@minilhc >DROP TABLE tmp;
 
TABLE dropped.
 
TMP@minilhc >CREATE global TEMPORARY TABLE tmp (n number) ON commit preserve rows;
 
TABLE created.
 
TMP@minilhc >INSERT INTO tmp SELECT level FROM dual connect BY level<11;
 
10 rows created.
 
TMP@minilhc >commit;
 
Commit complete.
 
TMP@minilhc >SELECT * FROM tmp;
 
         N
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected.
 
TMP@minilhc >TRUNCATE TABLE tmp;
 
TABLE truncated.
 
TMP@minilhc >DROP TABLE tmp;
 
TABLE dropped.

Je dis pas que c'est ca, mais ca peut-etre une erreur bete a laquelle on ne fait pas attention lors de la creation de la table.

[Djene]

La table temporaire existe déjà et est effectivement droppée à la fin de chaque traitement.

Le code concerné doit UPDATER certaines données.
Ces données sont lues depuis la table temporaire une fois celle-ci chargée.
J'ai mis en place une boucle FOR ... LOOP ... end loop; dans laquelle un UPDATE a lieu sur une des colonnes de cette table temporaire selon certaines conditions.

C'est cet UPDATE qui ne fonctionne pas et je souhaiterais savoir pourquoi et que faire pour y remédier.

Merci pour votre aide.

[Rams7s]

Non en fait, c'est pas ce que j'aurais voulu dire.

Si apres avoir fait l'import depuis votre table externe dans la table temporaire vous faites un commit, et que dans le create vous avez omis la clause

Code :

ON commit preserve rows

alors ca les efface a chaque commit.
Du coup pas d'update.
avant de lancer votre update, faites donc un select count(*) du select a l'interieur du FOR, juste pour verifier que ce n'est pas vide.

[Djene]

La purge de la table temporaire + commit a lieu beaucoup plus loin dans la suite du traitement.

Cependant, effectivement en utilisant le debugger, je vois que le SELECT de ma boucle FOR a des données à NULL alors que dans mon fichier de test, au moins deux lignes remplissent les conditions requises pour l'UPDATE.
Je me demande si mon LOOP est bien placé.

Lorsque je retire la Boucle FOR, j'ai un message d'erreur me disant que le Select remonte plus d'une ligne ...

Lorsque je fais en sorte de n'avoir qu'une ligne remplissant les conditions sans la boucle FOR, mon select remonte bien la ligne souhaitée et l'UPDATE a lieu.
Simplement, le fichier réel peut contenir des milliers de lignes et plusieurs d'entres elles peuvent remplir les conditions d'UPDATE.

Comment corriger ?

Merci par avance

[Djene]

Bonjour,

Mon code ci-dessous ramène l'erreur : ORA-01422: exact fetch returns more than requested number of rows

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
 
BEGIN
    --
  IF IS_INDICES AND provider = 'NASDAQ'
 THEN
  FOR C IN 
    (SELECT event_amount  
               FROM toto_tmp
               WHERE termsdate = evaluation_date
                 AND STATUS = 'IMPLEMENTED'
    )
 
    LOOP
       IF c.event_amount IS NOT NULL THEN
            SELECT REPLACE(regexp_substr(event_amount, '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),'SPECIAL','')
                INTO v_event_amount_2
                 FROM toto_tmp
                 WHERE termsdate = evaluation_date
                   AND STATUS = 'IMPLEMENTED'
                   AND event_amount IS NOT NULL;
          END IF;
 
             IF v_event_amount_2 IS NOT NULL 
            THEN 
             UPDATE toto_tmp SET 
                    special_div_in_prov_currency = v_event_amount_2
              WHERE termsdate = evaluation_date
                   AND STATUS = 'IMPLEMENTED';
             END IF;
  exit when sql%notfound;            
 
  END LOOP;
 
 
----------------------------------------------------------------------
info: inserting INTO toto_tmp
 
12:05:57 - info               | 100 rows inserted IN staging
12:05:57 - info               | Begin matching dividend exceptionnel
12:06:52 - error              | Error inserting FROM External TABLE
12:06:53 - error              | Error: ORA-01422: exact fetch returns more than requested number of rows

Comment corriger ?

Merci

[Garuda]

1) Il y a une boucle inutile
2) Pas de exit dans une boucle avec curseur

Ma proposition en PL/SQL

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
CURSOR cur_toto
   IS
      SELECT event_amount,
             REPLACE (
                REGEXP_SUBSTR (event_amount,
                               '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),
                'SPECIAL',
                '')
                event_amount_2
        FROM toto_tmp
       WHERE     termsdate = evaluation_date
             AND STATUS = 'IMPLEMENTED'
             AND event_amount IS NOT NULL;
   FOR   UPDATE;
BEGIN
   .............
   IF IS_INDICES AND provider = 'NASDAQ'
   THEN
      FOR C IN cur_toto
      LOOP
         IF c.event_amount_2 IS NOT NULL
         THEN
            UPDATE toto_tmp
               SET special_div_in_prov_currency = v_event_amount_2
             WHERE CURRENT OF cur_toto;
         END IF;
      END LOOP;
   ---
   END IF;
......
END;

En SQL ( conseillé)

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
/* Formatted on 27/10/2011 14:33:47 (QP5 v5.185.11230.41888) */
UPDATE toto_tmp
   SET special_div_in_prov_currency =
          REPLACE (
             REGEXP_SUBSTR (event_amount,
                            '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),
             'SPECIAL',
             '')
 WHERE     event_amount IS NOT NULL
       AND REPLACE (
              REGEXP_SUBSTR (event_amount,
                             '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),
              'SPECIAL',
              '')
              IS NOT NULL;

ou

Code :

1
2
3
4
5
6
7
8
9
UPDATE toto_tmp
   SET special_div_in_prov_currency =
          NVL(REPLACE (
             REGEXP_SUBSTR (event_amount,
                            '[[:digit:]]{1}.[[:digit:]]* SPECIAL'),
             'SPECIAL',
             ''),event_amount)
 WHERE     event_amount IS NOT NULL;

[Djene]

Merci, Garuda !

Je tente de faire la même chose avec le code qui suit:

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
 
Déclaration du curseur:
 
CURSOR cur_toto
        IS
            SELECT currency_code,
                    terms,
                    event_type,
               CASE event_type
              WHEN 'SPEC_DIV' THEN
                    substr(regexp_substr(terms,'Specdiv [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),9,3)
          ELSE NULL
          END MONNAIE,
          CASE event_type
           WHEN 'SPEC_DIV' THEN
                   substr(regexp_substr(terms,'Specdiv [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),13)
           ELSE NULL
           END MONTANT
         INTO  v_sec_currency_code,
               v_terms,
               v_event_type,
               v_spec_div_prov_currency,
               v_spec_div_in_prov_amount
          FROM toto_temp
            WHERE termsdate = evaluation_date
           AND STATUS = 'IMPLEMENTED' 
           AND event_type = 'SPEC_DIV';
       FOR UPDATE;

Ensuite :

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
 
BEGIN
IF IS_INDICES AND provider = 'NASDAQ'
   THEN
...
...
---
...
ELSE 
      FOR C IN cur_toto
       LOOP
       IF c.currency_code <> v_spec_div_prov_currency
         THEN 
         -- Appel de la fonction de calcul des taux de change
           v_special_dividend_amount := sf_compute_spec_div_in_sec_ccy
            (
                p_sec_currency_code       =>  v_sec_currency_code,
                p_spec_div_prov_currency  =>  v_spec_div_prov_currency,
                p_spec_div_amount         =>  v_spec_div_in_prov_amount
                );
 
            pkg_log.info('    '||'After conversion the special dividend amount is : '||v_special_dividend_amount); 
            pkg_log.info('    ');  
 
            IF v_special_dividend_amount IS NOT NULL
            THEN UPDATE toto_tmp SET
                   special_div_in_sec_currency  =   v_special_dividend_amount,
                   special_div_in_prov_currency =   v_spec_div_in_prov_amount,
                   special_div_prov_currency    =   v_spec_div_prov_currency;
            WHERE CURRENT OF cur_toto;  
            END IF; 
     ELSE 
          --c.currency_code = v_spec_div_prov_currency
           UPDATE toto_tmp SET
                 special_div_in_sec_currency  =   v_spec_div_in_prov_amount,
                 special_div_in_prov_currency =   v_spec_div_in_prov_amount,
                 special_div_prov_currency    =   v_sec_currency_code
WHERE CURRENT OF cur_toto;
END IF; 
       END LOOP;
 END IF;

1/ Mon code met une erreur sur le FOR UPDATE du curseur de déclaration ainsi que sur le WHERE CURRENT OF cur_toto.

2/ A l'exécution, j'ai le message suivant :

Code :

1
2
3
4
 
inserting INTO toto_tmp
18:01:16 - error               | Error: ORA-29913: error IN executing ODCIEXTTABLEFETCH callout
                               | ORA-30653: reject LIMIT reached

Grand merci pour votre aide !

[Garuda]

1) Il ne faut pas mettre de into dans la section DECLARE (curseur).
L'affectation est IMPLICITE !

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
CURSOR cur_toto
        IS
            SELECT currency_code,
                    terms,
                    event_type,
               CASE event_type
              WHEN 'SPEC_DIV' THEN
                    substr(regexp_substr(terms,'Specdiv [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),9,3)
          ELSE NULL
          END MONNAIE,
          CASE event_type
           WHEN 'SPEC_DIV' THEN
                   substr(regexp_substr(terms,'Specdiv [[:upper:]]{3} [[:digit:]]{1}.[[:digit:]]*',1),13)
           ELSE NULL
           END MONTANT
       /*  INTO  v_sec_currency_code,
               v_terms,
               v_event_type,
               v_spec_div_prov_currency,
               v_spec_div_in_prov_amount*/
          FROM toto_temp
            WHERE termsdate = evaluation_date
           AND STATUS = 'IMPLEMENTED' 
           AND event_type = 'SPEC_DIV';
       FOR UPDATE;

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
 
FOR C IN cur_toto
       LOOP
       IF c.currency_code <> C.MONNAIE
         THEN 
         -- Appel de la fonction de calcul des taux de change
           v_special_dividend_amount := sf_compute_spec_div_in_sec_ccy
            (
                p_sec_currency_code       =>  v_sec_currency_code,
                p_spec_div_prov_currency  =>  C.MONNAIE,
                p_spec_div_amount         =>  C.MONTANT
                );
 
            pkg_log.info('    '||'After conversion the special dividend amount is : '||v_special_dividend_amount); 
            pkg_log.info('    ');  
 
            IF v_special_dividend_amount IS NOT NULL
            THEN UPDATE toto_tmp SET
                   special_div_in_sec_currency  =  v_special_dividend_amount,
                   special_div_in_prov_currency =   C.MONTANT,
                   special_div_prov_currency    =   C.MONNAIE
            WHERE CURRENT OF cur_toto;  
            END IF; 
     ELSE 
          --c.currency_code = C.MONNAIE
           UPDATE toto_tmp SET
                 special_div_in_sec_currency  =   C.MONTANT,
                 special_div_in_prov_currency =   C.MONTANT,
                 special_div_prov_currency    =   C.MONNAIE
WHERE CURRENT OF cur_toto;

2) A quoi sert le case puisque que le where ne selectionne que les données dont EVENT_TYPE='SPEC_DIV'
3) L'appel a la fonction peut être mis directement dans le SELECT
4) Encore une fois, il est inutile de faire une boucle PL/SQl. Un simple UPDATE SQL est largement faisable ( et préférable)

[Djene]

Grand merci Garuda pour votre aide !

Pour répondre à votre question:
2) A quoi sert le case puisque que le where ne selectionne que les données dont EVENT_TYPE='SPEC_DIV'

Le CASE sur EVENT_TYPE sert à nous indiquer que un montant est à extraire de la chaine de caractère de la col TERMS.


J'ai appliqué votre solution, simplement à l'exécussion, malgré le curseur, j'ai toujours ce message d'erreur :

Code :

1
2
 
Error: ORA-01422: exact fetch returns more than requested number of rows

Merci!

[Garuda]

Le case ne sert à rien car les lignes selectionnées répondront toujours au case !
L'erreur ne peut pas se trouver dans le code que j'ai envoyé car il n'a pas de SOUS-REQUETE !

Code :

1
2
3
4
5
6
7
8
9
 CASE event_type
           WHEN 'SPEC_DIV' THEN
....
 
WHERE termsdate = evaluation_date
           AND STATUS = 'IMPLEMENTED' 
           AND event_type = 'SPEC_DIV';

Merci d'envoyer le code complet de la procedure

[Djene]

L'erreur ne se trouvait effectivement pas dans le code; il s'agissait d'un ancien code Select qui gênait. Une fois retiré, tout fonctionne merveilleusement !

Grand Grand Merci!