dbms_sql.bind_variable avec une "nested table"

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:

---

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:

---

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

Citation:

---

Envoyé par pedroleouf

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

Citation:

---

Envoyé par mnitu

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:

---

1 2 3

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

---

Merci quand-même ;-)

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:

---

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

---

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

Voilà.

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

---

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:

---

TYPE t_tab IS TABLE OF blablabla;

---

au lieu de:

Code:

---

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:

---

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

Citation:

---

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

---

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

Une idée :roll:?

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

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

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:

---

CREATE OR REPLACE TYPE t_tab AS TABLE OF blablaba

---

sans le "index by ..."

Merci pour ton aide mnitu ;-)