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;
Try this.
BTW... Do not name your variables same as your table column names or any other objects in the database. Sooner or later it will bite you. That is why I appended an _ at the end of all variables. You can add some error handling like what to do if there are no records returned from next_number_gen etc. Also, you can use sequence to generate an incremental ID for you...which is pretty much gauranteed to be unique and you don't have to deal with a number generating table.

CREATE OR REPLACE PROCEDURE 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 )

   N1_ Next_Number_Gen.Next_Number%TYPE;    CURSOR cur_ IS

      SELECT NEXT_NUMBER
      FROM NEXT_NUMBER_GEN
      WHERE UPPER(TABLE_NAME) = 'V_CONTACT'
      FOR UPDATE NOWAIT;

   FETCH cur_ INTO N1_;
   CLOSE cur_;
   N1_ := N1_ + 1;

   UPDATE NEXT_NUMBER_GEN
      SET NEXT_NUMBER = N1_
      WHERE UPPER(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_, FIRST_NAME_, LAST_NAME_, TITLE_, BUS_PHONE_, EMAIL_ADDR_,
       WEB_PASSWORD_, WEB_USER_ID_, CUST_ID_, USER_T1_);

SHOW ERROR I would rate PLSQL developer http://www.allroundautomations.nl as #1 tool in the market for writing PLSQL code. It also includes a procedure debugger.

HTH
//Rauf Sarwar Received on Thu Jul 04 2002 - 07:44:38 CEST