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

MrHelpMe wrote:
> 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.

Why two procedures and not one?

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org