IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

[Oracle 10G] Dbms_sql et les tableaux


Sujet :

PL/SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Membre chevronné

    Profil pro
    Inscrit en
    Septembre 2004
    Messages
    507
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2004
    Messages : 507
    Par défaut [Oracle 10G] Dbms_sql et les tableaux
    Bonjour.
    Je suis en train de "dynamiser" une procédure et je suis un peu embêté.
    Pour faire simple, à partir d'une table en paramètre, je construis une requête et je veux ensuite mettre le résultat de cette requête dans un tableau (au pire dans des variables que je traiterai au fur et à mesure). Mais bien évidemment, je ne sais pas à l'avance combien de lignes ni de colonnes va me retourner la requête.
    Après une journée de recherche, j'ai presque trouvé la solution mais je bloque sur la dernière étape.

    Voici un script qui qui se rapproche de ce que je veux.
    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
    declare
      c       NUMBER;
      d       NUMBER;
      n_tab   DBMS_SQL.NUMBER_TABLE;
      indx    NUMBER := -10;
    BEGIN
      c := DBMS_SQL.OPEN_CURSOR;
      dBMS_SQL.PARSE(c, 'select n from t order by 1', DBMS_SQL.NATIVE);
     
      DBMS_SQL.DEFINE_ARRAY(c, 1, n_tab, 10, indx);
     
      d := DBMS_SQL.EXECUTE(c);
      loop
        d := DBMS_SQL.FETCH_ROWS(c);
     
        DBMS_SQL.COLUMN_VALUE(c, 1, n_tab);
     
        EXIT WHEN d != 10;
      END LOOP;
     
      DBMS_SQL.CLOSE_CURSOR(c);
     
      EXCEPTION WHEN OTHERS THEN
        IF DBMS_SQL.IS_OPEN(c) THEN
          DBMS_SQL.CLOSE_CURSOR(c);
        END IF;
        RAISE;
    END;
    /
    La "petite" différence est qu'au lieu d'avoir
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    'select n from t order by 1'
    j'ai une requête du genre:
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
     'select t1.n1, t2.n2 from table1 t1, table2 t2 where t1.n3=t2.n3'
    Et je n'arrive pas à gérer un tableau.
    (NB: dans cet exemple, je ne comprends pas bien la gestion de 'indx' )

    Merci de m'aider à résoudre ce pb dont la solution m'intéresse beaucoup.

  2. #2
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    EXECUTE IMMEDIATE et BULK COLLECT c'est pas plus simple ?

  3. #3
    Membre chevronné

    Profil pro
    Inscrit en
    Septembre 2004
    Messages
    507
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2004
    Messages : 507
    Par défaut
    Citation Envoyé par orafrance Voir le message
    EXECUTE IMMEDIATE et BULK COLLECT c'est pas plus simple ?
    Je l'utilise déjà une fois pour récupérer les noms et types des colonnes (en allant lire dans all_tab_colums).
    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
          TYPE typ_colnoms IS TABLE OF VARCHAR2 (30);
     
          TYPE typ_coltypes IS TABLE OF VARCHAR2 (106);
     
          t_colnoms         typ_colnoms;
          t_coltypes        typ_coltypes;
    BEGIN
          ls_req_colonnes :=
                'SELECT column_name, data_type FROM all_tab_columns '
             || 'WHERE table_name = upper('''
             || p_table
             || ''')';
          OPEN c_colonnes FOR (ls_req_colonnes);
     
          FETCH c_colonnes
          BULK COLLECT INTO t_colnoms, t_coltypes;
     
          CLOSE c_colonnes;
    Mais dans le cas présent, que mets-je derrière le INTO? Je ne connais pas à l'avance le nombre de colonnes, donc je ne peux pas définir un tableau.
    Tout le pb est dans la définition et l'utilisation d'un tableau à deux dimensions.

    Je ne vois pas ici comment faire, mais ce serait plus simple, bien sûr (ceci dit, cela m'intéresse de savoir utiliser dbms_sql ).

  4. #4
    Expert éminent
    Avatar de orafrance
    Profil pro
    Inscrit en
    Janvier 2004
    Messages
    15 967
    Détails du profil
    Informations personnelles :
    Âge : 48
    Localisation : France

    Informations forums :
    Inscription : Janvier 2004
    Messages : 15 967
    Par défaut
    ha oui... en effet... il me semble qu'on a déjà traité ce genre de problème mais si tu connais pas le nombre de colonnes j'vois pas bien comment tu vas réussir à créer ton tableau

    Attention de ne pas trop abuser des tableaux quand même

  5. #5
    Membre chevronné

    Profil pro
    Inscrit en
    Septembre 2004
    Messages
    507
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Septembre 2004
    Messages : 507
    Par défaut
    Je ne suis pas fan des tableaux. D'ailleurs, si je peux traiter les données à la volée dans une boucle incluant les 'DBMS_SQL.COLUMN_VALUE', je le ferai.

    Cependant, je peux avoir le nombre de colonnes en paramètre, même si le but est d'être dynamique au maximum.

    Ceci dit, je ne suis pas loin de la solution. Voici ma procédure de travail.
    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
    CREATE OR REPLACE PROCEDURE proc_test
    IS
       TYPE typ_big_tab IS TABLE OF DBMS_SQL.date_table;
     
       big_tab              typ_big_tab         := typ_big_tab ();
       source_cursor        INTEGER;
       ignore1               INTEGER;
       ignore2              INTEGER;
    BEGIN
       source_cursor := DBMS_SQL.open_cursor;
       DBMS_SQL.parse (source_cursor,
                       'SELECT sysdate, sysdate+1 from dual',
                       DBMS_SQL.native
                      );
       big_tab.EXTEND;
       DBMS_SQL.define_array (source_cursor, 1, big_tab (1), 1, 1);
       big_tab.EXTEND;
       DBMS_SQL.define_array (source_cursor, 2, big_tab (2), 1, 1);
     
       ignore1 := DBMS_SQL.EXECUTE (source_cursor);
       ignore2 := DBMS_SQL.fetch_rows (source_cursor);
     
       DBMS_SQL.COLUMN_VALUE (source_cursor, 1, big_tab (1));
       DBMS_SQL.COLUMN_VALUE (source_cursor, 2, big_tab (2));
     
       DBMS_SQL.close_cursor (source_cursor);
     
    EXCEPTION
       WHEN OTHERS
       THEN
          IF DBMS_SQL.is_open (source_cursor)
          THEN
             DBMS_SQL.close_cursor (source_cursor);
          END IF;
          RAISE;
    END;
    /
    Je me suis inspiré de ceci: http://www.cs.umbc.edu/help/oracle8/...ql.htm#1004875

    Mes problèmes:
    - j'ai du mal à comprendre les paramètres de la procédure DBMS_SQL.define_array
    - le code ci-dessous compile et s'exécute, mais je n'arrive pas à accéder au contenu du tableau (via un dbms_output).

  6. #6
    Expert confirmé
    Avatar de SheikYerbouti
    Profil pro
    Inscrit en
    Mai 2003
    Messages
    6 760
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Mai 2003
    Messages : 6 760
    Par défaut
    Lisez cet article et téléchargez le ZIP. Il contient le script d'un package PL/SQL qui fait ce dont vous avez besoin.

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. Réponses: 6
    Dernier message: 28/09/2010, 15h04
  2. [Oracle 10g]Trou dans les séquences des redo logs
    Par Christophe P. dans le forum Administration
    Réponses: 11
    Dernier message: 07/11/2008, 20h13
  3. Oracle 10g : comment figer les données de la base ?
    Par juanito92 dans le forum Administration
    Réponses: 8
    Dernier message: 18/07/2008, 10h13
  4. Oracle 10g - calcul sur les dates
    Par themateo dans le forum Langage SQL
    Réponses: 7
    Dernier message: 17/08/2007, 10h50
  5. [Oracle 10g] Question sur les sous-requetes
    Par hotkebab99 dans le forum Oracle
    Réponses: 2
    Dernier message: 27/10/2006, 11h25

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo