Re: Newbie: Stored Proc Help/Debugging Needed

"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. Received on Thu Jul 04 2002 - 05:44:14 CEST