Utilisation d'une table PL/SQL en SQL sans créer d'objets

[murgen23]

Bonjours,

Je cherche un moyen de populer des Tables PL/SQL et puis de les utiliser avec des commandes SQL ou de les exporter comme ref cursor et ce sans creer d'objets dans le data dictionary. Tout doit ce faire a l'interieur de "Declare" :
Jusqu'a present je ne suis pas parvenus.

J'ai essayé des variantes avec des fonction pipelined et la command "row pipe":

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
 
 declare
  2        TYPE TYP_TAB IS TABLE of varchar2(30) ;
  3        tab  TYP_TAB ;
  4        v_var varchar2(30);
  5        Type typ_ref IS ref cursor ;
  6        ref typ_ref;
  7    FUNCTION ff RETURN TYP_TAB pipelined IS
  8    begin
  9      FOR i IN tab.first..tab.last
 10      loop
 11         pipe row(tab(i) );
 12      end loop;
 13     RETURN;
 14    end ;
 15  Begin
 16     tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
 17     open ref FOR    SELECT * FROM TABLE(cast( tab AS typ_tab) );
 18     loop
 19      NULL ;
 20      end loop;
 21     dbms_output.put_line('f=' || tab(1) );
 22     dbms_output.put_line('f=' || tab(2) );
 23     dbms_output.put_line('f=' || tab(3) );
 24  end;
 25      --loop
 26      --  fetch ref into v_var ;
 27      --  exit when ref%NOTFOUND;
 28      --  dbms_output.put_line('v=' ||v_var) ;
 29*     --end loop;
SQL> /
   open ref FOR    SELECT * FROM TABLE(cast( tab AS typ_tab) );
                                                    *
ERROR at line 17:
ORA-06550: line 17, COLUMN 53:
PL/SQL: ORA-00902: invalid datatype
ORA-06550: line 17, COLUMN 20:
PL/SQL: SQL Statement ignored

Mon but ultime est de créer des anonymous blocks depuis java, de les expedier sur le serveur pour qu'il soient traités sur le serveur et de recupèrer un ref cursor en sortie sans avoir creé un objet sur le serverur.

Quelqu'un a t'il un example de transformation d'une table PL/SQL en SQL sachant que toute commande "CREATE or REPLACE" est prohibée?

Voici ma derniere tentative : ca échoue car la pseudo colonne "level" ne s'eincremente pas pour la table PL/SQL alors qu'elle s'incremente bien pour la colonne.

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
 
declare
  TYPE TYP_TAB IS TABLE of varchar2(100) ;
  tab  TYP_TAB ;
  cpt number:=0;
Begin
       tab := TYP_TAB( 'lundi','mardi','mercredi' ) ;
       cpt:=tab.last;
       FOR c IN ( SELECT level, tab(level+1) aa FROM dual connect BY level <= cpt )     /* <=tab.last does not work here */
       loop
           cpt:=cpt+1;
           dbms_output.put_line('c='||c.aa || ' level=' || to_char(c.level) ) ;
      end loop;
   dbms_output.put_line('f=' || tab(1) );
   dbms_output.put_line('f=' || tab(2) );
   dbms_output.put_line('f=' || tab(3) );
  end;
/
c=lundi level=1
c=lundi level=2
c=lundi level=3
f=lundi
f=mardi
f=mercredi
 
PL/SQL procedure successfully completed.

pas d'erreur mais pas le resultat escompte non plus

[SheikYerbouti]

Bonjour,

Mon but ultime est de créer des anonymous blocks depuis java, de les expedier sur le serveur pour qu'il soient traités sur le serveur et de recupèrer un ref cursor en sortie sans avoir creé un objet sur le serverur.

Pouvez-vous donner un exemple concret de ce que vous souhaitez ?

[murgen23]

Actuellement le code ci dessous me permet d'envoyer un anonymous block pour faire un traitement et je recupere un string:

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
 
           String sqlCmd="DECLARE \n" +
             " hScn number := 0;    lScn number := 0;    sScn number;     ascn number;     alog varchar2(1000);     v_session number; \n" +
             "begin \n" +
             "select logminer_id into v_session from  dba_capture where capture_name = upper('" + capName + "') ;\n" +
             "select min(start_scn), min(applied_scn) into sScn, ascn from dba_capture  where capture_name  = ('" + capName + "') ;\n" +
             " for cr in (select distinct(a.ckpt_scn) from system.logmnr_restart_ckpt$ a \n" +
             "      where a.ckpt_scn <= ascn and a.valid = 1 and session# = v_session \n" +
             "        and exists (select * from system.logmnr_log$ l \n" +
             "                           where a.ckpt_scn between l.first_change# and l.next_change# and session# = v_session ) \n" +
             "       order by a.ckpt_scn desc)\n" +
             "loop \n" +
             "if (hScn = 0) then \n" +
             "  hScn := cr.ckpt_scn; " +
             "else \n" +
             "  lScn := cr.ckpt_scn; \n " +
             "  exit; \n " +
             "  end if;\n" +
             "end loop; \n" +
             "if lScn = 0 then\n" +
             "  lScn := sScn; \n" +
             "end if;\n" +
             "select min(name) into alog from v$archived_log where lScn between first_change# and next_change#; \n" +
             "?:= 'Capture will restart from SCN ' || lScn ||' in log '||alog; \n" +
           "end;" ;
           //System.out.println(sqlCmd);
           try {
               CallableStatement stmt = conn.prepareCall(sqlCmd);
               //stmt = conn.prepareCall(sqlCmd);
               stmt.registerOutParameter(1,Types.VARCHAR);
                stmt.execute();
               // print the results
               var=stmt.getString(1);
              //System.out.println("var="+var)               ;
               stmt.close();

Ce code marche car je recupere le string du block anonyme grace a la ligne "? := trec" , et comme l'appetit vient en mangeant, je me suis dit que je pouvais aussi recuperer un set de rows (ref cursor) apres traitement sur le serveur de toute l'intelligence du process. je butte sur le fait de populer le ref cursor d'apres une table PL/SQL :

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
 
       String sqlCmd="declare \n" +
                      "type ttrec is table of number index by varchar2(30);"+
                      "trec ttrec; \n" +
                      "i number;\n" +
                      "fsql varchar2(512);\n" +
                      "begin\n" +
                      "for c in (select table_name from dba_tables where table_name like 'LOGM%' and owner='SYSTEM')\n" +
                      "  loop\n" +
                      "  fsql:='select count(*) from system.' || c.table_name ;\n" +
                      "  execute immediate fsql into i;\n" +
                      "  trec(c.table_name) := i;\n" +
                      "end loop;\n" +
                      "? := trec;\n" +
                      "end;" ;
System.out.println(sqlCmd);                      
        CallableStatement stmt;
        try {
              stmt = conn.prepareCall(sqlCmd);
              stmt.registerOutParameter(1,Types.ARRAY);  -- ou bien stmt.registerOutParameter(1,OracleTypes.CURSOR);
              stmt.execute();
              Object VALUES=stmt.getArray(1);
              List resultats = new ArrayList();
             -- process from now on the returned rowset

Comme on le voit grace a ce block j' essaie d' éviter de rapatrier sur le client la liste des tables dont il faut compter les rows puis generer une boucle qui va executer un SQL pour chaque table et rapatrier le resultat. Je veux faire tout le boulot sur le serveur et ne rapatrier que le tableau final de 2 colonnes. Quoi qu ici j'utilise un hash table avec le nom des tables en indices, j'essaye aussi avec un type RECORD (tname varchar2(30), v_count number) et sa table of record.

D'une manière generale j'essaie de mettre au point une methode qui me permette de generer un block de code sur le client, qu'on soumet au serveur, et le serveur renvoie un rowset. Ce serait le pendant PL/SQL du statment SQL. Je n'ai de success qu'avec un String, je m'essaie au rowset.

[SheikYerbouti]

Regardez si vous ne trouvez pas votre bonheur dans ceci:

[murgen23]

Helas, il y a plein de belles choses mais elle requierent le type OBJECT, pas le type RECORD. Subtile difference qu'on ne souligne pas assez dans la doc PL/SQL : OBJECT ne peux pas etre declaré dans un anonymous block: Il semble qu'il doive imperativement être dans le data dictionary. Cela exclu semble t il alors l'utilisation de fonction PIPEPLINED avec PIPE ROW. J'avais pensé simplement faire un PIPE ROW au STDOUT ou faire qlqchose du genre REF_CURSOR=f(PIPE). Mais voila avec f(pipe) qui n'accepte que les type "OBJECTS" et le type "OBJECT" est exclut des anonymous blocks. La boucle est bouclée.

Sinon je trouve aisement comment remplir un associative array à partir d'un Ref cursor, mais rien pour l'inverse. Je cherche donc maintenant à récuperer un ARRAY, c'est mon dernier espoir.

NT: Vraiment domage que la methode du "connect by < level " avec utilisation du level comme indice du tableau ne fonctionne pas. C'eut été un moyen élégant pour transformer un tableau en table SQL. Ce qui me fait le plus raler est qu'Oracle ne donne pas d"erreur.

[SheikYerbouti]

La fonction sur laquelle pointe mon lien retourne un curseur et ne fait intervenir aucun objet.

[murgen23]

Le malheur est que cette fonction traduit le rowset issus d'un SQL d'une table physique en un ref cursor. La source n'est pas une table PL/SQL.

La transformation d'un set de valeurs issus de VARRAY ou ASSOCIATIVE ARRAY en colonnes d'un authentique rowset comme en produisent les select sur tables, cursor et ref cursor semble etre le bas qui blesse.

Comment produire un ref cursor a partir de table PL/SQL?

[mnitu]

Envoyé par murgen23

...

Comment produire un ref cursor a partir de table PL/SQL?

En utilisant une fonction pipelined.

[murgen23]

Un pipeplined function doit avoir comme argument un type object ou derivé d'un object mais un anonymous block ne peut declare ce type. Mais si tu as un example du contraire, je suis preneur.

Ca fait des heures que j'essaie des trucs pour contourner cette limitation. J'ai ete jusqu'a declarer un fonction pipe qui derive la table dual pour pour ensuite faire un ROW PIPTE (dual%type). Mais héhé, on ne peut pas deriver la table sys.dual en un type table. tu ne vois le message qu'a l'execution :p

[mnitu]

Envoyé par murgen23

Un pipeplined function doit avoir comme argument un type object ou derivé d'un object mais un anonymous block ne peut declare ce type.
...

Je suppose que tu parles de type de retour de la fonction et non pas d'argument.

The return type of the pipelined table function must be a supported collection type, such as a nested table or a varray. This collection type can be declared at the schema level or inside a package. Inside the function, you return individual elements of the collection type. The elements of the collection type must be supported SQL datatypes, such as NUMBER and VARCHAR2. PL/SQL datatypes, such as PLS_INTEGER and BOOLEAN, are not supported as collection elements in a pipelined function.

Mais j’ai du mal à voir ce qui t’empêche de créer le type nécessaire et la fonction pipelined et ensuite de les utiliser pour produire le ref cursor de la table PL/SQL.

[murgen23]

Les fonctions pipeplined ne traitent que les types de base (int,varchar2) ou les objects. Les row des tables, ref cursor sont considerée comme objects. Les Records, et les tuple issus de PL/SQL tables ne sont pas des objects.

Mais probleme est lié à proprieté du type "OBJECT" :

Code :

1
2
3
4
5
6
 
 
SQL> CREATE type fobject IS object( col1 varchar2(30), col2 number) ;
 
 
Type created.

par contre, le meme code dans un anonymous block:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
 
SQL> declare
  2  type fobject IS object( col1 varchar2(30), col2 number) ;
  3  begin
  4  NULL;
  5  end;
  6  /
type fobject IS object( col1 varchar2(30), col2 number) ;
*
ERROR at line 2:
ORA-06550: line 2, COLUMN 1:
PLS-00540: object NOT supported IN this context.

Conclusion : les anonymous blocks ne supportent pas le type "OBJECT" à la volée. Avec un peu de recherche tu découvres que les pipelined function fabriquent des objets dans le data dictionary dans ton dos. Donc tu ne peux utiliser un fonction pipe dans un anonymous block, si tu n'as pas creé au préalable des "OBEJCTS" dans le data dictionary.

http://igor-db.blogspot.com/2007/03/...ly-create.html