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

Version imprimable

12/07/2007, 09h48
boutss

SQL dynamique - Récupérer tous les champs sauf certains
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:

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

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

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

12/07/2007, 11h47
pifor

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.
12/07/2007, 11h51
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.
12/07/2007, 12h14
Bruno2r

Avantages de EXECUTE IMMEDIATE

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
12/07/2007, 12h16
boutss

Citation:

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 !
12/07/2007, 12h23
plaineR

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

Citation:

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:

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.

12/07/2007, 15h02
boutss

Merci Laurent,

Et autres d'ailleurs aussi...

Mais l'instruction

Code:

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.
12/07/2007, 15h10
laurentschneider

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

Citation:

Envoyé par boutss

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

Non? pourquoi pas?

Citation:

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

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
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:

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;

pour faire plus simple

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

encore plus simple, sans clob

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
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...

12/07/2007, 15h53
boutss

Citation:

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 ! :king:
12/07/2007, 15h55
laurentschneider

donc je me suis grillé les neuronnes pour rien avec mon dbms_sql ?
:lefou:
12/07/2007, 15h58
boutss

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 :merci:

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

Code:

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