Results 1 to 13 of 13

Thread: newbie database

  1. #1
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23

    Unanswered: newbie database

    I have created the following tables :

    CREATE TABLE OWNER
    (ID CHAR(7) NOT NULL,
    NAME VARCHAR(12),
    FNAME VARCHAR(20),
    CITY VARCHAR(10),
    PRIMARY KEY(ID) );

    CREATE TABLE CAR
    (COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    CAPACITY INT,
    YEAR_C INT,
    PRIMARY KEY(COMPANY,MODEL) );

    CREATE TABLE VEHICLE
    (LIC_NUM CHAR(7) NOT NULL,
    COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    COLOR VARCHAR(10),
    YEAR INT,
    PRIMARY KEY(LIC_NUM) ,
    FOREIGN KEY (COMPANY,MODEL) REFERENCES CAR(COMPANY,MODEL) );

    CREATE TABLE PROPERTY
    (ID CHAR(7) NOT NULL,
    LIC_NUM CHAR(7) NOT NULL,
    PRO_PERC INT,
    PRIMARY KEY(ID,LIC_NUM),
    FOREIGN KEY (ID) REFERENCES OWNER(ID),
    FOREIGN KEY (LIC_NUM) REFERENCES CAR(LIC_NUM) );

    and i want to make a trigger with the constraint that the year(YEAR) of a vehicle's(VEHICLE) license can't be smaller than it's car's(CAR) year(YEAR_C).
    I have wrote this but shows errors :

    create or replace trigger check_Y_VEH
    before insert or update of YEAR on VEHICLE
    for each row
    declare
    myyearc CAR.YEAR_C%TYPE;
    begin
    select YEAR_C into myyearc from CAR
    where CAR.COMPANY = :new.COMPANY and CAR.MODEL = :new.MODEL;
    if myyearc > :new.YEAR then
    raise application error(-20235, 'ERROR YEAR');
    end if ;
    end;
    /

    any ideas ??

    in table CAR why i can't write PRIMARY KEY(LIC_NUM) ON DELETE CASCADE instead of just PRIMARY KEY(LIC_NUM) ??

    Any help would be appreciated.
    Attached Thumbnails Attached Thumbnails capture.jpg  

  2. #2
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Thumbs up

    create or replace trigger check_Y_VEH
    before insert or update of YEAR on VEHICLE
    for each row
    declare
    myyearc CAR.YEAR_C%TYPE;
    begin
    select YEAR_C into myyearc from CAR
    where CAR.COMPANY = :new.COMPANY and CAR.MODEL = :new.MODEL;
    if myyearc > :new.YEAR then
    raise application error(-20235, 'ERROR YEAR');
    end if ;
    end;



    The code should be

    create or replace trigger check_Y_VEH
    before insert or update on VEHICLE
    for each row
    ..........

    begin
    .....
    end;
    Try changing like this without of col name on tab name
    but on tab name only.
    Regards,

    Rushi

  3. #3
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Nothing changed.
    I get the same errors:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/21 PLS-00103: access of symbol "ERROR" when i was waiting one of the following:
    . ;
    The symbol ";" has been raplaced with "ERROR" in order to continue.

    7/35 PLS-00103: access of symbol "ERROR" when i was waiting one of the following:
    ( - + case mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe

  4. #4
    Join Date
    Jan 2004
    Posts
    3

    Re: newbie database

    I think you should use Check constraint to do it instead of trigger, if you are checking the length of characters to be entered. Then you must use procision and scale in the datatype. It should be good to use VARCHAR2 instead of int or number datatype

    Originally posted by subwrc
    I have created the following tables :

    CREATE TABLE OWNER
    (ID CHAR(7) NOT NULL,
    NAME VARCHAR(12),
    FNAME VARCHAR(20),
    CITY VARCHAR(10),
    PRIMARY KEY(ID) );

    CREATE TABLE CAR
    (COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    CAPACITY INT,
    YEAR_C INT,
    PRIMARY KEY(COMPANY,MODEL) );

    CREATE TABLE VEHICLE
    (LIC_NUM CHAR(7) NOT NULL,
    COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    COLOR VARCHAR(10),
    YEAR INT,
    PRIMARY KEY(LIC_NUM) ,
    FOREIGN KEY (COMPANY,MODEL) REFERENCES CAR(COMPANY,MODEL) );

    CREATE TABLE PROPERTY
    (ID CHAR(7) NOT NULL,
    LIC_NUM CHAR(7) NOT NULL,
    PRO_PERC INT,
    PRIMARY KEY(ID,LIC_NUM),
    FOREIGN KEY (ID) REFERENCES OWNER(ID),
    FOREIGN KEY (LIC_NUM) REFERENCES CAR(LIC_NUM) );

    and i want to make a trigger with the constraint that the year(YEAR) of a vehicle's(VEHICLE) license can't be smaller than it's car's(CAR) year(YEAR_C).
    I have wrote this but shows errors :

    create or replace trigger check_Y_VEH
    before insert or update of YEAR on VEHICLE
    for each row
    declare
    myyearc CAR.YEAR_C%TYPE;
    begin
    select YEAR_C into myyearc from CAR
    where CAR.COMPANY = :new.COMPANY and CAR.MODEL = :new.MODEL;
    if myyearc > :new.YEAR then
    raise application error(-20235, 'ERROR YEAR');
    end if ;
    end;
    /

    any ideas ??

    in table CAR why i can't write PRIMARY KEY(LIC_NUM) ON DELETE CASCADE instead of just PRIMARY KEY(LIC_NUM) ??

    Any help would be appreciated.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    It should be raise_application_error not "raise application error".

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: newbie database

    Originally posted by subwrc
    in table CAR why i can't write PRIMARY KEY(LIC_NUM) ON DELETE CASCADE instead of just PRIMARY KEY(LIC_NUM) ??
    The ON DELETE CASCADE option goes on the foreign keys referencing this primary key, not on the primary key itself:

    CREATE TABLE PROPERTY
    (ID CHAR(7) NOT NULL,
    LIC_NUM CHAR(7) NOT NULL,
    PRO_PERC INT,
    PRIMARY KEY(ID,LIC_NUM),
    FOREIGN KEY (ID) REFERENCES OWNER(ID),
    FOREIGN KEY (LIC_NUM) REFERENCES CAR(LIC_NUM) ON DELETE CASCADE );

  7. #7
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    Hi,

    This error message is from the parser. It found a token (language element) that is inappropriate in this context.


    Originally posted by subwrc
    Nothing changed.
    I get the same errors:

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/21 PLS-00103: access of symbol "ERROR" when i was waiting one of the following:
    . ;
    The symbol ";" has been raplaced with "ERROR" in order to continue.

    7/35 PLS-00103: access of symbol "ERROR" when i was waiting one of the following:
    ( - + case mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe
    SATHISH .

  8. #8
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Thanks a lot guys for the answers!

    I did the "on delete cascade" as Andrew said and the "raise_application_error " as Todd said.
    Now with or without "of YEAR" in "before insert or update on OXIMA" the trigger gives me only one error but can't find the solution.

    LINE/COL ERROR
    -------- -----------------------------------------------------------------
    7/34 PLS-00103: access of symbol "ERROR" when i was waiting one of the following:
    ( - + case mod not null others <an identifier>
    <a double-quoted delimited-identifier> <a bind variable> avg
    count current exists max min prior sql stddev sum variance
    execute forall merge time timestamp interval date
    <a string literal with character set specification>
    <a number> <a single-quoted SQL string> pipe


    Chukws ,with the check constraint tha you said it is not allowed to refer to columns of other tables or to formulate queries as check conditions.So i can't use it.I 'm not checking the length.

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Please show your code again.

  10. #10
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Ooops!
    Now seems to work!
    The code that i present here is with english names for the columns while i use a greeklish names(greek with english characters)!
    So i have to check the names again!
    I 'll post again.

    CREATE TABLE OWNER
    (ID CHAR(7) NOT NULL,
    NAME VARCHAR(12),
    FNAME VARCHAR(20),
    CITY VARCHAR(10),
    PRIMARY KEY(ID) );

    CREATE TABLE CAR
    (COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    CAPACITY INT,
    YEAR_C INT,
    PRIMARY KEY(COMPANY,MODEL) );

    CREATE TABLE VEHICLE
    (LIC_NUM CHAR(7) NOT NULL,
    COMPANY VARCHAR(10) NOT NULL,
    MODEL VARCHAR(10) NOT NULL,
    COLOR VARCHAR(10),
    YEAR INT,
    PRIMARY KEY(LIC_NUM) ,
    FOREIGN KEY (COMPANY,MODEL) REFERENCES CAR(COMPANY,MODEL) );

    CREATE TABLE PROPERTY
    (ID CHAR(7) NOT NULL,
    LIC_NUM CHAR(7) NOT NULL,
    PRO_PERC INT,
    PRIMARY KEY(ID,LIC_NUM),
    FOREIGN KEY (ID) REFERENCES OWNER(ID),
    FOREIGN KEY (LIC_NUM) REFERENCES VEHICLE(LIC_NUM) on delete cascade );

    create or replace trigger check_Y_VEH
    before insert or update of YEAR on VEHICLE
    for each row
    declare
    myyearc CAR.YEAR_C%TYPE;
    begin
    select YEAR_C into myyearc from CAR
    where CAR.COMPANY = :new.COMPANY and CAR.MODEL = :new.MODEL;
    if myyearc > :new.YEAR then
    raise_application_error(-20235, 'ERROR YEAR');
    end if ;
    end;
    /

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    names are just labels. If it breaks again gives the real source not your anglicized version. (Hopefully it doesn't break again).

  12. #12
    Join Date
    Jan 2004
    Location
    Greece
    Posts
    23
    Ok works!

    Thanks a lot!

    now i have to load .txt files but i want to enter a name for the file each time i want to load one.
    Can i use SQL*Loader to make that or the name of the file to load can't be a variable ?

  13. #13
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Looking at the SQL*Loader doc is is not totally clear. It looks like you can load multiple files at once (it does not give an example however), but it does not say that you can specify on the command line. Are you on UNIX? How fancy are your scripting skills. I could see how you could create your control files on the fly and there by add your "inputed" input file name. Check the doc and good luck.

    HTH

Posting Permissions

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