Discussion :

[pedroleouf]

Bonjour,

je cherche désespérément à binder un dbms_sql.varchar2_table grace à la fonction dbms_sql.bind_variable, qui selon la doc et les specs est supposée pouvoir le faire, mais je me retrouve avec une erreur de compilation.

En gros, je parse et bind un stmt (appel d'une fonction oracle qui attend un paramètre de type NESTED TABLE => TYPE ... IS TABLE OF ... INDEX BY ...)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
 
DECLARE
    l_tbl dbms_sql.varchar2_table;
BEGIN
    -- [...]
    dbms_sql.parse(l_c, 'call pkg_truc.machin(:tbl)');
    -- [...]
    dbms_sql.bind_variable(l_c, 'tbl', l_tbl); -- erreur de compilation: PLS-00306: numéro ou types d'arguments erronés dans appel à 'BIND_VARIABLE'
    -- [...]

Je bind aussi des types simples (varchar2, number, date, ...) sans problème, et d'après la doc et les specs je devrai pouvoir binder une nested table ou un varray (j'ai aussi essayé avec un varray plutôt qu'une nested table mais sans succès).
Pas de problème de version, j'ai même installé un nouveau serveur avec la toute dernière 11g pour être sûr d'être en accord avec la version de la doc.
Un petit détail, dans les specs de dbms_sql, une surcharge de bind_variable existe avec le type, mais il est entre guillemets:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

procedure bind_variable(c in integer, name in varchar2, value in "<TABLE_1>");

Est-ce que quelqu'un sait ce qu'impliquent ces guillemets?
Aussi il existe une fonction bind_array mais ce n'est pas la même chose car elle est utilisée pour les éxécutions multiples.

Donc, quelqu'un saurait-il comment je peux binder ma nested table grace à cette fonction bind_variable?

Un grand merci d'avance.

Pierre

[mnitu]

...
Est-ce que quelqu'un sait ce qu'impliquent ces guillemets?
Aussi il existe une fonction bind_array mais ce n'est pas la même chose car elle est utilisée pour les éxécutions multiples.
...

Je n'ai jamais testé mais je pense que c'est cette function qu'il te faut. Finalement tu va récuperer le nested table comme un bulk non ?

[pedroleouf]

Je n'ai jamais testé mais je pense que c'est cette function qu'il te faut. Finalement tu va récuperer le nested table comme un bulk non ?

Non, je ne veux pas récupérer comme un bulk (genre d'éxécution multiple mais récupération/envoi par tableau), mais vraiment passer la nested table en tant que telle à une procédure stoquée telle que celle-ci par exemple (en utilisant dbms_sql bien sûr):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
CREATE OR REPLACE PROCEDURE machin(p_tab IN OUT dbms_sql.varchar2_table) is
    -- [...]
END;

Merci quand-même ;-)

[mnitu]

OK! J'ai trouvé un exemple avec Varray dans la doc d'Oracle 11, Example 10
Il utilise bien DBMS_SQL.BIND_VARIABLE mais par contre le type passé est le type 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
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30)
/...
CREATE OR REPLACE PROCEDURE update_depts(new_dnames dnames_var, region VARCHAR2) IS
   some_dnames dnames_var;
   c      NUMBER;
   r      NUMBER;
   sql_stmt VARCHAR2(32767) :=
    'UPDATE depts SET dept_names = :b1 WHERE region = :b2 RETURNING dept_names INTO :b3';
 
BEGIN
 
   c := DBMS_SQL.OPEN_CURSOR;
 
   DBMS_SQL.PARSE(c, sql_stmt, dbms_sql.native);
 
   DBMS_SQL.BIND_VARIABLE(c, 'b1', new_dnames);
...

[pedroleouf]

Ok, merci!
Je vais donc essayer de binder un varray au lieu d'une nested table...
J'essaie ça dans le WE et te tiens au courant.

@+

Pierre

[mnitu]

Voilà.

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
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
 
SQL> create or replace type var2_tab is table of varchar2(10)
  2  /
 
Type crÚÚ.
 
SQL> create or replace procedure p_nt( p_vartab_i In var2_tab) Is
  2  Begin
  3    For i In p_vartab_i.first .. p_vartab_i.last Loop
  4      dbms_output.put_line(p_vartab_i(i));
  5    End Loop;
  6  end;
  7  /
 
ProcÚdure crÚÚe.
 
SQL> set serveroutput on
SQL> -- static
SQL> declare
  2    l_vtab var2_tab := var2_tab('einz','zwei','drei','vier');
  3  begin
  4    p_nt(l_vtab);
  5  end;
  6  /
einz
zwei
drei
vier
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> create or replace procedure p_dyn_nt Is
  2    ch      Number;
  3    res        Number;
  4    l_vtab var2_tab := var2_tab('einz','zwei','drei','vier');
  5  Begin
  6    ch := dbms_sql.open_cursor;
  7    --
  8    dbms_sql.parse(ch,'begin p_nt(:tab); end;',dbms_sql.native);
  9    dbms_sql.bind_variable(ch, 'tab', l_vtab);
 10    --
 11    res := dbms_sql.execute(ch);
 12    --
 13    dbms_sql.close_cursor(ch);
 14  End;
 15  /
 
ProcÚdure crÚÚe.
 
SQL> exec p_dyn_nt;
einz
zwei
drei
vier
 
ProcÚdure PL/SQL terminÚe avec succÞs.

[pedroleouf]

Merci mintu!!!!!!

Ca fonctionne bien, mais le pb c'est que derrière j'ai besoin de convertir la collection en un objet de type AnyData, et AnyData.ConvertCollection(tab_var) plante car le type est défini comme:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TYPE t_tab IS TABLE OF blablabla;

au lieu de:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

TYPE t_tab IS TABLE OF blablabla INDEX BY PLS_INTEGER/INTEGER/BINARY_INTEGER;

Enfin, c'est déjà un problème de régler, je vais essayer de créer les éléments un par un avec les méthode de AnyData et aussi à l'aide de AnyType, mais je craint que je sois en train de repousser le problème au niveau de la reconversion de AnyData en t_tab qui a lieu par la suite dans mon code.

... bon en fait j'ai testé avant 'envoyer ce message, et j'en suis à ce pb:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
TYPE t_string_array IS TABLE OF varchar2(4000);
...
l_string_array t_string_array;
...
dbms_sql.bind_variable(c, name, l_string_array); -- erreur ici

C'est un appel à une fonciton qui accepte le même type de paramètre, et je me retrouve avec une erreur trop parlante :

ORA-00600: internal error code, arguments: [pigitoidcol], [], [], [], [], [], [], []

Juste pour précision, l_string_array est initialisé et contient des éléments...

Une idée ?

[mnitu]

Peut être que si t'essai de fournir un exemple du code qui ne marche pas...

[SheikYerbouti]

Avez-vous bien parcouru toute la doc ?
http://download.oracle.com/docs/cd/B...ql.htm#i996891

[pedroleouf]

Disons que c'est un package de plus de 4000 lignes, et je n'ai pas le droit de le sortir de la société même si c'est moi qui l'ai crée...

Sinon depuis j'ai fais d'autres tests: au lieu de déclarer le type dans le package, je l'ai déclaré comme toi, en tant qu'object au final, et cela fonctionne.

Donc en gros pour résumer si questions, pour qu'un tel type soit passé à dbms_sql.bind_variable, il doit être crée en tant que type global (pas dans un package)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

CREATE OR REPLACE TYPE t_tab AS TABLE OF blablaba

sans le "index by ..."

Merci pour ton aide mnitu ;-)