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. Received on Mon Apr 30 2007 - 07:23:48 CDT