ORA-00984 column not allowed here

Question

I am getting error "Execute-984 ORA-00984: column not allowed here" while I am inserting values in my table Registred_Customer using Pro*C

Registred_Customer is defined as

CREATE TABLE Registred_Customer (
    Cust_id NUMBER(6) PRIMARY KEY,
    Name VARCHAR2(20) NOT NULL,
    Age NUMBER,
    Sex CHAR,
    Addr VARCHAR2(50),
    Contact NUMBER(10)
);

Inserting values using a pro*c method

addCustomer(i, name,age, gender, address,contectNo);

in Pro*C method I use following code to insert

EXEC SQL INSERT INTO REGISTRED_CUSTOMER VALUES  
(cust_id, cust_name, age, sex, addr,   contact);

here cust_name and addr are char *; and sex is char rest as int;

It reports error while using variable but works fine using direct values like EXEC SQL INSERT INTO REGISTRED_CUSTOMER VALUES (10, 'Pankaj', 23, 'M', 'asdfs', 45875);

I tried changing few lines but in vain.

Thanks in advance.

Answer 1

Your Pro*C code is basically missing the colons (assuming that your formal parameters are called cust_id, cust_name, age etc.):

EXEC SQL INSERT INTO REGISTRED_CUSTOMER VALUES  
    (:cust_id, :cust_name, :age, :sex, :addr, :contact);

And it would be more robust to explicitly specify the columns name. Otherwise a change to the table schema can result in difficult to find bugs:

EXEC SQL INSERT INTO REGISTRED_CUSTOMER (Cust_Id, Name, Ag, Sex, Addr, Contact)
    VALUES (:cust_id, :cust_name, :age, :sex, :addr, :contact);