Re: Using Bind Arrays in Select-Statements

This functionality allows you to select into an array, not execute a select once for each row in an array.

Since (I assume) you are on Oracle 8.0, you could look at my web-site under PL/SQL -> In Lists for an object-based method of using a list of values as a single parameter to a call.

You may be able to use this technique to achieve the result you are after.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

jmangold_at_my-deja.com wrote in message <7rq57h$q7b$1_at_nnrp1.deja.com>...
>Hello,
>
>I want to use a bind array to rerieve data from a
>table. Let's have a look at the code:
>
>PROCEDURE search IS
>tObjeIdComp DBMS_SQL.Number_Table;
>tObjeIdA DBMS_SQL.Number_Table;
>nObjeIdCountComp INTEGER;
>nretVal INTEGER;
>objr_obje_a_id INTEGER;
>sstmt VARCHAR2(2000);
>CN_V7 INTEGER := 2;
>
>C_AnyWrite INTEGER;
>idx INTEGER;
>
>BEGIN
>
> -- ....
>tObjeIdComp(1) := 3071;
>tObjeIdComp(2) := 2136;
>tObjeIdComp(3) := 2140;
>tObjeIdComp(4) := 45230;
>nObjeIdCountComp := 4;
>
>C_AnyWrite := DBMS_SQL.open_cursor;
>
>sstmt := 'SELECT objr_obje_a_id FROM MIS.TB_OBJECT_RELATIONSHIPS
> WHERE objr_obje_n_id = :tObjeIdComp ';
>
>DBMS_SQL.Parse(C_AnyWrite, sstmt, CN_V7);
>DBMS_SQL.DEFINE_COLUMN(C_AnyWrite, 1, objr_obje_a_id);
>DBMS_SQL.Bind_Array(C_AnyWrite, ':tObjeIdComp', tObjeIdComp);
>
>nretVal := DBMS_SQL.Execute(C_AnyWrite);
>LOOP
> idx := DBMS_SQL.FETCH_ROWS(C_AnyWrite);
> IF idx > 0 THEN
> -- get values
> DBMS_SQL.COLUMN_VALUE(C_AnyWrite, 1, objr_obje_a_id);
> -- .. work on
> ELSE
> EXIT;
> END IF;
>END LOOP;
>
>DBMS_SQL.close_cursor(C_AnyWrite);
>
>END search;
>
>
>There's no compile error nor an error while
>working. But it retrieves only the result(s) for
>the last value in the PL/SQL-Table (actually
>tObjeIdComp = 45230).
>
Received on Thu Sep 16 1999 - 04:05:26 CDT