Re: Using host arrays in PLSQL

From: Hicham Douba <hdouba_at_sprynet.com>
Date: 1998/09/28
Message-ID: <360faa66.5815770@news.sprynet.com>#1/1

On Wed, 23 Sep 1998 16:16:38 +0200, Flemming Jans <fj_at_belle.dk> wrote:

>Hi,
>
>Is there any way I can use arrays or host arrays to INSERT/UPDATE/DELETE
>
>for example 100 rows in one operation in PL/SQL (as you can do it in Pro
>C)
>
>Something like:
>
>DECLARE
> names[100];
>BEGIN
> -- Insert 100 rows in one operation
> names[0] := 'Superman';
> names[1] := 'Batman';
> INSERT INTO name_table VALUES (names);
>
> -- Fetch 100 rows in one operation
> SELECT name FROM name_table INTO names
>
>END;
>
>Regards,
>
>Flemming Jans
>(fj_at_belle.dk)
>
>

The following is an example that uses the array processing feature of Oracle 8.

set serveroutput on

declare
  cur pls_integer := dbms_sql.open_cursor;   d_table dbms_sql.date_table;
  n_table dbms_sql.number_table;
  vc_table dbms_sql.varchar2_table;
  fdbk pls_integer;
begin
  timing := dbms_utility.get_time;
  dbms_output.put_line('time = ' || to_char(dbms_utility.get_time));   n_table(1) := 900;
  d_table(1) := to_date('1998-04-02');
  vc_table(1) := 'x';

  n_table(2) := 901;
  d_table(2) := to_date('1998-04-02');
  vc_table(2) := 'x';

  n_table(3) := 902;
  d_table(3) := to_date('1998-04-02');
  vc_table(3) := 'x';

  n_table(4) := 903;
  d_table(4) := to_date('1998-04-02');
  vc_table(4) := 'x';

  dbms_sql.parse(cur, 'insert into t values (:n, :vc, :d)', dbms_sql.native);

  dbms_sql.bind_array(cur, 'n', n_table);
  dbms_sql.bind_array(cur, 'vc', vc_table);
  dbms_sql.bind_array(cur, 'd', d_table);

  fdbk := dbms_sql.execute(cur);

  dbms_output.put_line('rows inserted = ' || to_char(fdbk));

  dbms_sql.close_cursor(cur);

  dbms_output.put_line('time = ' || to_char(dbms_utility.get_time));   dbms_output.put_line('time = ' || to_char(dbms_utility.get_time - timing));

exception
  when others then

    dbms_output.put_line(sqlerrm);
    dbms_output.put_line(sqlcode);
    dbms_sql.close_cursor(cur);

end;
/

SQL> desc t

 Name                            Null?    Type
 ------------------------------- -------- ----
 N                                        NUMBER
 VC                              NOT NULL VARCHAR2(30)
 D                                        DATE

Good Luck,

Hicham Douba
(613)829-3004 Received on Mon Sep 28 1998 - 00:00:00 CDT