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

On Apr 30, 11:29 am, DA Morgan <damor...@xxxxxxxxx> 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...@xxxxxxxxxxxxxxxx
(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.

.

Relevant Pages