| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Pro*C Help Needed
On Wed, 13 Aug 1997 15:02:38 -0500, Ed Tolsch <ds56947_at_fmr.com> wrote:
>I'm trying to use a host variable within a select clause as follows:
>
>EXEC SQL BEGIN DECLARE SECTION
>char HostVar[10][15];
>EXEC SQL END DECLARE SECTION
>
>strcpy(HostVar[0], 'string1');
>strcpy(HostVar[1], 'string2');
>
> select somefield
> from table
> where value in (:Hostvar);
>
>This does not return any values unless I fill up the entire array. Any
>help would be appreciated! Thanks.
You can't use a HOST array in the manner you are trying. In a select statement, HOST arrays may be fetched into (reducing the total number of network round trips). You can't use them in a where clause as you are trying. The In statement needs a fixed number of elements to 'in' on, you would have to write the above as:
exec sql declare C1 cursor for
select somefield from table
where values in ( :HostVar[0], :HostVar[1], ...., :HostVar[9] );
In an Insert/Update/Delete statment, HOST arrays may be used in the where clause (eg: exec sql delete from emp where empno = :HostVar). This has the effect of executing the delete statement N times where N is the size of the host array (you would typically use exec sql FOR :N delete from emp where empno = :HostVar to specify N though). Again, this has the effect of executing the delete statement N times.
So in a Select, host arrays have the effect of executing a FETCH N times.
in a DML stmt, host array have the effect of executing the STMT N times.
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |