Need Help on Pro-C

[mithubabu1970]

Hello Friends.. I am working on some Pro-C program using Dynamic SQL. I am fetching a set of values and storing those in an host variable array of datatype long. The fetching into the array is working fine. But when I use the in correlated subquery having "NOT IN" and "IN" clause, it behaves differently. For "IN" clasue, the array works fine, but for "NOT IN" clause, it does not work. For example, I have created the following small piece of code.

I have long array MyNum[3]. The Table TEST_LOC has 6 records, 3 records have SerialNum same as those given in MyNum array. When I use "IN" clause, 3 records are inserted in TEST_LOC1 Table. But when I use "NOT IN" clause, it inserts 15 records with duplicate values. I am unable to solve it till now.

Can someone please help me on this? Thanks a lot..
--Sain

--------------------------------------- Sample Pro-C Code -----------------------------
/* This program uses dynamic SQL Method 2 to insert rows into second table from first table. */

#include <stdio.h>
#include <string.h>
#include <stdlib.h>

EXEC SQL INCLUDE "sqlca.h";
EXEC SQL INCLUDE "oraca.h";
EXEC ORACLE OPTION (ORACA=YES);

VARCHAR dynstmt[80];

void main()
{
EXEC SQL BEGIN DECLARE SECTION;
char connStr[101]="";
char TName[20] = "TEST_LOC1";
long MyNum[3] = {18, 21, 12};
EXEC SQL END DECLARE SECTION;

oraca.orastxtf = ORASTFERR;
strcpy(connStr, "psoproj/psoproj@epsnsdev");

EXEC SQL CONNECT :connStr;
if(sqlca.sqlcode != 0)
{ printf("ERROR: Unable to connect to oracle SQLCODE: %d", sqlca.sqlcode);
exit(1);
}
puts("\nConnected to Oracle.\n");

sprintf((char *)dynstmt.arr, "INSERT INTO %s select * from TEST_LOC a1
WHERE a1.SERIALNUM IN (:v1)", TName);

// sprintf((char *)dynstmt.arr, "INSERT INTO %s select * from TEST_LOC a1
// WHERE a1.SERIALNUM NOT IN (:v1)", TName);

dynstmt.len = (unsigned short)strlen((char *)dynstmt.arr);
puts((char *) dynstmt.arr);

EXEC SQL PREPARE S FROM :dynstmt;
EXEC SQL EXECUTE S USING :MyNum ;

printf("\n Value of sqlcode:%d \n", sqlca.sqlcode);

EXEC SQL COMMIT WORK RELEASE;
puts((char *)"\nHave a good day!\n");
exit(0);
}
------------------------- End of Sample Code ------------------------

The Tables TEST_LOC and TEST_LOC1 have same table structure:
CREATE TABLE TEST_LOC
( NAME VARCHAR2(30 BYTE) NOT NULL,
SERIALNUM NUMBER NOT NULL,
ADDDT DATE DEFAULT SYSDATE NOT NULL
);