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;
No GUI tool will make up for not reading manuals. The code above will turn your system in something *completely non-scalable* as you are locking a table exclusively (no need for that, Oracle has row-level locking) where you should have used a SEQUENCE in the first place.
Then: I amazed you are using _at_ signs in front of parameters. Obviously you never even looked at any stored procedure example. Remove them, they are the source of your error.
Also: select * from user_errors *always* works. Also: If you are planning to use this in a trigger: you need the sequence code only and you should remove the insert. A correct trigger fires BEFORE the row is inserted.
You should start reading the pl/sql reference manual and the Oracle application developers manual before resorting to use a GUI. In your case that won't help, as you have been brainwashed by sqlserver, and you think Oracle is sqlserver sold by a different vendor. It is -luckily - NOT.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Thu Jul 04 2002 - 09:48:15 CEST