Re: Newbie: Stored Proc Help/Debugging Needed

Sybrand,

Thanks for the flame. You are correct, I do use SQL Server quite a bit more out of convenience and am used to qui-based query builders so I had hoped that perhaps more sophisticated tools thatn DBA Studio and SQL+ may be out there(ok, easier to use). Due to the nature of this project I need to use our Oracle server which runs some of our business systems.

Firstly, this is week 2 working with Oracle and I don't pretend to be a DBA, whatever the db involved. I did miss the _at_ symbols, most of the stored proc tutorials I've found online have not been exactly Oracle related/friendly. I have been trying to read the manuals but you know impossible deadlines....and we don't have a dedicated dba here who fully groks Oracle. Lastly, I'm a Mac user, stuck in a W2K world forced to use the less than straightforward tools offered by Oracle with limited knowledge of PL/SQL on a database that's 20 years old, and even I can recognize that the design our business system vendor has put in place is total crap, but that its a legacy crap so it has an aromatic smell to it and its something I have to work with, and work around without breaking it.

I appreciate the obvious thought, candor and time you put into your flame. Thanks for being that necessary 15% who would rather complain than help and educate. To all the others that sent me helpful advice, you bet I'll be reading some manuals, I'd have done more if I didn't have next week as a deadline.

Cheers,
Jim

"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message news:a20d28ee.0207032348.1e2142e8_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;

>
>
>
> 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