Erreur SP2-0552: Variable non déclarée
Bonjour,
Je viens de mettre au point un script SQL ci-dessous mais il m'affiche une erreur d'entrée de jeu.
Je passes en paramètre un schéma Oracle.
Merci d'avance si vous avez une idée !
Message d'erreur
Connecte a :
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ancien 6 : SELECT UPPER ('&1') INTO: schema_bdd FROM dual;
nouveau 6 : SELECT UPPER ('MONSCHEMA') INTO: schema_bdd FROM dual;
SP2-0552: Variable attachee "SCHEMA_BDD" non declaree.
Deconnecte de Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
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
|
DECLARE
schema_bdd VARCHAR2(25);
BEGIN
SELECT UPPER ('&1') INTO: schema_bdd FROM dual;
FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND (table_name LIKE 'BATCH_%' OR table_name LIKE 'TMP_%'))
LOOP
dbms_stats.delete_table_stats(schema_bdd,cur.table_name);
dbms_stats.lock_table_stats(schema_bdd,cur.table_name);
END LOOP;
FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND (NOT table_name LIKE 'BATCH_%' OR table_name LIKE 'TMP_%'))
LOOP
dbms_stats.unlock_table_stats(schema_bdd,cur.table_name);
END LOOP;
FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd AND table_name LIKE 'BATCH_%')
LOOP
EXECUTE IMMEDIATE 'ALTER TABLE ' || schema_bdd || '.' || cur.table_name || 'NOLOGGING';
END LOOP;
FOR cur IN (SELECT table_name FROM dba_tables WHERE owner=schema_bdd)
LOOP
dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'METHOD_OPT',pvalue=> 'FOR ALL COLUMNS SIZE SKEWONLY');
dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'ESTIMATE_PERCENT',pvalue=> 'DBMS_STATS.AUTO_SAMPLE_SIZE');
dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'NO_INVALIDATE',pvalue => 'FALSE');
dbms_stats.set_table_prefs(ownname => schema_bdd,tabname => cur,pname => 'CASCADE',pvalue=> 'TRUE');
END LOOP;
FOR cur IN (SELECT sequence_name FROM dba_sequences WHERE sequence_owner=schema_bdd and cache_size !='20')
LOOP
EXECUTE IMMEDIATE 'ALTER SEQUENCE ' || schema_bdd || '.' || cur.sequence_name || ' cache 20';
END LOOP;
FOR cur IN (SELECT object_name, object_type, owner FROM sys.all_objects WHERE status = 'INVALID' )
LOOP
BEGIN
IF cur.object_type = 'PACKAGE BODY' THEN
EXECUTE IMMEDIATE 'ALTER ' || cur.object_type || ' "' || schema_bdd || '"."' || cur.object_name || '" COMPILE BODY';
ELSE
EXECUTE IMMEDIATE 'ALTER ' || cur.object_type || ' "' || schema_bdd || '"."' || cur.object_name || '" COMPILE';
END IF;
EXCEPTION
WHEN OTHERS THEN NULL;
END;
END LOOP;
END;
/
EXIT; |
