Pro*C returning Not a number in the IN clause

Question

I am trying to run an SQL from my C code using Pro*c. This is my SQL

EXEC SQL select count(1) from MY_TABLE where id IN ( :format );

id is a NUMBER(10) and format is a char array containing value 1,2,3,4,5

This is returning error with "Not a Number" However if the format array is just a single number, its running fine. Please let me know if someone find the error.

Thx!

IN clause dont support bind variable this way. With Pro*C you may have form a query string with hard coded values or try [this](https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:139812348065) — Maheswaran Ravisankar, Jun 26 '14 at 18:48
think about adding a list of arguments for the IN operator. No need to hard code it in the SQL, just add (:form1, :form2, ...) — , Jun 27 '14 at 10:49
@OracleUser: Plz add it as a comment so that I can mark it as answer. — rahul, Jun 28 '14 at 16:38

score 1 · Accepted Answer · edited May 23 '17 at 10:33

1

IN clause accept bind variables only as (:1,:2,:3) , so you have yo know the number of bind variables before hand. Which is not likely.

Simplest way is to form a dynamic query string with hard coded values in Pro*C.

There are alternative solutions from AsKTom and My SO answer

edited May 23 '17 at 10:33

Community

1
1

answered Jun 30 '14 at 06:35

Maheswaran Ravisankar

17,652
6
47
69

Singh Gaurav · Answer 2 · 2017-09-26T20:34:37.157

    for(i=0;i<5;i++)
    {
        EXEC SQL select count(1) from MY_TABLE where id IN ( :format[i] );
    }

I am telling to use above code as it is too lousy, just explaining how arrays works in Pro*C. You have to give the index of the array.

Edit: I learned a new thing for this problem:- We can also use

    EXEC SQL FOR 5
        SELECT COUNT(1) FROM MY_TABLE WHERE id IN (:format);

   EXEC SQL SELECT COUNT(1) FROM MY_TABLE WHERE id IN 
             (:format[0],:format[1],:format[2],:format[3],:format[4])

Pro*C returning Not a number in the IN clause

2 Answers2