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

If im seeing correct you are trying to insert into the columns, the columns??

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

it would be more helpful if you post your procedure complete.

Regards

Answer 2

As Mr. mentioned, you are trying to use the columns as input values. When you provide actual values it works. Are you perhaps meaning to use PL/SQL variables or the procedure arguments? In this case, whatever your procedure parameters are called is what you should put in the values section.

i.e if addCustomer looks like

PROCEDURE addCustomer (pId NUMBER, pName VARCHAR2, pAge NUMBER, pGender CHAR, pAddress VARCHAR2, pContact NUMBER)

Then you'd do something like

INSERT INTO registered_customer (cust_id, name, age, sex, addr, contact) VALUES (pId, pName, pAge, pGender, pAddress, pContact);

But if you are inserting into all columns you can leave out the column definition and just provide values

Answer 3

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);

Answer 4

I also got this error message in a stored procedure doing an insert. I misspelled a parameter name in the values clause and the oracle interpreter saw the misspelled name as a column name and issued the 00984.