[11gR2] ORA-02287 : faire un insert avec un WITH

INSERT INTO LISTE_IDENTIFIANTS(ID_IDENT,AGT_MATRICULE, APP_C_APPLI, L_IDENT, D_DER_MAJ,B_ACTIF, APP_PROFIL, APP_ENV,APP_SAISIE) WITH ALL_COMPTES_AD AS ( SELECT matricule, cast(t.column_value.extract('//text()') AS varchar2(50)) unmemberof FROM COMPTES_AD, TABLE( xmlsequence( xmltype( '<x><x>' || REPLACE(REPLACE(MEMBEROF,' ',NULL), ',', '</x><x>') || '</x></x>' ).EXTRACT('//x/*'))) T ) SELECT s_ide.nextval, MATRICULE, 'INTRANET' AS APP_C_APPLI, UNMEMBEROF, SYSDATE, 'O' AS B_ACTIF, '', '', 'N' AS APP_SAISIE FROM ALL_COMPTES_AD WHERE unmemberof LIKE 'yga_dossier%' ORDER BY MATRICULE

Hello,

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create sequence seq1;
 
create table tst (id number, nom varchar2(20), seq number);
INSERT INTO tst
WITH ALL_COMPTES_AD AS
(
SELECT 1 id, 'name1' nom from dual union all
SELECT 2 , 'name2'  from dual union all
SELECT 5 , 'name5'  from dual 
)
SELECT id,nom,seq1.NEXTVAL
  FROM ALL_COMPTES_AD; 
 
3 rows inserted

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
 
INSERT INTO tst
WITH ALL_COMPTES_AD AS
(
SELECT 1 id, 'name1' nom from dual union all
SELECT 2 , 'name2'  from dual union all
SELECT 5 , 'name5'  from dual 
)
SELECT id,nom,seq1.NEXTVAL
  FROM ALL_COMPTES_AD 
order by nom;
 
Erreur à la ligne de commande : 11, colonne : 19
Rapport d'erreur :
Erreur SQL : ORA-02287: numéro de séquence non autorisé ici
02287. 00000 -  "sequence number not allowed here"
*Cause:    The specified sequence number (CURRVAL or NEXTVAL) is inappropriate
           here in the statement.
*Action:   Remove the sequence number.

Pas d'order by!

jko