Créer une table en PL/SPL [Résolu]

[bsidy1]

Bonjour
Je voudrai créer une table dans mon script pl/sql et j'ai tjrs des erreurs
ci dessous le code

Code :

1
2
3
4
5
6
7
8
9
 
 
   EXECUTE IMMEDIATE   CREATE TABLE  BONUS_PROGRAMME      AS     
              (   SELECT   *  FROM 
                  (    SELECT  (SELECT (sysdate-7) FROM dual )  AS the_date,  TXNLPATTERNA AS MOBILE,     sum(cost) AS MONTANT, SUM(BILLED_DURATION) AS DUREE   FROM tmp_revenu_week 
                       WHERE CALLBNUMBER IN ('derede', 'dddee' ,'Cfer' ,'dddddd', 'derE' ,'ffff' , 'fzzzzz', 'zzzzz' ,'dere','sdsdsd' ,'Sdse') AND bltpid='1' AND PRDTID IN (6,10,14) GROUP BY   TXNLPATTERNA
                  )  
                      WHERE MONTANT >4000 AND MONTANT < 20001 AND ROWNUM < 11001 
              )   ;

je sais compter sur vous tous

[mnitu]

Execute immediate
C'est une mauvaise idée de créer des tables en PL/SQL.

[bsidy1]

Je vois bien le mal mais je voudrai corriger ces erreurs
est ce que qlqun peut m'aider

[LBO72]

Bonjour,

Le execute immediate fonctionne en PlSql. Essaye ceci :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
BEGIN
 EXECUTE IMMEDIATE 
              'CREATE TABLE  BONUS_PROGRAMME      AS
                  (SELECT   *  FROM
                         (SELECT  (SELECT (sysdate-7) FROM dual )  AS the_date,  TXNLPATTERNA AS MOBILE,     sum(cost) AS MONTANT, SUM(BILLED_DURATION) AS DUREE   FROM tmp_revenu_week
                          WHERE CALLBNUMBER IN (''derede'', ''dddee'' ,''Cfer'' ,''dddddd'', ''derE'' ,''ffff'' , ''fzzzzz'', ''zzzzz'' ,''dere'',''sdsdsd'' ,''Sdse'') AND bltpid=''1'' AND PRDTID IN (6,10,14)
                          GROUP BY   TXNLPATTERNA )
                   WHERE MONTANT >4000 AND MONTANT < 20001 AND ROWNUM < 11001 )   ;';
END;
/

[LBO72]

Essaye plutôt cela : Il faut supprimer le ; du execute immediate :

Code :

1
2
3
4
5
6
7
8
9
10
11
 
BEGIN
 EXECUTE IMMEDIATE 
              'CREATE TABLE  BONUS_PROGRAMME      AS
                  (SELECT   *  FROM
                         (SELECT  (SELECT (sysdate-7) FROM dual )  AS the_date,  TXNLPATTERNA AS MOBILE,     sum(cost) AS MONTANT, SUM(BILLED_DURATION) AS DUREE   FROM tmp_revenu_week
                          WHERE CALLBNUMBER IN (''derede'', ''dddee'' ,''Cfer'' ,''dddddd'', ''derE'' ,''ffff'' , ''fzzzzz'', ''zzzzz'' ,''dere'',''sdsdsd'' ,''Sdse'') AND bltpid=''1'' AND PRDTID IN (6,10,14)
                          GROUP BY   TXNLPATTERNA )
                   WHERE MONTANT >4000 AND MONTANT < 20001 AND ROWNUM < 11001 )   ';
END;
/

Cdlt,

LBO72

[bsidy1]

bonjour a tout le monde

j'ai opté pour le truncate
merci infiniment