Créer une table en PL/SPL

Version imprimable

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

19/12/2012, 13h19
mnitu

Execute immediate
C'est une mauvaise idée de créer des tables en PL/SQL.
19/12/2012, 13h54
bsidy1

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

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;
/

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

20/12/2012, 11h40
bsidy1

bonjour a tout le monde

j'ai opté pour le truncate
merci infiniment