BIND_ARRAY

From: <ineyman_at_perceptron.com>
Date: Mon, 06 Dec 1999 14:51:23 GMT
Message-ID: <82gih9$dmd$1@nnrp1.deja.com>

I hope, Tomas Kyte will answer this question.

I am trying to use dbms_sql.bind_array for variable used in 'IN' where clause.
I create test table:

    CREATE TABLE t1(tpk number, tval number); Then, populate it:

    declare nJ int;
    nI int;
    begin
    for nJ in 1..10 loop

        for nI in 1..10 loop
            insert into t1 values(nJ, nI);
        end loop;

    end loop;
    commit;
    end;
    /

And then I run the following code to retrive data according to 'IN' where clause:

declare lCur int;
lStmt varchar2(4000);
inp_array DBMS_SQL.NUMBER_TABLE;
nJ BINARY_INTEGER;
lpk int;
lval int;
lDummy int;
begin

inp_array(1) := 3;
inp_array(2) := 4;
inp_array(3) := 5;

lStmt := 'select tpk, tval from t1 where tpk in (:Btpk)'; lCur := dbms_sql.open_cursor;

dbms_sql.parse(lCur, lStmt, DBMS_SQL.NATIVE);
DBMS_SQL.BIND_ARRAY(lCur, ':Btpk', inp_array, 1, inp_array.COUNT);
DBMS_SQL.DEFINE_COLUMN(lCur, 1, lpk);
DBMS_SQL.DEFINE_COLUMN(lCur, 2, lval);

lDummy := DBMS_SQL.EXECUTE(lCur);
LOOP
 IF (DBMS_SQL.FETCH_ROWS(lCur) = 0) THEN   EXIT;
 END IF;
 DBMS_SQL.COLUMN_VALUE(lCur, 1, lpk);
 DBMS_SQL.COLUMN_VALUE(lCur, 2, lval);

 dbms_output.put_line(to_char(lpk) || ' ' || to_char(lval));

END LOOP;
dbms_sql.close_cursor(lCur);
end;
/

The result is:
5 1
5 2
5 3
5 4
5 5
5 6
5 7
5 8
5 9
5 10

It uses only the last array element is being used, as if it was BIND_VARIABLE - not BIND_ARRAY.
Is this - expected behavior?

Igor Neyman, OCP DBA
Perceptron, Inc.
(734)414-4627
ineyman_at_perceptron.com

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Mon Dec 06 1999 - 08:51:23 CST