Discussion :

[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 :

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
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 :

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
 
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 :

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
 
           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 :

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
 
       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.