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

On Apr 30, 11:29 am, DA Morgan <damor..._at_psoug.org> wrote:
> 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,Ap­prover,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
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Hi DA Morgan,
Thanks for the reply. I used 2 procedures originally because I wasn't sure how to accomplish this in 1 procedure. To be honest I just managed to figure this out and it looks like it is definitely working now, however, I need your assistance and ask you and other experts what I can do in the below situation.

Now that I have the insert working, how can I say the following. Upon insert if the FirstName, LastName and Email address are the same do not insert into the Users table but insert into the other 2 tables all the info. plus the appropriate user id. So if Joe smith is the first to enter a request his UserID will be 1. Now if Joe smith enters another request his id should still remain 1 and this 1 will be captured in the other 2 tables and not UserID 2 as it is currently working. Here is my final insert that seems to work minus what I am asking.

[code]
CREATE OR REPLACE PROCEDURE test (User_ID OUT INT,

                                   Hardware_ID OUT INT,
                                   Approver_ID OUT INT,
                                   First_Name IN VARCHAR2,
                                   Last_Name IN VARCHAR2,
                                   Email IN VARCHAR2,
                                   Extension IN VARCHAR2,
                                   Last_Modified_By IN VARCHAR2,
                                   Date_Required IN DATE,
                                   Equipment IN VARCHAR2,
                                   Purpose IN VARCHAR2,
                                   Loan_Duration IN DATE,
                                   Approver_Name IN VARCHAR2)

IS
BEGIN
  INSERT INTO Users (User_ID, First_Name, Last_Name, Email, Extension, Create_Date, Last_Modified_By)
VALUES Corp_UserID_seq.nextval, First_Name, Last_Name, Email, Extension, sysdate, Last_Modified_By)
  RETURNING User_ID INTO User_ID;
  INSERT INTO Hardware (Hardware_ID, User_ID, Date_Required, Description, Purpose, Duration, Create_Date, Last_Modified_By)   VALUES(Corp_HardwareID_seq.nextval, User_ID, Date_Required, Equipment, Purpose, Loan_Duration, sysdate,Last_Modified_By)   RETURNING Hardware_ID INTO Hardware_ID;   INSERT INTO Approver (Approver_ID, Hardware_ID, User_ID, Approver_Name, Status, Create_Date, Last_Modified_By)   VALUES(Corp_ApproverID_seq.nextval, Hardware_ID, User_ID, Approver_Name, NULL, sysdate,Last_Modified_By)   RETURNING Approver_ID INTO Approver_ID; COMMIT;
END;
[/code]

Thanks again all. Received on Mon Apr 30 2007 - 10:47:20 CDT