Newbie: Stored Proc Help/Debugging Needed
Date: Wed, 3 Jul 2002 16:29:12 -0400
Message-ID: <c9JU8.3362$P53.1177712_at_news20.bellglobal.com>
[Quoted] [Quoted] A few things. I'm having issues with Oracle SP syntax (Oracle 8.17). Are [Quoted] there any good GUI-based tools/debuggers you'd recommend? Also, below is my [Quoted] [Quoted] first stored proc that's coming up invalid. I'm trying to pass values from a [Quoted] web form, lock a table, select a specific value from it, iterate it, update [Quoted] the table, then insert the passed values plus the iterated value into [Quoted] 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 [Quoted] Errors" in DBA Studio isn't returning anything. I can get to the Lock [Quoted] statement fine. And the next SELECT works ok, independently, you add them [Quoted] 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; Received on Wed Jul 03 2002 - 22:29:12 CEST