| Returning a recordset from an insert proc [message #7679] |
Mon, 30 June 2003 13:46  |
Joël
Messages: 23
Registered: August 2002
|
Junior Member |
|
|
Hello all,
I need to return the last value used in a sequence from an INSERT proc. I tried to SELECT MAX(ID) FROM table after the INSERT statement as follows:
CREATE OR REPLACE Procedure usp_RollupInsertJob(
VchDescription IN DIM_RollupJob.VCH_DESCRIPTION%TYPE,
VchManager IN DIM_RollupJob.VCH_MANAGER%TYPE,
VchStatus IN DIM_RollupJob.VCH_STATUS%TYPE,
VchStartTime IN DIM_RollupJob.VCH_STARTTIME%TYPE,
VchEndTime IN DIM_RollupJob.VCH_ENDTIME%TYPE)
AS
BEGIN
INSERT INTO DIM_RollupJob (N_JobID, Vch_Description, Vch_Manager, Vch_Status,Vch_StartTime, Vch_EndTime)
VALUES (SEQ_JobID.nextval, VchDescription, VchManager, VchStatus, VchStartTime, VchEndTime);
SELECT MAX(N_JobID) FROM DIM_RollupJob;
END;
Output parameters are NOT an option, unfortunately. I need to be able to programatically access a recordset.
Any suggestions?
Thanks,
Joel Scavone
|
|
|
|
| Re: Returning a recordset from an insert proc [message #7680 is a reply to message #7679] |
Mon, 30 June 2003 15:07  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Several issues here Joel.
1) The use of SELECT MAX to return the just-inserted sequence value is not a valid approach in a multi-user environment. With concurrent inserts, you are likely to get a value inserted by someone else.
2) A SELECT statement (with no INTO clause) is invalid in PL/SQL.
3) Why is an OUT parameter not an option? You are writing in PL/SQL here - not sure why you are eliminating a fundamental feature.
4) The only way to return the value issued by the sequence - short of assigning the value prior to the insert statement - is the RETURNING clause:
insert into ... values (...) returning n_jobid into v_n_jobid;
But you have no way to return this to the calling program without using an OUT parameter.
|
|
|
|
| Re: Returning a recordset from an insert proc [message #7681 is a reply to message #7680] |
Mon, 30 June 2003 15:47 |
Joël
Messages: 23
Registered: August 2002
|
Junior Member |
|
|
Todd,
Thanks.
I wouldn't use this approach if I wasn't POSITIVE my process was the ONLY one inserting.
Our ADO code consistently crashes with Output parameters and the OLEDB Provider for Oracle.
I'm a reasonably seasoned T-SQL cat, and I'm tearing my hair out with PL/SQL. What is the best book you would recommend that's NOT from Oracle Press? -- I'm not a big fan of MS Press either; I don't like to buy books from the creater of the technology-- they're usually a bit biased.
|
|
|
|
|
|
| Re: Returning a recordset from an insert proc [message #7683 is a reply to message #7681] |
Mon, 30 June 2003 17:05 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
OK, well why a stored proc at all then given the middleware bug issues? Why not just:
insert into ... values .. returning n_jobid into :x;
in your ADO code? I mean, I'm a huge advocate of DML only happening in backend (PL/SQL) code, but with your bug issues, why not just deal with it at the source?
|
|
|
|
| Re: Returning a recordset from an insert proc [message #7691 is a reply to message #7683] |
Tue, 01 July 2003 08:51 |
Joël
Messages: 23
Registered: August 2002
|
Junior Member |
|
|
I'd agree. Architecturally, We're trying to keep a loose coupling between the business tier and data tier. We're trying to maintain parallel code for Oracle and SQL Server.
However, you make a good point. I was unfamiliar with the RETURNING clause. I'm not accustomed to the OO nature of PL/SQL.
I'm going to look into that as an option given our setting. Thanks for the info. You've been helpful.
Joel
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
