[Débutant][8i]PLS-00320, PLS-00201

**Magnus** · 16/12/2005, 10h18

Bonjour à tou(te)s,

Je débute en PL/SQL et je veux écrire une fonction qui récupère automatiquement dans une chaîne la concaténation des colonnes d'une table fournie en paramètre.
Exemple : soit la table A ayant 2 colonnes X et Y alors le résultat de l'appel de cette fonction à laquelle on fournit le paramètre 'A' est : (X, Y)

Voici l'erreur que j'obtiens quand j'essaye de la déclarer sous sqlplus :

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
  1  CREATE OR REPLACE FUNCTION listColumns (PCname_table VARCHAR2, PCowner VARCHAR2) RETURN VARCHAR
  2    LClist_columns VARCHAR2(1000);
  3    LCcolumn_fetched VARCHAR2(50);
  4    CURSOR LCuTable IS
  5    SELECT COLUMN_NAME
  6    FROM DBA_TAB_COLUMNS
  7    WHERE UPPER(OWNER) = PCowner
  8    AND TABLE_NAME = PCname_table
  9    ORDER BY COLUMN_ID;
 10  BEGIN
 11    LClist_columns := '';
 12    OPEN LCuTable;
 13      LOOP
 14        FETCH LCuTable INTO LCcolumn_fetched;
 15        EXIT WHEN LCuTable%NOTFOUND;
 16        LClist_columns := LClist_columns || LCcolumn_fetched || ', ';
 17      END LOOP;
 18      LClist_columns := '(' || SUBSTR( LClist_columns, 1, LENGTH(LClist_columns) ) || ')';
 19    CLOSE LCuTable;
 20    RETURN LClist_columns;
 21* END;
SQL> /
 
Attention : Fonction créée avec erreurs de compilation.
 
SQL> show error
Erreurs pour FUNCTION LISTCOLUMNS :
 
LINE/COL ERROR
-------- -----------------------------------------------------------------
5/3      PL/SQL: SQL Statement ignored
5/10     PLS-00320: the declaration of the type of this expression is
         incomplete or malformed
 
6/8      PLS-00201: identifier 'SYS.DBA_TAB_COLUMNS' must be declared
14/7     PL/SQL: SQL Statement ignored

Pour information si j'utilise un "bloc anonyme" (sorry si j'emploie un mot à la place d'un autre, je suis débutant

) alors l'exécution se déroule sans souci et me retourne la chaine que je demande.

Merci du moindre coup de main.

**plaineR** · 16/12/2005, 10h36

Tu n'as pas les droits sur la table dba_tab_columns ou du moins ils ne t'ont pas été accordé directement.

En PL/SQL, comme en SQL dynamique, Oracle ne tient pas compte des droits accordés pas des rôles. Il faut qu'ils t'aient été donné directement via un grant.

**Magnus** · 16/12/2005, 11h58

Salut plaineR,

Suite à ton post j'ai essayé de faire ça une fois connecté en tant que SYS :

Code :

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

1
2
3
4
GRANT ALL PRIVILEGES ON TABLE DBA_TAB_COLUMNS TO CYRIL
                        *
ERROR à la ligne 1 :
ORA-00903: invalid table name

Pour information je ne travaille qu'avec des synonymes, est-ce que cela peut avoir un rapport avec mon problème originel ?

**Jaouad** · 16/12/2005, 12h03

Avec ton user essaye plutôt la requête sur user_tab_columns

**Magnus** · 16/12/2005, 12h48

Jaouad >> Ton idée me plaisait mais :
1/ cette table ne possède pas la colonne OWNER qui importe dans mon exemple précis (bien sûr ça aurait trop facile sinon)
2/ comme je passe par des synonymes, voilà le problème que l'utilisation de cette table induit :

Code :

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

1
2
3
4
SQL> select distinct table_name
  2  from user_tab_columns;
 
aucune ligne sélectionnée

**plaineR** · 16/12/2005, 13h54

Essaie :

Code :

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

grant select on dba_tab_columns to <ton user>;

**Jaouad** · 16/12/2005, 14h30

Magnus je n'avais pas vu cela ,

Alors dans ce cas la all_tab_columns posséde cette colonne

Bon courage

**Jaouad** · 16/12/2005, 14h36

Envoyé par plaineR

Essaie :

Code :

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

grant select on dba_tab_columns to <ton user>;

il faut éviter de donner ce genre de droits a des users non DBA

**plaineR** · 16/12/2005, 14h43

Envoyé par Jaouad

Envoyé par plaineR

Essaie :

Code :

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

grant select on dba_tab_columns to <ton user>;

il faut éviter de donner ce genre de droits a des users non DBA

Je suis d'accord avec toi, mais si le user peut y accéder via un ordre SQL, il n'y a pas de raison qu'il n'y ait pas droit en PL.
Et puis rien dans son post n'indique que le user n'est pas DBA.

**Jaouad** · 16/12/2005, 14h48

Envoyé par plaineR

Je suis d'accord avec toi, mais si le user peut y accéder via un ordre SQL, il n'y a pas de raison qu'il n'y ait pas droit en PL.
Et puis rien dans son post n'indique que le user n'est pas DBA.

Si son user avait le rôle DBA il pourrait lire la table dba_tab_columns .

**Magnus** · 16/12/2005, 14h52

Envoyé par Jaouad

la all_tab_columns posséde cette colonne

Excellent : grâce à cette table, je n'ai plus d'erreurs et je n'ai même pas besoin d'adapter ma fonction.

Envoyé par plaineR

Essaie :

Code :

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

grant select on dba_tab_columns to <ton user>;

Résultat :

Code :

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

1
2
3
4
5
6
SQL> grant select on dba_tab_columns to cyril
  2  ;
grant select on dba_tab_columns to cyril
                *
ERROR à la ligne 1 :
ORA-01031: insufficient privileges

Encore une chose : quel mécanisme bloquait l'utilisation de DBA_TAB_COLUMNS alors que si j'exécute une requête sur cette table depuis sqlplus elle passe sans souci ?

Un grand merci à vous 2.

**plaineR** · 16/12/2005, 14h54

Pas en PL justement, si les droits DBA ont été donné via un rôle.

**Magnus** · 16/12/2005, 14h54

Envoyé par plaineR

Je suis d'accord avec toi, mais si le user peut y accéder via un ordre SQL, il n'y a pas de raison qu'il n'y ait pas droit en PL.
Et puis rien dans son post n'indique que le user n'est pas DBA.

Je confirme que je peux accéder à cette table depuis sqlplus et d'autre part mes collègues m'ont dit que le user CYRIL est DBA (mais je ne vous en ai pas parlé parce que je ne sais pas comment vérifier leur affirmation).

**Jaouad** · 16/12/2005, 14h58

A vérifier ici :

DBA_ROLE_PRIVS

**Jaouad** · 16/12/2005, 15h00

Envoyé par plaineR

Pas en PL justement, si les droits DBA ont été donné via un rôle.

Pas pour les objets du dictionnaire :

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
 
SQL> create user dvp identified by dvp ; 
 
Utilisateur créé.
 
SQL>  grant dba to dvp ;
 
Autorisation de privilèges (GRANT) acceptée.
 
SQL> conn dvp/dvp
Connecté.
SQL> select count (*) from dba_tab_columns ; 
 
  COUNT(*)
----------
    299366
 
SQL> set serveroutput on 
SQL> declare
  2  a number ; 
  3  begin 
  4   select count (*) into a from dba_tab_columns ; 
  5   dbms_output.put_line (a) ; 
  6  end ; 
  7  /
299366
 
Procédure PL/SQL terminée avec succès.

**plaineR** · 16/12/2005, 15h00

Envoyé par Magnus

Résultat :

Code :

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

1
2
3
4
5
6
SQL> grant select on dba_tab_columns to cyril
  2  ;
grant select on dba_tab_columns to cyril
                *
ERROR à la ligne 1 :
ORA-01031: insufficient privileges

Il faut le faire sous le user sys.

Envoyé par Magnus

Encore une chose : quel mécanisme bloquait l'utilisation de DBA_TAB_COLUMNS alors que si j'exécute une requête sur cette table depuis sqlplus elle passe sans souci ?

Envoyé par plaineR

En PL/SQL, comme en SQL dynamique, Oracle ne tient pas compte des droits accordés pas des rôles. Il faut qu'ils t'aient été donné directement via un grant.