1
votes

Accès aux éléments du type d'enregistrement Oracle PLSQL lors de l'exécution

J'utilise du SQL dynamique où j'utilise dynamiquement la valeur du nom de la colonne à lier et sa valeur à lier

OLD CODE

       <Inner Loop>
    FOR j IN lvaMappingTab.FIRST..lvaMappingTab.LAST
    LOOP
    lvsColForBinding := ':'||lvaMappingTab(j).MstRptColCds;

        lvsColValForBind :=  'lvrCurDBOBJDTL(i).'||lvaMappingTab(j).RptColCd;

DBMS_SQL.BIND_VARIABLE ( lvnInsertCursorId,lvsColForBinding, lvsColValForBind);

  END LOOP;

Au lieu d'écrire BIND_VARIABLE pour 100 fois, je veux accéder dynamiquement à la valeur de la collection. Je suis capable de récupérer la valeur des colonnes de manière dynamique, qui doivent être bind (lvsColForBinding), mais la valeur de lvsColValForBind arrive en tant que 'lvrCurDBOBJDTL (i) .DBONAME', 'lvrCurDBOBJDTL (i) .DBOTYPE' et même pour le reste des 98 colonnes,

<Outer Loop>
FOR i IN lvaDBOBJDTLRecTab.FIRST .. lvaDBOBJDTLRecTab.LAST
LOOP
  DBMS_SQL.BIND_VARIABLE ( lvnInsertCursorId, ':RTTEXT2VC100', 
  lvaDBOBJDTLRecTab(i).DBONAME  );

  DBMS_SQL.BIND_VARIABLE ( lvnInsertCursorId, ':RTTEXT3VC100', 
  lvaDBOBJDTLRecTab(i).DBOTYPE  );
    3.
   .
  .
  .
  100

END LOOP;

quand DBMS_SQL.BIND_VARIABLE est exécuté pour chaque ligne, comme mentionné précédemment Column to be bind est correct mais la valeur doit être bind, à la place de venir comme valeur de 'XYZ' = lvrCurDBOBJDTL (i) .DBONAME il vient comme ceci entre guillemets simples 'lvrCurDBOBJDTL (i) .DBONAME' idem pour toutes les colonnes. comment pouvons-nous extraire la valeur de chaque élément dans la boucle interne. quelle étape devons-nous faire pour récupérer la valeur de lvsColValForBind?

Lors du débogage via SQLDEveloper Watches, je peux voir le nom, la valeur et le type de l'élément, en ajoutant et en double-cliquant sur la variable d'enregistrement plsql quel est le SQL derrière cela, pouvons-nous l'utiliser dans le codage?

oracle plsql dynamic-sql

0 commentaires

3 Réponses :

0
votes

Lorsque vous appelez bind_variable , vous liez une valeur réelle à un espace réservé. Donc, si vous fournissez une chaîne qui est le nom de votre variable, eh bien, cette chaîne est la valeur liée à l'espace réservé.

Si le tableau contient ces valeurs, alors faites simplement référence à l'élément du tableau et non au nom de cet élément, comme dans:

DBMS_SQL.BIND_VARIABLE ( 
   lvnInsertCursorId,
   lvaMappingTab(j).MstRptColCds, 
   lvrCurDBOBJDTL(i).lvaMappingTab(j).RptColCd);

Mais je suis presque sûr que ce n'est pas ce vous y êtes. J'espère que cela vous aidera!

1 commentaires

J'ai essayé cela aussi, cela donne une erreur: PLS-00302: Le composant 'lvaMappingTab' doit être déclaré. Comme étape suivante, j'ai déclaré «lvaMappingTab» au niveau du package. Même erreur PLS-00302. C’est la raison pour laquelle j’essayais d’utiliser la fonctionnalité / SQL qui est utilisé par SQLDEVELOPER lors de la génération de la valeur des éléments de collection dans la fenêtre «WATCHES». il montre le nom de l'élément, la valeur de l'élément et le type de l'élément (+ plus de détails) Similaire à cela, si je peux accéder aux éléments de manière dynamique, je devrais pouvoir comparer et faire correspondre les valeurs de la variable de liaison.

0
votes

Ma première recommandation est que vous utilisiez du SQL dynamique pour générer beaucoup de code stupide au lieu d'utiliser une petite quantité de PL / SQL intelligent. Si la génération de code ne fonctionne pas, vous pouvez utiliser ANYDATA et ANYTYPE pour créer une réflexion PL / SQL pour parcourir dynamiquement les éléments d'un enregistrement au moment de l'exécution. > écrivez BIND_VARIABLE 100 fois, mais créez un petit programme pour générer les 100 lignes de code pour vous. Si les données proviennent finalement d'une table et vont dans une autre table, l'entrée et la sortie peuvent être prévisibles sur la base de vues de dictionnaire de données comme DBA_TAB_COLUMNS.

Espérons qu'une petite requête comme celle-ci pourrait aider à générer tout le code pour une seule table:

1
A
2
B

Ensuite, vous pouvez copier-coller la sortie dans le bloc PL / SQL. Vous voudrez probablement aussi un avertissement, comme "ne pas modifier, ce code est généré automatiquement par la procédure CODE_TRON_2000".

Cette approche ne fonctionnera que si le code PL / SQL est prévisible, basé sur les données dictionnaire ou d'autres métadonnées.

Réflexion PL / SQL

Il n'y a pas de réflexion PL / SQL pure pour les types PL / SQL * mais il existe une solution simple si vous êtes prêt à créer le les types d'enregistrement comme des objets SQL à la place. Si tous vos enregistrements PL / SQL sont basés sur des types d'objets, ANYDATA et ANYTYPE peuvent être utilisés pour accéder dynamiquement aux attributs. Les types d'objets et les types d'enregistrements PL / SQL sont assez similaires, il devrait être relativement simple de les convertir l'un en l'autre.

Par exemple, si vous créez un type d'objet contenant un nombre et une chaîne: p>

declare
    type v_nt_type is table of v_type;
    v_values v_nt_type := v_nt_type(v_type(1, 'A'), v_type(2, 'B'));
begin
    --For each record:
    for i in 1 .. v_values.count loop
        declare
            v_anydata anydata := anydata.ConvertObject(v_values(i));
            v_number number;
            v_varchar2 varchar2(4000);
            v_result pls_integer;
            v_anytype anytype;
            v_dummy_num  pls_integer;
            v_dummy_char varchar2(4000);
            v_dummy_anytype anytype;
            v_number_of_elements number;
        begin
            --Get the ANYTYPE and the number of elements.
            v_result := v_anydata.getType(v_anytype);
            v_result := v_anytype.getInfo
            (
               prec        => v_dummy_num,
               scale       => v_dummy_num,
               len         => v_dummy_num,
               csid        => v_dummy_num,
               csfrm       => v_dummy_num,
               schema_name => v_dummy_char,
               type_name   => v_dummy_char,
               version     => v_dummy_char,
               numelems    => v_number_of_elements
            );

            --For each element in the record:
            for i in 1 .. v_number_of_elements loop
                --Find the type of the element:
                v_anydata.piecewise;
                v_result := v_anytype.getAttrElemInfo(
                pos            => i,
                prec           => v_dummy_num,
                scale          => v_dummy_num,
                len            => v_dummy_num,
                csid           => v_dummy_num,
                csfrm          => v_dummy_num,
                attr_elt_type  => v_dummy_anytype,
                aname          => v_dummy_char);

                --This is where you do something interesting with the values.
                --(The same code merely prints the values.)
                if v_result = dbms_types.typecode_number then
                    v_result := v_anydata.getNumber(num => v_number);
                    dbms_output.put_line(v_number);
                elsif v_result = dbms_types.typecode_varchar2 then
                    v_result := v_anydata.getVarchar2(c => v_varchar2);
                    dbms_output.put_line(v_varchar2);
                --TODO: Add other potential types here.
                end if;
            end loop;
        end;
    end loop;
end;
/

Ce (douloureux) bloc PL / SQL montre comment parcourir tous les enregistrements d'une collection, puis parcourir tous les attributs de chaque enregistrement. (Le code imprime les valeurs pour, vous devrez ajouter vous-même les éléments de liaison.)

create or replace type v_type is object(a number, b varchar2(1));

Résultats:

--Generate PL/SQL statements for binds.
select
    'DBMS_SQL.BIND_VARIABLE(lvnInsertCursorId, '':RTTEXT'||column_id||'VC100'', lvaDBOBJDTLRecTab(i).'||column_name||');'
from dba_tab_columns
where owner = 'SOME_OWNER'
    and table_name = 'SOME_TABLE'
order by 1;

* Vous avez raison de dire qu'il doit y avoir un moyen de trouver ces informations d'exécution, si le débogueur les obtient. Mais pour autant que je sache, il n'y a aucun moyen pour PL / SQL de récupérer ces informations de débogage. Peut-être n'est-il disponible que pour une interface OCI (?)?

0 commentaires

0
votes

@Jon Merci pour vos contributions, cela a aidé. aussi je suis capable d'itérer des cols sans créer des OBJETS en utilisant DBMS_SQL.DESCRIBE_COLUMNS.

** Le code ci-dessous nécessite encore un peu de réglage fin, mais fonctionne principalement :)

   BEGIN
        COLS_TRAVERSE('SELECT * FROM ALL_OBJECTS WHERE ROWNUM<=100');
   END;



   create or replace PROCEDURE COLS_TRAVERSE ( p_query in varchar2 )
   AS
            v_curid    NUMBER;
            v_desctab  DBMS_SQL.DESC_TAB;
            v_colcnt   NUMBER;
            v_RowNumcnt   NUMBER := 1;
            v_Colname_var  VARCHAR2(10000);
            v_name_var  VARCHAR2(10000);
            v_num_var   NUMBER;
            v_date_var  DATE;
            v_row_num    NUMBER;
            p_sql_stmt VARCHAR2(1000);

   BEGIN
        v_curid := DBMS_SQL.OPEN_CURSOR;
        DBMS_SQL.PARSE(v_curid, p_query, DBMS_SQL.NATIVE);
        DBMS_SQL.DESCRIBE_COLUMNS(v_curid, v_colcnt, v_desctab);

       -- Define columns:
       FOR i IN 1 .. v_colcnt LOOP
        IF v_desctab(i).col_type = 2 THEN
            DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_num_var);
            ELSIF v_desctab(i).col_type = 12 THEN
            DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_date_var);
            ELSE
            DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_name_var, 50);
            END IF;
        END LOOP;
        v_row_num := dbms_sql.execute(v_curid);
        -- Fetch rows with DBMS_SQL package:
        WHILE DBMS_SQL.FETCH_ROWS(v_curid) > 0 LOOP

          FOR i IN 1 .. v_colcnt 

            LOOP
                v_Colname_var := v_desctab(i).col_name;
                dbms_output.put_line( 'Name:' ||v_Colname_var );
                IF (v_desctab(i).col_type = 1) THEN
                    DBMS_SQL.COLUMN_VALUE(v_curid, i, v_name_var);
                    dbms_output.put_line( 'String Value:' || v_name_var );
                ELSIF (v_desctab(i).col_type = 2) THEN
                    DBMS_SQL.COLUMN_VALUE(v_curid, i, v_num_var);
                    dbms_output.put_line( 'Number Value:' || v_num_var);
                ELSIF (v_desctab(i).col_type = 12) THEN
                    DBMS_SQL.COLUMN_VALUE(v_curid, i, v_date_var);
                    dbms_output.put_line( 'Date Value:' || v_date_var );
                END IF;
            END LOOP;



            dbms_output.put_line( 'End of Row Number # ' ||v_RowNumcnt );

            v_RowNumcnt := v_RowNumcnt+1;

        END LOOP;

        DBMS_SQL.CLOSE_CURSOR(v_curid);
     END;
     /


    DBMS_OUT PUT 

    Name:OWNER
    String Value:SYS
    Name:OBJECT_NAME
    String Value:ORA$BASE
    Name:SUBOBJECT_NAME
    String Value:
    Name:OBJECT_ID
    Number Value:134
    Name:DATA_OBJECT_ID
    Number Value:
    Name:OBJECT_TYPE
    String Value:EDITION
    Name:CREATED
    Date Value:30-03-18
    Name:LAST_DDL_TIME
    Date Value:30-03-18
    Name:TIMESTAMP
    String Value:2018-03-30:21:37:22
    Name:STATUS
    String Value:VALID
    Name:TEMPORARY
    String Value:N
    Name:GENERATED
    String Value:N
    Name:SECONDARY
    String Value:N
    Name:NAMESPACE
    Number Value:64
    Name:EDITION_NAME
    String Value:
    Name:SHARING
    String Value:NONE
    Name:EDITIONABLE
    String Value:
    Name:ORACLE_MAINTAINED
    String Value:Y
    Name:APPLICATION
    String Value:N
    Name:DEFAULT_COLLATION
    String Value:
    Name:DUPLICATED
    String Value:N
    Name:SHARDED
    String Value:N
    Name:CREATED_APPID
    Number Value:
    Name:CREATED_VSNID
    Number Value:
    Name:MODIFIED_APPID
    Number Value:
    Name:MODIFIED_VSNID
    Number Value:
    End of Row Number # 1

    Name:OWNER
    String Value:SYS
    Name:OBJECT_NAME
    String Value:DUAL
    Name:SUBOBJECT_NAME
    String Value:
    Name:OBJECT_ID
    Number Value:143
    Name:DATA_OBJECT_ID
    Number Value:143
    Name:OBJECT_TYPE
    String Value:TABLE
    Name:CREATED
    Date Value:30-03-18
    Name:LAST_DDL_TIME
    Date Value:31-03-18
    Name:TIMESTAMP
    String Value:2018-03-30:21:37:22
    Name:STATUS
    String Value:VALID
    Name:TEMPORARY
    String Value:N
    Name:GENERATED
    String Value:N
    Name:SECONDARY
    String Value:N
    Name:NAMESPACE
    Number Value:1
    Name:EDITION_NAME
    String Value:
    Name:SHARING
    String Value:METADATA LINK
    Name:EDITIONABLE
    String Value:
    Name:ORACLE_MAINTAINED
    String Value:Y
    Name:APPLICATION
    String Value:N
    Name:DEFAULT_COLLATION
    String Value:USING_NLS_COMP
    Name:DUPLICATED
    String Value:N
    Name:SHARDED
    String Value:N
    Name:CREATED_APPID
    Number Value:
    Name:CREATED_VSNID
    Number Value:
    Name:MODIFIED_APPID
    Number Value:
    Name:MODIFIED_VSNID
    Number Value:
    End of Row Number # 2

0 commentaires