Please help with Inserting ID into table from another table in Oracle 9.2

Good day everyone,

I have 3 tables. A user table and Hardware table and approver
table(Please correct the design if you see any issues.

[code]
Users Table

User_ID INT,
First_Name VARCHAR2
Last_Name VARCHAR2
Email VARCHAR2
Extension VARCHAR2
Create_Date DATE
Last_Modified_By VARCHAR2
[/code]

Second Table
[code]
Hardware Table

Hardware_ID INT
User_ID INT
Date_Required DATE
Equipment VARCHAR2
Purpose VARCHAR2
Duration DATE
Create_Date DATE
Last_Modified_By VARCHAR2
[/code]

I have been thinking of a third table for normalization called
Approver for approver info as follows:

[code]
Approver

Hardware_ID INT
User_ID INT
Approver_ID INT
Approver_Name VARCHAR2
Status INT
[/code]


A User comes in a fills out a form and then clicks submit. On submit
(this is where I am stuck) I created 2 procedures to do my inserts:

[code]
1st procedure
CREATE OR REPLACE PROCEDURE User_Info_Insert
(User_ID OUT INT,
First_Name IN VARCHAR2,
Last_Name IN VARCHAR2,
Email IN VARCHAR2,
Extension IN VARCHAR2,
Last_Modified_By IN VARCHAR2)

IS
BEGIN
insert into Users values
(Corp_UserID_seq.nextval,First_Name,Last_Name,Email,Extension,sysdate,Last_Modified_By)
Returning User_ID INTO User_ID;
END;
[/Code]

2nd procedure
[code]
CREATE OR REPLACE PROCEDURE
Hardware_Info_Insert (DateRequired IN DATE,
Equipment IN VARCHAR2,
Purpose IN VARCHAR2,
LoanDuration IN DATE,
Approver IN VARCHAR2,
LastModifiedBy IN VARCHAR2)

IS
begin
insert into Hardware_Desc values
(Corp_HardwareID_seq.nextval,DateRequired,Equipment,Purpose,LoanDuration,Approver,sysdate,LastModifiedBy);
end;

3rd will follow and will contain the approver info.
[/code]

I need to get the User_ID( Returning User_ID INTO User_ID) from the
first insert to also be added to the hardware table and the approver
table?. Is this possible? Unless I can just run one big insert that
will complete these 3 inserts. Could someone please show me by
example how to do this? Please please please your help is really
appreciated. Thanks everyone.

.

Relevant Pages