Results 1 to 3 of 3

Thread: ORA-00904 error

  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: ORA-00904 error

    hi all i have the following program in pro*C and when i try to insert something in the table owner i get a ORA-00904 invalid column name,and i can't find out why no matter how many changes i make to the code.

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

    #define UNAME_LEN 20
    #define PWD_LEN 11

    typedef char asciiz[PWD_LEN];
    EXEC SQL TYPE asciiz IS CHARZ(PWD_LEN) REFERENCE;
    asciiz username;
    asciiz password;

    EXEC SQL include SQLCA;

    void do_connect();
    void sql_error(char* routine);
    int LicenceNumberCheck(char *licencenumber);
    int IDcheck(char *id) ;


    int main(void) {

    int i;
    varchar licencenumber[7];
    varchar company[10];
    varchar model[10];
    varchar color[15];
    int licenceyear;
    varchar id[7];
    varchar name[12];
    varchar surname[20];
    varchar town[10];
    int ownerdegree;

    char c;
    int sum;

    /* Connect to ORACLE. */
    do_connect();


    label1:
    printf("Give the licence number(7 chars)");
    scanf("%s",licencenumber.arr);
    licencenumber.len=strlen((char *)licencenumber.arr);
    if((licencenumber.len!=7) || !LicenceNumberCheck((char *)licencenumber.arr)) {
    printf("False licence number\n");
    goto label1;
    }
    for(i=0;i<licencenumber.len;i++)
    licencenumber.arr[i]=toupper(licencenumber.arr[i]);

    printf("Give the firm name(10 chars maximum):");
    scanf("%s",company.arr);
    company.len=strlen((char *)company.arr);
    for(i=0;i<company.len;i++)
    company.arr[i]=toupper(company.arr[i]);

    printf("Give car's model(10 chars maximum):");
    scanf("%s",model.arr);
    model.len=strlen((char *)model.arr);
    for(i=0;i<model.len;i++)
    model.arr[i]=toupper(model.arr[i]);

    printf("Give car's color(q otherwise):");
    scanf("%s",color.arr);
    if(!strcmp((char *)color.arr,"q"))
    color.len=0;
    else {
    color.len=strlen((char *)color.arr);
    for(i=0;i<color.len;i++)
    color.arr[i]=toupper(color.arr[i]);
    }

    printf("Give licence's number year:");
    scanf("%d",&licenceyear);

    EXEC SQL WHENEVER SQLERROR DO sql_error("VEHICLE insert error");
    EXEC SQL INSERT INTO VEHICLE
    VALUES(:licencenumber,:company,:model,:color,:lice nceyear);
    EXEC SQL COMMIT;
    do {
    label2:
    printf("Give owner's id:");
    scanf("%s",id.arr);
    id.len=strlen((char *)id.arr);
    if((id.len!=7) || !IDcheck((char *)id.arr)) {
    printf("False id\n");
    goto label2;
    }
    for(i=0;i<id.len;i++)
    id.arr[i]=toupper(id.arr[i]);

    printf("Give owner's name:");
    scanf("%s",name.arr);
    name.len=strlen((char *)name.arr);
    for(i=0;i<name.len;i++)
    name.arr[i]=toupper(name.arr[i]);

    printf("Give owner's surname:");
    scanf("%s",surname.arr);
    surname.len=strlen((char *)surname.arr);
    for(i=0;i<surname.len;i++)
    surname.arr[i]=toupper(surname.arr[i]);

    printf("Give owner's hometown(q otherwise):");
    scanf("%s",town.arr);
    if(!strcmp((char *)town.arr,"q"))
    town.len=0;
    else {
    town.len=strlen((char *)town.arr);
    for(i=0;i<town.len;i++)
    town.arr[i]=toupper(town.arr[i]);
    }

    EXEC SQL WHENEVER SQLERROR DO sql_error("OWNER insert error:");
    EXEC SQL INSERT INTO OWNER
    VALUES(:id,:name,:surname,:town);

    printf("Give ownership degree on the vehicle:");
    scanf("%d",&ownerdegree);

    EXEC SQL WHENEVER SQLERROR DO sql_error("OWNERSHIP insert error:");
    EXEC SQL INSERT INTO OWNERSHIP
    VALUES(:licencenumber,:id,wnerdegree);

    EXEC SQL SELECT SUM(DEGREE) INTO :sum FROM OWNERSHIP
    WHERE OWNERSHIP.LICENCENUMBER=:licencenumber;

    if(sum>100) {
    printf("TOtal ownership degree cannot be over 100%\n");
    EXEC SQL ROLLBACK;
    }

    EXEC SQL COMMIT;
    printf("Wanna insert another owner(y/n)?");
    scanf("%c",&c);
    }while(c=='y');

    printf("Values inserted\n");

    /* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(EXIT_SUCCESS);
    }

    /* Connect to the database. */
    void do_connect()
    {
    strcpy(username,"????????");
    strcpy(password,"????????");

    EXEC SQL WHENEVER SQLERROR DO sql_error("do_connect():CONNECT");
    EXEC SQL CONNECT :username IDENTIFIED BY assword;

    printf("Connected to ORACLE as user %s\n",username);
    }

    void sql_error(char* routine)
    {
    char message_buffer[512];
    size_t buffer_size;
    size_t message_length;

    /* Turn off the call to sql_error() to avoid a possible infinite loop */
    EXEC SQL WHENEVER SQLERROR CONTINUE;

    printf("\nOracle error while executing %s!\n", routine);

    /* Use sqlglm() to get the full text of the error message. */
    buffer_size = sizeof(message_buffer);
    sqlglm(message_buffer, &buffer_size, &message_length);
    printf("%.*s\n", message_length, message_buffer);

    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_FAILURE);
    }

    int LicenceNumberCheck(char *licencenumber)
    {

    int i,k=0;

    for(i=0;i<3;i++) {
    if(isalpha(licencenumber[i]))
    k=1;
    else {
    k=0;
    return k;
    }
    }
    for(i=3;i<strlen(licencenumber);i++) {
    if(isdigit(licencenumber[i]))
    k=1;
    else {
    k=0;
    return k;
    }
    }
    return k;

    }

    int IDcheck(char *id)
    {

    int i,k=0;

    if(isalpha(id[0]))
    k=1;
    else {
    k=0;
    return k;
    }

    for(i=1;i<strlen(id);i++) {
    if(isdigit(id[i]))
    k=1;
    else {
    k=0;
    return k;
    }
    }
    return k;

    }

    My database:
    CREATE TABLE OWNER
    (ID VARCHAR(7) NOT NULL,
    NAME VARCHAR(12) NOT NULL,
    SURNAME VARCHAR(20) NOT NULL,
    TOWN VARCHAR(10),
    PRIMARY KEY(ID));

    CREATE TABLE CAR
    (FIRM VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    CUBICS INT,
    MADEYEAR INT NOT NULL,
    PRIMARY KEY(FIRM,MODEL));

    CREATE TABLE VEHICLE
    (LICENCE VARCHAR(7) NOT NULL,
    FIRM VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    COLOR VARCHAR(15),
    YEAR INT NOT NULL,
    PRIMARY KEY(LICENCE),
    FOREIGN KEY(FIRM,MODEL) REFERENCES CAR(FIRM,MODEL)
    ON DELETE SET NULL);

    CREATE TABLE OWNERSHIP
    (LICENCENUMBER VARCHAR(7) NOT NULL,
    ID VARCHAR(7) NOT NULL,
    OWNERDEGREE INT
    Constraint posostocheck check(OWNERDEGREE<=100),
    PRIMARY KEY(LICENCENUMBER,ID),
    FOREIGN KEY(LICENCENUMBER) REFERENCES OXIMA(LICENCE),
    FOREIGN KEY(ID) REFERENCES IDIOKTITIS(ID)
    ON DELETE SET NULL);


    CREATE OR REPLACE TRIGGER LICENCENUMBERCHECK
    BEFORE INSERT OR UPDATE ON VEHICLE
    FOR EACH ROW
    declare NMADEYEAR CAR.MADEYEAR%TYPE;
    BEGIN
    SELECT MADEYEAR INTO NMADEYEAR FROM CAR
    WHERE FIRM=:new.FIRM
    AND MODEL=:new.MODEL;
    IF(:new.YEAR < NMADEYEAR ) THEN
    RAISE_APPLICATION_ERROR(-20000,'LICENCE NUMBER SHOULD NOT BE ISSUED BEFORE THE DATE THE CAR WAS MADE');
    END IF;
    END;
    .
    run;


    Any suggestions would be very much appreciated.
    Thanx in advance.

  2. #2
    Join Date
    Jan 2004
    Posts
    370
    EXEC SQL SELECT SUM(DEGREE) INTO :sum FROM OWNERSHIP
    WHERE OWNERSHIP.LICENCENUMBER=:licencenumber;

    Perhaps:
    EXEC SQL SELECT SUM(OWNERDEGREE) INTO :sum FROM OWNERSHIP
    WHERE OWNERSHIP.LICENCENUMBER=:licencenumber;

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    Originally posted by SkyWriter
    EXEC SQL SELECT SUM(DEGREE) INTO :sum FROM OWNERSHIP
    WHERE OWNERSHIP.LICENCENUMBER=:licencenumber;

    Perhaps:
    EXEC SQL SELECT SUM(OWNERDEGREE) INTO :sum FROM OWNERSHIP
    WHERE OWNERSHIP.LICENCENUMBER=:licencenumber;
    Yes that was the problem.
    Thanx very much.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •