Confused with Oracle Procedure with sequence, linking errors and filling null fields

Question

I am trying to make a procedure that takes makes potential empty "received" fields use the current date. I made a sequence called Order_number_seq that populates the order number (Ono) column. I don't know how to link errors in the orders table to a entry in the Orders_errors table.

this is what i have so far:

CREATE PROCEDURE Add_Order
  AS BEGIN
    UPDATE Orders
    CREATE Sequence Order_number_seq
    Start with 1,        
    Increment by 1;
    UPDATE Orders SET  received = GETDATE WHERE received = null;

These are the tables I am working with:

Orders table

(
 Ono Number Not Null,
 Cno Number Not Null,
 Eno  Number Not Null,
 Received Date Null,
 Shipped_Date Date Null,
 Creation_Date  Date Not Null,
 Created_By  VARCHAR2(10) Not Null,
 Last_Update_Date Date Not Null,
 Last_Updated_By VARCHAR2(10) Not Null,
 CONSTRAINT Ono_PK PRIMARY KEY (Ono), 
 CONSTRAINT Cno_FK FOREIGN KEY (Cno)
 REFERENCES Customers_Proj2 (Cno)
);

and

Order_Errors table

(
 Ono Number Not Null,
 Transaction_Date Date Not Null,
 Message  VARCHAR(100) Not Null
);

Any help is appreciated, especially on linking the orders table errors to create a new entry in OrderErrors table.

Thanks in advance.

Answer 1

Contrary to Martin Drautzburg's answer, there is no foreign key for the order number on the Order_Errors table. There is an Ono column which appears to serve that purpose, but it is not a foreign as far as Oracle is concerned. To make it a foreign key, you need to add a constraint much like the Cno_FK on Orders. An example:

CREATE TABLE Order_Errors
(
 Ono Number Not Null,
 Transaction_Date Date Not Null,
 Message  VARCHAR(100) Not Null,
 CONSTRAINT Order_Errors_Orders_FK FOREIGN KEY (Ono) REFERENCES Orders (Ono)
);

Or, if your Order_Errors table already exists and you don't want to drop it, you can use an ALTER TABLE statement:

ALTER TABLE Order_Errors
ADD CONSTRAINT Order_Errors_Orders_FK FOREIGN KEY (Ono) REFERENCES Orders (Ono)
;

As for the procedure, I'm inclined to say what you're trying to do does not lend itself well to a PROCEDURE. If your intention is that you want the row to use default values when inserted, a trigger is better suited for this purpose. (There is some performance hit to using a trigger, so that's a consideration.)

-- Create sequence to be used
CREATE SEQUENCE Order_Number_Sequence
START WITH 1
INCREMENT BY 1
/

-- Create trigger for insert
CREATE TRIGGER Orders_Insert_Trigger
BEFORE INSERT ON Orders
FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.Ono IS NULL
  THEN
    SELECT Order_Number_Sequence.NEXTVAL INTO :NEW.Ono FROM DUAL;
  END IF;
  IF :NEW.Received IS NULL
  THEN
    SELECT CURRENT_DATE INTO :NEW.O_Received FROM DUAL;
  END IF;
END;
/

This trigger will then be executed on every single row inserted into the Orders table. It checks if the Ono column was NULL and replaces it with an ID from the sequence if so. (Be careful that you don't ever provide an ID that will later be generated by the sequence; it will get a primary key conflict error.) It then checks if the received date is NULL and sets it to the current date, using the CURRENT_DATE function (which I believe was one of the things you were trying to figure out), if so.

(Side note: Other databases may not require a trigger to do this and instead could use a default value. I believe PostgreSQL, for instance, allows the use of function calls in its DEFAULT clauses, and that is how its SERIAL auto-increment type is implemented.)

If you are merely trying to update existing data, I would think the UPDATE statements by themselves would suffice. Is there a reason this needs to be a PROCEDURE?

One other note. Order_Errors has no primary key. You probably want to have an auto-incrementating surrogate key column, or at least create an index on its Ono column if you only ever intend to select off that column.

Answer 2

There are a number of confusing things in your question:

(1) You are creating a sequence inside a procedure. Does this even compile?

(2) Your procedure does not have any parameters. It just updates the RECEIVED column of all rows.

(3) You are not telling us what you want in the MESSAGE column.

My impression is that you should first go "back to the books" before you ask questions here.

As for your original question

how to link errors in the orders table to a entry in the Orders_errors table.

This is aleady (correctly) done. The Orders_error table contains an ONO foreign key which points to an order.