Fonction avec état invalide

Version imprimable

Bonjour,

Je ne suis pas un habitué d'Oracle, et je ne comprends pas pourquoi ma fonction ne fonctionne pas :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE FUNCTION uppercase_fields_name()
BEGIN
  FOR r IN ( SELECT owner, table_name, column_name
             FROM   ALL_TAB_COLUMNS
             WHERE  owner IN ( 'URBA' )
             AND    column_name <> UPPER( column_name )
           )
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE "' || r.owner || '"."' || r.table_name
            || '" RENAME COLUMN "' || r.column_name || '" TO ' || r.column_name;
  END LOOP;
END;
/

Quand je souhaite l''exécuter :

Code:

select uppercase_fields_name() FROM DUAL;

J'ai une erreur :

Code:

1
2
3
4
5
Error: ORA-06575: Package or function UPPERCASE_FIELDS_NAME is in an invalid state
 
SQLState:  65000
ErrorCode: 6575
Position: 40

D'où cela peut venir ?

Merci.

Hello,
elle est en statut invalide car il y a une erreur de syntaxe : ou est le return ?
Que va valoir ce return code ? Il faut lui affecter une valeur.
Sinon il faut écrire une procédure à la place d'une fonction.

Le code sans erreur (mais sans return code) est :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE OR REPLACE FUNCTION uppercase_fields_name
return number
is
BEGIN
  FOR r IN ( SELECT owner, table_name, column_name
             FROM   ALL_TAB_COLUMNS
             WHERE  owner IN ( 'URBA' )
             AND    column_name <> UPPER( column_name )
           )
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE "' || r.owner || '"."' || r.table_name
            || '" RENAME COLUMN "' || r.column_name || '" TO ' || r.column_name;
  END LOOP;
END;
/

Ok, merci. Le retour m'importe peu, du moment que la fonction/procédure est exécutée.

Par contre, quand j'exécute votre code, j'ai une erreur :
Code:

1 2 3 4 5 6 Error: ORA-14552: cannot perform a DDL, commit or rollback inside a query or DML ORA-06512: at "SYSTEM.UPPERCASE_FIELDS_NAME", line 10 SQLState: 72000 ErrorCode: 14552 Position: 40

Une fonction ne peut réaliser que des requêtes. Pour votre opération, il faut passer par une procédure.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
CREATE OR REPLACE PROCEDURE uppercase_fields_name
is
BEGIN
  FOR r IN ( SELECT owner, table_name, column_name
             FROM   ALL_TAB_COLUMNS
             WHERE  owner IN ( 'URBA' )
             AND    column_name <> UPPER( column_name )
           )
  LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE "' || r.owner || '"."' || r.table_name
            || '" RENAME COLUMN "' || r.column_name || '" TO ' || UPPER(r.column_name);
  END LOOP;
END;
/

Au passage, j'ai rajouté un UPPER après le 'TO'.

Et l'appel devient alors :

Code:

1
2
3
4
 
BEGIN
  UPPERCASE_FIELDS_NAME();
END;

29/11/2016, 09h58
Cincinnatus
Pour info, après modification d'une table, par exemple, je vérifie que les dépendances (packages, triggers) sont valides avec cette requête :
Code:

1 2 3 4 SELECT * FROM user_objects WHERE status = 'INVALID' ORDER BY object_type, object_name
29/11/2016, 10h40
batchi
Merci Cincinnatus.

La procédure est bien "valid", mais quand je l'appel avec
Code:

1 2 3 4 BEGIN UPPERCASE_FIELDS_NAME(); END; /
J'ai un message d'erreur :
Code:

1 2 3 4 5 6 7 Error: ORA-01031: insufficient privileges ORA-06512: at "SYSTEM.UPPERCASE_FIELDS_NAME", line 10 ORA-06512: at line 2 SQLState: 42000 ErrorCode: 1031 Position: 39
Hors je suis connecté en "system" et a priori, j'ai tous les droits :/

EDIT : C'est ok. Je me suis crée un utilisateur ! Ça fonctionne ! Merci tout le monde.