Re: Here is a good one

Jeff Boyer wrote:

> Actually what I need to know is how to pass the ID back to my program. Can
> this be done without a procedure or function?
> "Markus Stuhlpfarrer" <mstuhlpfarrer_at_hollomey.com> wrote in message
> news:3B4CA484.52FEA6A1_at_hollomey.com...
> > a function would go like this:
> >
> > CREATE OR REPLACE FUNCTION Feedback_Id_Proc
> > RETURN varchar2;
> > Temp varchar2;
> > BEGIN
> > SELECT Feedback_ID_SEQ.NEXTVAL
> > INTO Temp FROM DUAL;
> > Temp := 'FB-'||Temp;
> > RETURN Temp;
> > END Feedback_Id_Proc;
> >
> > the question is, why you don't make the "select from dual" in your insert
> > that would be the same and you don't need a function or procedure
> >
> >
> > Jeff Boyer wrote:
> >
> > > Ok, I have figured out that I need to use a stored procedure so that I
 can
> > > return the value of the new feedback Id. However, I am confused as to
 how
> > > to return this value. This is the procedure I am using:
> > >
> > > CREATE OR REPLACE PROCEDURE Feedback_Id_Proc IS
> > > Temp varchar2(30);
> > > BEGIN
> > > SELECT Feedback_ID_SEQ.NEXTVAL
> > > INTO Temp FROM DUAL;
> > > Temp := 'FB-'||Temp;
> > > Insert into Feedback (Feedback_Id) Values (Temp);
> > > return(?????);
> > >
> > > End;
> > > /
> > >
> > > If someone could help me out with what I need to return it would be
 greatly
> > > appreciated.
> > >
> > > Thanks again,
> > > Jeff
> > >
> > > "Jeff Boyer" <jdboyer@(remove)icomproductions.ca> wrote in message
> > > news:9ihu7r$sm1$1_at_news3.cadvision.com...
> > > > Ok people, I am using Oracle 8i and for a front end I have a feedback
> > > > process online using VBScript. In this feedback process I have a form
 that
> > > > inserts all the information for the feedback into my feedback table.
> > > > However ,before any of it happens I have a trigger that inserts a
 feedback
> > > > id before anything is inserted. The insertion happens when the submit
> > > > button is pressed. What happens next is where I am stuck. When you
 hit
 the
> > > > submit button, you are taken to a web page that gathers a little more
> > > > information regarding contact information and so forth. On this page
 I
 need
> > > > to be able to update that record that was just created in the feedback
> > > > table. In order to do this I need to know that feedback Id that my
 trigger
> > > > just created for me. Is there any way at all to get that id back out
 of
 the
> > > > database? I have been banging my head against a wall for a week now
 and
> > > > can't seem to get it. Just for your information the trigger I use to
 insert
> > > > the feedback id is below.
> > > >
> > > > CREATE OR REPLACE TRIGGER Feedback_Id_trg
> > > > BEFORE INSERT on Feedback
> > > > FOR EACH ROW
> > > > DECLARE
> > > > Temp varchar2(30);
> > > > BEGIN
> > > > SELECT Feedback_ID_SEQ.NEXTVAL
> > > > INTO Temp FROM DUAL;
> > > > Temp := 'FB-'||Temp;
> > > > :New.Feedback_id := Temp;
> > > >
> > > > End;
> > > > /
> > > >
> > > >
> > > >
> > > >
> >

I don't know about passing the value back but I wonder why you are doing it. There is no need for your front-end to have the number to make it part of your insert statement. All you need to do is as follows:

INSERT INTO mytable
(field1, field2, field3)
VALUES
(mysequence.NEXTVAL, value2, value3);

Unless you are using the number in some other manner your processing is inefficient of both resources and i/o.

Daniel A. Morgan Received on Wed Jul 11 2001 - 16:44:49 CDT