Re: Binding types to a dynamic sql statements

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 19 Aug 2001 14:25:21 -0700
Message-ID: <9lpas102ouu@drn.newsguy.com>

In article <82cecea6.0108181754.3b9ab93c_at_posting.google.com>, christophertava_at_hotmail.com says...
>
>Has anyone tried to used dbms_sql with the collection types?
>
>I want to bind a type to a sql statment for an insert statment?
>
>i.e.
>
>CREATE OR REPLACE TYPE usertype AS TABLE OF VARCHAR2(20)
>/
>
>declare
> l_user_tab usertype := usertype();
> l_varchar2_tab dbms_sql.varchar2_table;
>begin
> l_user_tab.extend;
> l_user_tab(l_user_tab.last) := 'TEST1';
>
> l_user_tab.extend;
> l_user_tab(l_user_tab.last) := 'TEST2';
>
> l_sql := 'INSERT INTO '|| l_table_name || ' ' ||
> 'VALUES(usertype(:l_user_tab))';
>
> WHAT WOULD BE NICE!!!
> dbms_sql.bind_array(l_cursor,':l_user_tab',l_user_tab);
>
> WHAT IS AVAILABLE!!!
> dbms_sql.bind_array(l_cursor,':l_user_tab',l_varchar2_tab);
>
>Any suggestions!!
>
>Thanks!

Native Dyanmic SQL does that.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create or replace type myArrayType as table of number 2 /

Type created.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> create table t ( x int, y myArrayType ) 2 nested table y store as y_tab
3 /

Table created.

ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> 
ops$tkyte_at_ORA8I.WORLD> declare
  2          l_array myArrayType := myArrayType(1,2,3);
  3  begin
  4          execute immediate
  5          'insert into t (x,y) values ( 1, :l_array )' using l_array;

6 end;
7 /

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA8I.WORLD>
ops$tkyte_at_ORA8I.WORLD> select * from t;

1
MYARRAYTYPE(1, 2, 3)

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sun Aug 19 2001 - 16:25:21 CDT