Pb requete, reprise PL SQL

Version imprimable

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:

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:

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.

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

Code:

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:

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

12/11/2019, 12h24
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.
12/11/2019, 12h50
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?
12/11/2019, 13h29
Cincinnatus
Citation:

Envoyé par 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?

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

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

Code:

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.
12/11/2019, 13h37
Cvanhove

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

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

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

Citation:

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.

Citation:

Envoyé par Cvanhove

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:

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:

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.

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:

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;

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:

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;

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:

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

13/11/2019, 11h15
Cvanhove

Merci infiniment pour vos retours et l'aide apporter :)