Utilisation d'une table PL/SQL en SQL sans créer d'objets
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 :cry:
recuperation de tableau issus d'anonymous block au moyen de ref cursors
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.
