SQL dynamique - Récupérer tous les champs sauf certains

**boutss** · 12/07/2007, 09h48

Bonjour à tous,

Je suis en 9i. [PL/SQL]

En fait j'aimerai beaucoup que vous m'aidiez à construire une requête dynamiquement en récupérant les champs de sélection dans la table "user_tab_columns".

En fait le but est de récupérer tous les champs sauf certains.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
select * from user_tab_columns where table_name='TARTICLE'
and column_name <> 'ETATOBJET'

Après la construction de la requête on l'exécute avec EXECUTE IMMEDIATE.

En vous remerciant d'avance.
Boutss

**laurentschneider** · 12/07/2007, 11h33

un execute immediate d'un select, ça te sert à quoi?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
SQL> exec execute immediate 'select * from emp'
 
PL/SQL procedure successfully completed.

**pifor** · 12/07/2007, 11h47

Si on est sûr que la requête ne retourne qu'une seule ligne, on peut utiliser EXECUTE IMMEDIATE et récupérer le résultat dans des variables avec la clause INTO: voir l'exemple 8.1 dans le Application Developer's Guide - Fundamentals.

**boutss** · 12/07/2007, 11h51

En fait je me suis peut-être un peu avancé.
Je pensais que le execute immédiate permettait d'exécuter une requête sql dynamique.

Au temps pour moi.

Non en fait le résultat retournera plusieurs un grand nombre de ligne et sera stocké dans un fichier texte via spool.

**Bruno2r** · 12/07/2007, 12h14

En PL/SQL, le recours à EXECUTE IMMEDIATE mysql est nécessaire pour

1) les instructions SQL de DDL (DATA DEFINITION LANGUAGE) comme DROP ou CREATE TABLE.

2) les instructions SQL de DML (DATA MANIPULATION LANGUAGE) comme SELECT ou INSERT lorsque l'on veut construire dynamiquement le SQL en transmettant des valeurs de variables à la clause WHERE.

mysql = 'SELECT * FROM clients WHERE ville = :a;'
EXECUTE IMMEDIATE mysql USING variable_ville;

Dans ton cas, le simple select ... ne justfie effectivement pas son emploi mais je suppose que ton besoin en SQL dynamque concerne l'étape suivante :
Intégrer la liste de colonnes obtenue dans le SQL de l'étape suivante.

Au passage, je préfère
and column_name != 'ETATOBJET'
au
and column_name <> 'ETATOBJET'

Bonne journée

**boutss** · 12/07/2007, 12h16

Dans ton cas, le simple select ... ne justfie effectivement pas son emploi mais je suppose que ton besoin en SQL dynamque concerne l'étape suivante :
Intégrer la liste de colonnes obtenue dans le SQL de l'étape suivante.

C'est exactement ça !

**plaineR** · 12/07/2007, 12h23

Tu peux t'inspirer de ceci :
http://www.developpez.net/forums/sho...51&postcount=5

**laurentschneider** · 12/07/2007, 12h55

Envoyé par boutss

En fait je me suis peut-être un peu avancé.
Je pensais que le execute immédiate permettait d'exécuter une requête sql dynamique.

Au temps pour moi.

Non en fait le résultat retournera plusieurs un grand nombre de ligne et sera stocké dans un fichier texte via spool.

avec execute immediate et into ça me parait tiré par les cheveux, car tu feras into quoi?

par contre je vois bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SQL> var r refcursor
SQL> declare
  2    str varchar2(4000);
  3    first boolean;
  4  begin
  5    str := 'select ';
  6    first := true;
  7    for f in (
  8      select column_name
  9      from user_tab_columns
 10      where table_name='EMP'
 11      and column_name!='EMPNO'
 12      order by column_id)
 13    loop
 14      if first then
 15        first:=false;
 16      else
 17        str:=str||',';
 18      end if;
 19      str:=str||f.column_name;
 20    end loop;
 21    str := str||' from emp';
 22    open :r for str;
 23  end;
 24  /
 
PL/SQL procedure successfully completed.
 
ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
SMITH      CLERK           7902 17-DEC-80        800                    20
ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
WARD       SALESMAN        7698 22-FEB-81       1250        500         30
JONES      MANAGER         7839 02-APR-81       2975                    20
MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
BLAKE      MANAGER         7839 01-MAY-81       2850                    30
CLARK      MANAGER         7839 09-JUN-81       2450                    10
SCOTT      ANALYST         7566 19-APR-87       3000                    20
KING       PRESIDENT            17-NOV-81       5000                    10
TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
ADAMS      CLERK           7788 23-MAY-87       1100                    20
JAMES      CLERK           7698 03-DEC-81        950                    30
FORD       ANALYST         7566 03-DEC-81       3000                    20
MILLER     CLERK           7782 23-JAN-82       1300                    10
 
14 rows selected.

**boutss** · 12/07/2007, 15h02

Merci Laurent,

Et autres d'ailleurs aussi...

Mais l'instruction

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

open :r for str;

n'écrit pas dans le spool comme un select classique.

Dois-je faire une boucle pour écrire dans le spool ?

En même temps le refcursor déclaré avant la partie declare, est-ce une variable globale ?

Merci.

**laurentschneider** · 12/07/2007, 15h10

non, c'est une variable sqlplus (une variable hôte), et c'est le set autoprint qui l'affiche.

Envoyé par boutss

n'écrit pas dans le spool comme un select classique.

Non? pourquoi pas?

Envoyé par boutss

une boucle

oui, tu peux. mais pour récupérer les colonnes dynamiquement il te faudra dbms_sql. Je vais essayer de te poster exemple

**laurentschneider** · 12/07/2007, 15h17

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
set serverout on size 1000000 head off feedback off
alter session set nls_date_format='YYYY-MM-DD';
declare
  str varchar2(4000);
  first boolean;
  curid number;
  desctab dbms_sql.desc_tab;
  colcnt number;
  namevar varchar2(4000);
  tmp clob;
begin
  str := 'select ';
  first := true;
  for f in (
    select column_name 
    from user_tab_columns 
    where table_name='EMP' 
    and column_name!='EMPNO' 
    order by column_id) 
  loop
    if first then 
      first:=false;
    else
      str:=str||',';
    end if;
    str:=str||f.column_name;
  end loop;
  str:=str||' from EMP';
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, str, dbms_sql.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
  FOR i IN 1 .. colcnt LOOP
    DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
  END LOOP;
  if DBMS_SQL.execute(curid) = 0 THEN
    dbms_lob.createtemporary(tmp, true);
	FOR i IN 1 .. colcnt LOOP
	  if (i>1) then
        dbms_lob.writeappend(tmp, 1, ';');
      end if;
      dbms_lob.writeappend(tmp,
        length(desctab(i).col_name),
        desctab(i).col_name);
    END LOOP;
    dbms_output.put_line(tmp);
	dbms_lob.freetemporary(tmp);
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
	  dbms_lob.createtemporary(tmp, true);
	  FOR i IN 1 .. colcnt LOOP
	    if (i>1) then
		  dbms_lob.writeappend(tmp, 1, ';');
        end if;
	    DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
	    if (namevar is not null)
		then
		  dbms_lob.writeappend(tmp, length(namevar),
                    namevar);
		end if;
	  END LOOP;
    dbms_output.put_line(tmp);
	  dbms_lob.freetemporary(tmp);
	END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
ENAME;JOB;MGR;HIREDATE;SAL;COMM;DEPTNO;
SMITH;CLERK;7902;1980-12-17;800;;20;
ALLEN;SALESMAN;7698;1981-02-20;1600;300;30;
WARD;SALESMAN;7698;1981-02-22;1250;500;30;
JONES;MANAGER;7839;1981-04-02;2975;;20;
MARTIN;SALESMAN;7698;1981-09-28;1250;1400;30;
BLAKE;MANAGER;7839;1981-05-01;2850;;30;
CLARK;MANAGER;7839;1981-06-09;2450;;10;
SCOTT;ANALYST;7566;1987-04-19;3000;;20;
KING;PRESIDENT;;1981-11-17;5000;;10;
TURNER;SALESMAN;7698;1981-09-08;1500;0;30;
ADAMS;CLERK;7788;1987-05-23;1100;;20;
JAMES;CLERK;7698;1981-12-03;950;;30;
FORD;ANALYST;7566;1981-12-03;3000;;20;
MILLER;CLERK;7782;1982-01-23;1300;;10;

**laurentschneider** · 12/07/2007, 15h26

pour faire plus simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
set serverout on size 1000000 head off feedback off
alter session set nls_date_format='YYYY-MM-DD';
declare
  str varchar2(4000);
  curid number;
  desctab dbms_sql.desc_tab;
  colcnt number;
  namevar varchar2(4000);
  tmp clob;
begin
  str := 'select * from emp';
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, str, dbms_sql.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
  FOR i IN 1 .. colcnt LOOP
    DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
  END LOOP;
  if DBMS_SQL.execute(curid) = 0 THEN
    dbms_lob.createtemporary(tmp, true);
	FOR i IN 1 .. colcnt LOOP
      if (desctab(i).col_name != 'EMPNO') then
        dbms_lob.writeappend(tmp,
          length(desctab(i).col_name),
          desctab(i).col_name);
        dbms_lob.writeappend(tmp, 1, ';');
      end if;
    END LOOP;
    dbms_output.put_line(tmp);
	dbms_lob.freetemporary(tmp);
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
	  dbms_lob.createtemporary(tmp, true);
	  FOR i IN 1 .. colcnt LOOP
	    DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
        if (desctab(i).col_name != 'EMPNO') then  
	      if (namevar is not null)
		  then
		    dbms_lob.writeappend(tmp, length(namevar), namevar);
		  end if;
          dbms_lob.writeappend(tmp, 1, ';');
        end if;
	  END LOOP;
    dbms_output.put_line(tmp);
	dbms_lob.freetemporary(tmp);
	END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

**laurentschneider** · 12/07/2007, 15h46

encore plus simple, sans clob

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
declare
  str varchar2(4000);
  curid number;
  desctab dbms_sql.desc_tab;
  colcnt number;
  namevar varchar2(4000);
  tmp clob;
begin
  str := 'select * from emp';
  curid := dbms_sql.open_cursor;
  dbms_sql.parse(curid, str, dbms_sql.NATIVE);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
  FOR i IN 1 .. colcnt LOOP
    DBMS_SQL.DEFINE_COLUMN(curid, i, namevar,4000);
  END LOOP;
  if DBMS_SQL.execute(curid) = 0 THEN
	FOR i IN 1 .. colcnt LOOP
	  if (desctab(i).col_name!='EMPNO') then
        dbms_output.put(desctab(i).col_name||';');
      end if;
    END LOOP;
    dbms_output.new_line;
    WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
	  FOR i IN 1 .. colcnt LOOP
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
	    if (desctab(i).col_name!='EMPNO') then
		  dbms_output.put(namevar||';');
		end if;
	  END LOOP;
    dbms_output.new_line;
	END LOOP;
  END IF;
  DBMS_SQL.CLOSE_CURSOR(curid);
end;
/

mais bien sûr, en 9i tu es limité par dbms_output à 1000000 caractères. Tu peux tenter utl_file plutôt...

**boutss** · 12/07/2007, 15h53

non, c'est une variable sqlplus (une variable hôte), et c'est le set autoprint qui l'affiche.

Ca fonctionne très bien avec ça !

**laurentschneider** · 12/07/2007, 15h55

donc je me suis grillé les neuronnes pour rien avec mon dbms_sql ?

**boutss** · 12/07/2007, 15h58

Oui j'ai bien l'impression !

Mais ce n'est pas vain, je vais tout de même étudier ce que tu m'as proposé.
J'ai eu des exceptions avec les DBMS.SQL mais ça doit être du aux contraintes de taille.

En tout cas respect

**boutss** · 12/07/2007, 16h35

J'ai raccourci en faisant un substr pour gérer la dernière virgule :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
var r refcursor
declare
	str varchar2(4000);
begin
str := 'select ';
  for f in (
    select column_name
    from user_tab_columns
    where table_name='TENUM'
    and column_name not in ('ACTEURCREATION', 'DATECREATION', 'ACTEURMODIFICATION', 'DATEMODIFICATION', 'ACTEURSUPPRESSION', 'DATESUPPRESSION')
    order by column_id)
  loop
    str:=str||f.column_name||',';
  end loop;
  str := substr(str, 0, length(str)-1) ||' from TENUM where rownum <= 10';
  open :r for str;
end;
/

SQL dynamique - Récupérer tous les champs sauf certains

SQL Oracle

Discussions similaires

Partager

Partager