Re: Newbie: Stored Proc Help/Debugging Needed

GP, thanks.

The DB itself is 20 years old, I'd love to add a Sequence but the problem is an app uses the NEXT_NUMBER_GEN table as a repository for the..you guessed it, NEXT_NUMBER. An app programatically does what I'm trying to get the SP to do so I need to ref that table. You've givem me a lot of help though, thanks.

Cheers,
Jim

"GP" <g_palgrave_at_yahoo.com> wrote in message news:680a4735.0207031944.2a406b00_at_posting.google.com... > "Jim Alemany" <jalemany_at_sandvine.com> wrote in message  news:<c9JU8.3362$P53.1177712_at_news20.bellglobal.com>...
> > A few things. I'm having issues with Oracle SP syntax (Oracle 8.17). Are
> > there any good GUI-based tools/debuggers you'd recommend? Also, below is
 my
> > first stored proc that's coming up invalid. I'm trying to pass values
 from a
> > web form, lock a table, select a specific value from it, iterate it,
 update
> > the table, then insert the passed values plus the iterated value into
> > another table. Old db design didn't use triggers for IDs so I have to
> > replicate what the app front end is doing. Its crapping out but the
 "Show
> > Errors" in DBA Studio isn't returning anything. I can get to the Lock
> > statement fine. And the next SELECT works ok, independently, you add
 them
> > together and it barfs.
> >
> > Any/all help is greatly appreciated.
> >
> > Cheers,
> > Jim
> >
> >
> >
> > CREATE OR REPLACE PROCEDURE "SYSADM"."SP_CONTACT_ID_GEN"
> > (
> > FIRST_NAME IN varchar2,
> > LAST_NAME IN varchar2,
> > TITLE IN varchar2,
> > BUS_PHONE IN varchar2,
> > EMAIL_ADDR IN varchar2,
> > WEB_PASSWORD IN varchar2,
> > WEB_USER_ID IN varchar2,
> > CUST_ID IN varchar2,
> > User_T1 IN varchar2
> > )
> >
> > AS
> > BEGIN
> >
> >
> > LOCK TABLE NEXT_NUMBER_GEN in EXCLUSIVE MODE;
> >
> >
> > SELECT NEXT_NUMBER AS N1
> > FROM NEXT_NUMBER_GEN
> > WHERE TABLE_NAME =V_CONTACT
> >
> >
> > N1:=N1+1;
> >
> >
> > UPDATE NEXT_NUMBER_GEN
> > SET NEXT_NUMBER = N1
> > WHERE TABLE_NAME=V_CONTACT;
> >
> >
> > INSERT INTO V_CONTACT
> > (ID, FIRST_NAME, LAST_NAME, TITLE, BUS_PHONE, EMAIL_ADDR, WEB_PASSWORD,
> > WEB_USER_ID, CUST_ID, USER_T1)
> > VALUES
> > (N1, _at_FIRST_NAME, @LAST_NAME, @TITLE, @BUS_PHONE, @EMAIL_ADDR,
> > _at_WEB_PASSWORD, @WEB_USER_ID, @CUST_ID, @USER_T1);
> >
> > END;
> >
> > COMMIT;

>
>
> I don't htink you need the '_at_' in front of the variable names - that
> looks like you're converting from MSSQL... and I'd suggest you lose
> the NEXT_NUMBER_GEN table. Look at CREATE SEQUENCE in the SQL
> REFERENCE and how to use them, so the ID column is then populated
> using the SEQUENCE value. You'll end up with something like:
>
> CREATE SEQUENCE IDNO
> INCREMENT BY 1;
>
> Then use
>
> CREATE OR REPLACE  PROCEDURE SYSADM.SP_CONTACT_ID_GEN
>  (
>  FIRST_NAME IN varchar2,
>  LAST_NAME IN varchar2,
>  TITLE IN varchar2,
>  BUS_PHONE IN varchar2,
>  EMAIL_ADDR IN varchar2,
>  WEB_PASSWORD IN varchar2,
>  WEB_USER_ID IN varchar2,
>  CUST_ID IN varchar2,
>  User_T1 IN varchar2
>  )
>
> AS
>
> BEGIN
>
> INSERT INTO V_CONTACT
>   (ID, FIRST_NAME, LAST_NAME, TITLE, BUS_PHONE, EMAIL_ADDR,
> EB_PASSWORD,
>   WEB_USER_ID, CUST_ID, USER_T1)
> VALUES
>  (IDNO.NEXTVAL, FIRST_NAME, LAST_NAME, TITLE, BUS_PHONE, EMAIL_ADDR,
>   WEB_PASSWORD, WEB_USER_ID, CUST_ID, USER_T1);
>
>  END;
>
> COMMIT;
>
> Sample proc from the SQL Ref manual:
>
> CREATE PROCEDURE sam.credit (acc_no IN NUMBER, amount IN NUMBER) AS
> BEGIN
> UPDATE accounts
> SET balance = balance + amount
> WHERE account_id = acc_no;
> END;
>
> As always, RTFM first.
>
> Hope that helps.