Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13

    Unanswered: Error messages ?????

    Hi
    I am learning Oracle, I am trying to insert data but i am getting this error message I am really not sure what I should do.
    The query1 is

    SQL> Create table sales_order_details (order_no varchar2(6), product_no varchar2
    (6), qty_ordered number(8), qty_disp number(8), product_rate number(10,2),CONSTR
    AINT pk_or_pr_no PRIMARY KEY(order_no, product_no), CONSTRAINT fk_orderno FOREIG
    N KEY(order_no) REFERENCES sales_order(order_no), CONSTRAINT fk_prno FOREIGN KEY
    (product_no) REFERENCES product_master);


    SQL> Insert into sales_order_details values ('&order_no','&product_no', '&qty_or
    dered','&qty_disp','&product_rate');
    Enter value for order_no: O19001
    Enter value for product_no: P00001
    Enter value for qty_ordered: 4
    Enter value for qty_disp: 4
    Enter value for product_rate: 525
    old 1: Insert into sales_order_details values ('&order_no','&product_no', '&qt
    y_ordered','&qty_disp','&product_rate')
    new 1: Insert into sales_order_details values ('O19001','P00001', '4','4','525
    ')
    Insert into sales_order_details values ('O19001','P00001', '4','4','525')
    *
    ERROR at line 1:
    ORA-02291: integrity constraint (SCOTT.FK_ORDERNO) violated - parent key not found

    This is one error and for another table
    Query 2:

    SQL> Insert into sales_order Values ('&order_no','&order_date','&client_no','&de
    ly_addr','&salesman_no','&dely_type','&billed_yn', '&dely_date','&order_status');


    SQL> Insert into sales_order Values ('&order_no','&order_date','&client_no','
    ly_addr','&salesman_no','&dely_type','&billed_yn', '&dely_date','&order_status

    Enter value for order_no: O19001
    Enter value for order_date: 12-Jan-96
    Enter value for client_no: C00001
    Enter value for dely_addr:
    Enter value for salesman_no: S00001
    Enter value for dely_type: F
    Enter value for billed_yn: N
    Enter value for d

    ERROR at line 1:
    RA-02290: check constraint (SCOTT.CK_DELYDATE) violated

    Please help me..
    Thanks in advance

  2. #2
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    The error you get in query 1 is telling you that you are trying to insert something in a table but some references in other tables are missing.
    In the table sales_order_details you created two foreign keys: thi means that values for these two columns must exist in the referenced tables. You must first insert a value in the referenced table and then in sales_order_details (you cannot insert a detail for an order if the order itself does not exist... or the product does not exist...)

    Query 2 is a bit different: there is something wrong that is checked by another integrity constraint (ck_delydate).
    Try to execute
    SELECT * FROM USER_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CK_DELYDATE'
    or
    SELECT * FROM DBA_CONSTRAINTS WHERE CONSTRAINT_NAME = 'CK_DELYDATE' AND OWNER = 'SCOTT' -- if you have the privileges

    The field SEARCH_CONDITION should tell you what is wrong (the check that is being violated).

    hope this helps.
    astropp

  3. #3
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13
    HI
    Thanks for the help
    I found my mistake...
    but is does not end.. I am still stuck with something..I am doing everything right i think...
    the query is
    SQL> insert into sales_order values ('&order_no','&order_date','&client_no','&de
    ly_type','&billed_yn','&salesman_no','&dely_date', '&order_status', '&dely_addr')
    ;
    Enter value for order_no: O19001
    Enter value for order_date: 12-jan-96
    Enter value for client_no: C00001
    Enter value for dely_type: F
    Enter value for billed_yn: N
    Enter value for salesman_no: S00001
    Enter value for dely_date: 20-Jan-96
    Enter value for order_status: In process
    Enter value for dely_addr:
    old 1: insert into sales_order values ('&order_no','&order_date','&client_no',
    '&dely_type','&billed_yn','&salesman_no','&dely_da te','&order_status', '&dely_ad
    dr')
    new 1: insert into sales_order values ('O19001','12-jan-96','C00001','F','N','
    S00001','20-Jan-96','In process', '')
    insert into sales_order values ('O19001','12-jan-96','C00001','F','N','S00001','
    20-Jan-96','In process', '')
    *
    ERROR at line 1:
    ORA-01401: inserted value too large for column

    Thanks

  4. #4
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    try to describe the table where you are issuing the statement:

    SQL>desc sales_order

    this should report the names,the datatypes, the dimension and the null/not null constraint on all the columns of the table.

    Then check the values you are inserting against the values defined for the columns defined when the table was created.

    ORA-01401 means that you are inserting a value in a column but the value is too large... like trying to write 'HELLO' in a varchar2(4) or 1000 in a number(2).

    let me know if it works
    astropp

  5. #5
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13
    Hi
    Thanks again,
    but i tried but still showing the same error,
    desc sales_order;
    gives this result..but still not working...do i have to something witht he date, i mean like set date....
    the output of the query is
    SQL> desc sales_order
    Name Null? Type
    ----------------------------------------------------- -------- ---------------
    --------------------
    ORDER_NO NOT NULL VARCHAR2(6)
    ORDER_DATE DATE
    CLIENT_NO VARCHAR2(6)
    DELY_ADDR VARCHAR2(25)
    SALESMAN_NO VARCHAR2(6)
    DELY_TYPE CHAR(1)
    BILLED_YN CHAR(1)
    DELY_DATE DATE
    ORDER_STATUS VARCHAR2(10)

    Thanks for your patience

  6. #6
    Join Date
    Jan 2002
    Location
    italy
    Posts
    39
    I copied the values in the order you wrote and the describe of the table...
    The order of the fields you type must match the order of the columns on the table.

    insert into sales_order values (
    'O19001', -- ORDER_NO NOT NULL VARCHAR2(6)
    '12-jan-96', -- ORDER_DATE DATE
    'C00001', -- CLIENT_NO VARCHAR2(6)
    'F', -- DELY_ADDR VARCHAR2(25)
    'N', -- SALESMAN_NO VARCHAR2(6)
    'S00001', -- DELY_TYPE CHAR(1)
    '20-Jan-96', -- BILLED_YN CHAR(1)
    'In process', -- DELY_DATE DATE

    '' -- ORDER_STATUS VARCHAR2(10)
    )


    By the way, you'd better write your insert this way:
    insert into sales_order (ORDER_NO
    ,ORDER_DATE
    ,CLIENT_NO
    ,DELY_ADDR
    ,SALESMAN_NO
    ,DELY_TYPE
    ,BILLED_YN
    ,DELY_DATE
    ,ORDER_STATUS) values (...)

    so that if you add or remove columns from the table your code will still work.

    we're just a step from our goal
    astropp

  7. #7
    Join Date
    Nov 2001
    Location
    Baton Rouge
    Posts
    13
    Hi
    Thank you very much..it worked...i followed my mistake...


Posting Permissions

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