Discussion :

[Cvanhove]

Bonjour,

J'ai un soucis avec ma requête PL SQL (ça fait plusieurs années que j'en avais pas fait et la reprise est difficile!)
J'ai 4 erreurs remontées visiblement dut à un pb de structure (Un Begin mal déclaré peut etre?)

Voici mon code :

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
 
create or replace procedure  DWAUTO_PURGE_ALL_TABLES(NB_MOIS_RETENTION number)
is
maTable varchar2(30 char);
TABLE_NAME varchar2(100 char);
nbEnr integer;
currTable varchar2(30 char);
tbl_exist varchar2(30 char);
 
BEGIN
        select TABLE_NAME into currTable from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
        EXCEPTION WHEN no_data_found THEN
            currTable := ' ';
	--END;
 
	if currTable is null then
		---init avec une chaine vide pour traiter toutes les tables
		insert into DBDWAUTOUSR.TMP_PURGE_ALL_TABLES values (currTable);
	end if;
 
	for maTable in (select distinct substr(TABLE_NAME,1,16) as TABLE_NAME from all_tables where (REGEXP_like(TABLE_NAME,'^M[0-9]{6}_S[0-9]{7}$') or TABLE_NAME='MSG') AND OWNER = 'DBDWAUTOUSR' AND TABLE_NAME > currTable order by 1) loop
 
		select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
		if tbl_exist = 1 then -- Si la table TMP existe alors...
			select count(*) into cnt from maTable.TABLE_NAME;
			if cnt != 0 then -- Si la table source n'est pas vide, on Drop la table TMP
				execute immediate 'drop table DBDWAUTOUSR.TAB_TMP';
			end if; -- Sinon on skip le Drop de la table TMP et on passe à la suite
		end if;
 
		dbms_output.put_line( 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1');
		execute immediate 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1';
		execute immediate 'truncate table DBDWAUTOUSR.' || maTable.TABLE_NAME ;
		execute immediate 'insert /*+append */ into DBDWAUTOUSR. ' || maTable.TABLE_NAME || ' select * from DBDWAUTOUSR.TAB_TMP' ;
		execute immediate 'drop table DBDWAUTOUSR.TAB_TMP purge' ;
		update DBDWAUTOUSR.TMP_PURGE_ALL_TABLES set TABLE_NAME = maTable.TABLE_NAME;
	end loop;
 
	select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
	if tbl_exist = 1 then -- Si la table TMP existe alors...
		delete from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
	end if;
 
END DWAUTO_PURGE_ALL_TABLES;

Et voici mes erreurs :

Code :

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

1
2
3
4
5
 
Erreur(24,4): PL/SQL: SQL Statement ignored
Erreur(24,42): PL/SQL: ORA-00942: Table ou vue inexistante
Erreur(25,4): PL/SQL: Statement ignored
Erreur(25,7): PLS-00201: identifier 'CNT' must be declared

J'ai d'abord pensé à une ligne non fermé par un ";" mais visiblement c'est pas ça...
Si quelqu'un à des pistes ou voit une erreur je suis preneur !

Merci d'avance pour votre aide.

[McM]

Erreur(25,7): PLS-00201: identifier 'CNT' must be declared

Code :

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

SELECT COUNT(*) INTO cnt

La variable cnt n'est pas déclaré


Ensuite, c'est bizarre ce code, mais c'est peut être voulu

Code :

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

1
2
3
4
5
6
7
8
9
10
BEGIN
        SELECT TABLE_NAME INTO currTable FROM DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
EXCEPTION WHEN no_data_found THEN
            currTable := ' ';
	--END;
 
	IF currTable IS NULL THEN
		---init avec une chaine vide pour traiter toutes les tables
		INSERT INTO DBDWAUTOUSR.TMP_PURGE_ALL_TABLES VALUES (currTable);
	END IF;

Ca veut dire que tout le traitement ne se fait que pour l'exception NO_DATA_FOUND, et le currTable ne sera donc jamais NULL, mais vaudra toujours ' '

[al1_24]

L'erreur identifiée à la ligne 25 est claire :

• La variable cnt n'a pas été déclarée au début du bloc de code.

Celle de la ligne précédent l'est tout autant :

• La table maTable.TABLE_NAME n'existe pas.

[Cvanhove]

Effectivement pour le cnt, je l'avais oublié, par contre le maTable.TABLE_NAME est bien déclaré au début. Est-ce un warning qui n'a pas lieu d'être?

[Cincinnatus]

Effectivement pour le cnt, je l'avais oublié, par contre le maTable.TABLE_NAME est bien déclaré au début. Est-ce un warning qui n'a pas lieu d'être?

maTable et TABLE_NAME sont des variables déclarées au début, en effet :

Code :

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

1
2
3
maTable varchar2(30 char); TABLE_NAME varchar2(100char);

Mais

Code :

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

select count(*)into cnt from maTable.TABLE_NAME;

ne peut pas marcher comme ça. En execute immediate, ça pourrait mieux marcher, comme tu le fais pour d'autres instructions.

[Cvanhove]

D'accord mais j'ai un doute, passer cette ligne en execute immediate me permet quand même d'alimenter ma variable cnt?

[Cvanhove]

Bon j'ai passé ma ligne en execute immediate comme conseiller et j'arrive à compiler, ce qui me donne ceci :

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
 
create or replace procedure  DWAUTO_PURGE_ALL_TABLES(NB_MOIS_RETENTION number)
is
maTable varchar2(30 char);
TABLE_NAME varchar2(100 char);
nbEnr integer;
currTable varchar2(30 char);
tbl_exist varchar2(30 char);
cnt integer;
 
BEGIN
        select TABLE_NAME into currTable from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
        EXCEPTION WHEN no_data_found THEN
            currTable := ' ';
 
	if currTable is null then
		---init avec une chaine vide pour traiter toutes les tables
		insert into DBDWAUTOUSR.TMP_PURGE_ALL_TABLES values (currTable);
	end if;
 
	for maTable in (select distinct substr(TABLE_NAME,1,16) as TABLE_NAME from all_tables where (REGEXP_like(TABLE_NAME,'^M[0-9]{6}_S[0-9]{7}$') or TABLE_NAME='MSG') AND OWNER = 'DBDWAUTOUSR' AND TABLE_NAME > currTable order by 1) loop
 
		select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
		if tbl_exist = 1 then -- Si la table TMP existe alors...
			execute immediate 'select count(*) into cnt from DBDWAUTOUSR.' || maTable.TABLE_NAME;
			if cnt != 0 then -- Si la table source n'est pas vide, on Drop la table TMP
				execute immediate 'drop table DBDWAUTOUSR.TAB_TMP';
			end if; -- Sinon on skip le Drop de la table TMP et on passe à la suite
		end if;
 
		dbms_output.put_line( 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1');
		execute immediate 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1';
		execute immediate 'truncate table DBDWAUTOUSR.' || maTable.TABLE_NAME ;
		execute immediate 'insert /*+append */ into DBDWAUTOUSR. ' || maTable.TABLE_NAME || ' select * from DBDWAUTOUSR.TAB_TMP' ;
		execute immediate 'drop table DBDWAUTOUSR.TAB_TMP purge' ;
		update DBDWAUTOUSR.TMP_PURGE_ALL_TABLES set TABLE_NAME = maTable.TABLE_NAME;
	end loop;
 
	select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
	if tbl_exist = 1 then -- Si la table TMP existe alors...
		delete from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
	end if;
 
END DWAUTO_PURGE_ALL_TABLES;

Par contre quelques chose m'interpelle, quand McM disait

Ca veut dire que tout le traitement ne se fait que pour l'exception NO_DATA_FOUND, et le currTable ne sera donc jamais NULL, mais vaudra toujours ' '

Qu'est ce que ça veut dire? Si ma requete à la ligne 11 retourne des valeurs, le traitement va bien s’exécuter non?
J'ai oublié de préciser que ce n'est pas entièrement de moi. Je reprends le travail d'un traitement de purge d'un ancien collègue.
De ce qu'on m'a dit il avait fait en sorte que la variable currTable ne soit jamais null car lorsue c'était le cas le traitement plantait pour X raison.

[Cincinnatus]

D'accord mais j'ai un doute, passer cette ligne en execute immediate me permet quand même d'alimenter ma variable cnt?

C'est possible : d'après les exemples de chez Oracle : https://docs.oracle.com/cd/B12037_01...3_elems017.htm

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
DECLARE   sql_stmt    VARCHAR2(200);
   plsql_block VARCHAR2(500);
   emp_id      NUMBER(4) := 7566;
   salary      NUMBER(7,2);
   dept_id     NUMBER(2) := 50;
   dept_name   VARCHAR2(14) := 'PERSONNEL';
   location    VARCHAR2(13) := 'DALLAS';
   emp_rec     emp%ROWTYPE;
BEGIN
   EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)';
   sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)';
   EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location;
   sql_stmt := 'SELECT * FROM emp WHERE empno = :id';
   EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id;
   plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;';
   EXECUTE IMMEDIATE plsql_block USING 7788, 500;
   sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1
      RETURNING sal INTO :2';
   EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary;
   EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num'
      USING dept_id;
   EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; END;

ça pourrait donner :

Code :

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

execute immediate 'select count(*) from DBDWAUTOUSR.' || maTable || '.' || TABLE_NAME ||''' into cnt ;

Ou d'autres versions avec les paramètres dans la requête, en utilisant le USING, à voir.

[Scriuiw]

Ce dont fait référence McM c'est que :
- La grande partie de votre code se trouve dans le bloc d'exception NO_DATA_FOUND. Donc si une donnée est trouvée dans DBDWAUTOUSR.TMP_PURGE_ALL_TABLES, aucune instruction ne sera exécutée car cela mène à la fin de votre procédure
- Si aucune donnée n'est trouvée, la variable currTable est forcée à blanc puis un test est réalisé pour savoir si elle est NULL ce qui ne sera jamais le cas


Voici votre code indenté de façon plus lisible

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
create or replace procedure  DWAUTO_PURGE_ALL_TABLES(NB_MOIS_RETENTION number)
is
  maTable varchar2(30 char);
  TABLE_NAME varchar2(100 char);
  nbEnr integer;
  currTable varchar2(30 char);
  tbl_exist varchar2(30 char);
  cnt integer;
 
BEGIN
  select TABLE_NAME into currTable from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
EXCEPTION 
  WHEN no_data_found THEN
    currTable := ' ';
    if currTable is null then
      ---init avec une chaine vide pour traiter toutes les tables
      insert into DBDWAUTOUSR.TMP_PURGE_ALL_TABLES values (currTable);
    end if;
 
    for maTable in (select distinct substr(TABLE_NAME,1,16) as TABLE_NAME from all_tables where (REGEXP_like(TABLE_NAME,'^M[0-9]{6}_S[0-9]{7}$') or TABLE_NAME='MSG') AND OWNER = 'DBDWAUTOUSR' AND TABLE_NAME > currTable order by 1) loop
 
      select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
      if tbl_exist = 1 then -- Si la table TMP existe alors...
        execute immediate 'select count(*) into cnt from DBDWAUTOUSR.' || maTable.TABLE_NAME;
        if cnt != 0 then -- Si la table source n'est pas vide, on Drop la table TMP
          execute immediate 'drop table DBDWAUTOUSR.TAB_TMP';
        end if; -- Sinon on skip le Drop de la table TMP et on passe à la suite
      end if;
 
      dbms_output.put_line( 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1');
      execute immediate 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1';
      execute immediate 'truncate table DBDWAUTOUSR.' || maTable.TABLE_NAME ;
      execute immediate 'insert /*+append */ into DBDWAUTOUSR. ' || maTable.TABLE_NAME || ' select * from DBDWAUTOUSR.TAB_TMP' ;
      execute immediate 'drop table DBDWAUTOUSR.TAB_TMP purge' ;
      update DBDWAUTOUSR.TMP_PURGE_ALL_TABLES set TABLE_NAME = maTable.TABLE_NAME;
    end loop;
 
    select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
    if tbl_exist = 1 then -- Si la table TMP existe alors...
      delete from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
    end if;
END DWAUTO_PURGE_ALL_TABLES;

[Cvanhove]

Effectivement je comprend mieux, j'ai vraiment du mal à lire le pl sql et je m'en excuse.

J'ai pris note de vos remarques et voici enfin ce que donne la procédure (ce qui devrait être plus logique) :

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
 
create or replace procedure  DWAUTO_PURGE_ALL_TABLES(NB_MOIS_RETENTION number)
is
maTable varchar2(30 char);
TABLE_NAME varchar2(100 char);
nbEnr integer;
currTable varchar2(30 char);
tbl_exist varchar2(30 char);
cnt integer;
sql_stmt    VARCHAR2(200);
 
BEGIN
    BEGIN
    select TABLE_NAME into currTable from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
    EXCEPTION WHEN no_data_found THEN
        currTable := ' ';
    END;    
    BEGIN
        if currTable is null then
            ---init avec une chaine vide pour traiter toutes les tables
            insert into DBDWAUTOUSR.TMP_PURGE_ALL_TABLES values (currTable);
        end if;
 
        for maTable in (select distinct substr(TABLE_NAME,1,16) as TABLE_NAME from all_tables where (REGEXP_like(TABLE_NAME,'^M[0-9]{6}_S[0-9]{7}$') or TABLE_NAME='MSG') AND OWNER = 'DBDWAUTOUSR' AND TABLE_NAME > currTable order by 1) loop
 
            select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
            if tbl_exist = 1 then -- Si la table TMP existe alors...
                sql_stmt := 'select count(*) from DBDWAUTOUSR.' || maTable.TABLE_NAME;
                execute immediate sql_stmt into cnt ;
                if cnt != 0 then -- Si la table source n'est pas vide, on Drop la table TMP
                    execute immediate 'drop table DBDWAUTOUSR.TAB_TMP';
                end if; -- Sinon on skip le Drop de la table TMP et on passe à la suite
            end if;
 
            dbms_output.put_line( 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1');
            execute immediate 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.' || maTable.TABLE_NAME || ' WHERE date_rec >= add_months(sysdate, - ' || NB_MOIS_RETENTION || ')-1';
            execute immediate 'truncate table DBDWAUTOUSR.' || maTable.TABLE_NAME ;
            execute immediate 'insert /*+append */ into DBDWAUTOUSR. ' || maTable.TABLE_NAME || ' select * from DBDWAUTOUSR.TAB_TMP' ;
            execute immediate 'drop table DBDWAUTOUSR.TAB_TMP purge' ;
            update DBDWAUTOUSR.TMP_PURGE_ALL_TABLES set TABLE_NAME = maTable.TABLE_NAME;
        end loop;
 
        select count(*) into tbl_exist from all_tables where TABLE_NAME = 'TAB_TMP' and OWNER = 'DBDWAUTOUSR';
        if tbl_exist = 1 then -- Si la table TMP existe alors...
            delete from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
        end if;
    END;
END DWAUTO_PURGE_ALL_TABLES;

[Waldar]

Il reste quelques problèmes mineurs de typages de données, et j'aime bien préfixer les variables, paramètres et curseurs par respectivement v$, p$ et c$ de manière à les reconnaître directement.
Enfin un dernier effort d'indentation rend le code, à mon sens, plus lisible :

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
68
69
70
71
72
73
74
75
76
77
78
79
create or replace procedure DWAUTO_PURGE_ALL_TABLES (p$_NB_MOIS_RETENTION number)
is
    v$_currTable                     varchar2( 30 char);
    v$_tbl_exist                     simple_integer := 0;
    v$_cnt                           simple_integer := 0;
    v$_sql_stmt                      varchar2(200);
    v$_NB_MOIS_RETENTION    constant varchar2(  3) := to_char(p$_NB_MOIS_RETENTION, 'fm990');
 
begin
    begin
        select TABLE_NAME
          into v$_currTable
          from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
 
    exception when no_data_found
        then v$_currTable := ' ';
    end;
 
    begin
        if v$_currTable is null then
            ---init avec une chaine vide pour traiter toutes les tables
            insert into DBDWAUTOUSR.TMP_PURGE_ALL_TABLES (TABLE_NAME) values (null);
        end if;
 
        for c$_maTable in
        (
          select distinct
                 substr(TABLE_NAME, 1, 16) as TABLE_NAME
            from ALL_TABLES
           where (TABLE_NAME = 'MSG' or regexp_like(TABLE_NAME,'^M[0-9]{6}_S[0-9]{7}$'))
             and OWNER       = 'DBDWAUTOUSR'
             and TABLE_NAME  > v$_currTable
        order by 1
        )
        loop
 
            select count(*)
              into v$_tbl_exist
              from ALL_TABLES
             where TABLE_NAME = 'TAB_TMP'
               and OWNER      = 'DBDWAUTOUSR';
 
            if v$_tbl_exist = 1 then -- Si la table TMP existe alors...
                v$_sql_stmt := 'select count(*) from DBDWAUTOUSR.' || c$_maTable.TABLE_NAME;
                execute immediate v$_sql_stmt into v$_cnt;
 
                if v$_cnt > 0 then -- Si la table source n'est pas vide, on Drop la table TMP
                    execute immediate 'drop table DBDWAUTOUSR.TAB_TMP';
                end if; -- Sinon on skip le Drop de la table TMP et on passe à la suite
            end if;
 
            v$_sql_stmt := 'create table DBDWAUTOUSR.TAB_TMP as select * from DBDWAUTOUSR.'
                        || c$_maTable.TABLE_NAME
                        || ' where date_rec >= add_months(sysdate, -' || v$_NB_MOIS_RETENTION || ')-1';
            dbms_output.put_line(v$_sql_stmt);
            execute immediate v$_sql_stmt;
 
            execute immediate 'truncate table DBDWAUTOUSR.' || c$_maTable.TABLE_NAME;
            execute immediate 'insert /*+ append */ into DBDWAUTOUSR. ' || c$_maTable.TABLE_NAME || ' select * from DBDWAUTOUSR.TAB_TMP';
            execute immediate 'drop table DBDWAUTOUSR.TAB_TMP purge';
 
            update DBDWAUTOUSR.TMP_PURGE_ALL_TABLES
               set TABLE_NAME = c$_maTable.TABLE_NAME;
 
        end loop;
 
        select count(*)
          into v$_tbl_exist
          from ALL_TABLES
         where TABLE_NAME = 'TAB_TMP'
           and OWNER      = 'DBDWAUTOUSR';
 
        if v$_tbl_exist = 1 then -- Si la table TMP existe alors...
            delete from DBDWAUTOUSR.TMP_PURGE_ALL_TABLES;
        end if;
    end;
 
end DWAUTO_PURGE_ALL_TABLES;
/

[Cvanhove]

Merci infiniment pour vos retours et l'aide apporter