Re: Host variable in IN clause?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 31 Dec 1998 18:31:04 GMT
Message-ID: <369bc1bd.105911142@192.86.155.100>

A copy of this was sent to Richard Murphy <rmurphy_at_lbpc.com> (if that email address didn't require changing) On Thu, 31 Dec 1998 12:06:07 -0600, you wrote:

>Trying to use a host variable in a ProC program in a Where field IN
>:hostVar statement.
>Tried all sorts of mutations of this statement. The variable holds a
>string of codes ex. "1359,2345,6789" or "'1359','2345','7689'"and the
>field is a numeric field. I've tried converting the field to char,
>enclosing the numbers in single quotes, etc. but with no luck.
>Does someone know the syntax for what i am trying to do.
>
>Thanks, Richard M.

You cannot bind the string '1359','2345','7689' into an IN clause and have it behave as if you were looking for the three values. Since you have but one host variable there -- it is looking for the column that is in the set of the single value "'1359','2345','7689'" (eg: the column would have to be equal to '1359','2345','7689'). This is like executing the query:

select * from emp where empno in ( '1234,5678,9012' ); or
select * from emp where empno in ( '''1234'',''5678'',''9012''' );

To do this dynamic IN, you can code something like:

static void process( char * in_string ) {
varchar sqlstmt[1000];
varchar ename[40];

EXEC SQL WHENEVER SQLERROR DO sqlerror_hard();

sprintf( sqlstmt.arr, "select ename from emp where empno in ( %s )",

in_string);
sqlstmt.len = strlen(sqlstmt.arr);

printf( "\n\n%s\n", sqlstmt.arr );

    EXEC SQL PREPARE S FROM :sqlstmt;
    EXEC SQL DECLARE C CURSOR FOR S;     EXEC SQL OPEN C;
    for( ;; )
    {

        EXEC SQL WHENEVER NOTFOUND DO break;
        EXEC SQL FETCH C INTO :ename;

        printf( "%.*s\n", ename.len, ename.arr );

}

EXEC SQL CLOSE C;
}

You must use dynamic sql for example. Since this is pretty easy as shown above (you know the output columns so you can still use FETCH ... INTO and avoid a sqlda structure) its almost as easy as doing 'static' sql.

Beware however, this totally by passes SHARED SQL. Each query will potentially be a brand new, unique query. If you do this hundreds or thousands of times, you will want to do this differently for performance reasons. If you have a maximum number of IN fields you ever expect, we can still use 'static' sql with a little bit of parsing to do the binding....

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Thu Dec 31 1998 - 12:31:04 CST