Re: Newbie: Stored Proc Help/Debugging Needed
Date: Thu, 04 Jul 2002 03:57:36 GMT
Message-ID: <QIPU8.8271$zGH.4658_at_news01.bloor.is.net.cable.rogers.com>
Jim,
Second:
Your problem can be solved for example like this:
assuming that you have created new schema and you are connected as new
object owner....
CREATE OR REPLACE PACKAGE UpdateMax AS
PROCEDURE ContactIDGenerator ( cFirst VARCHAR2,
First of all.
Create another schema to store all your objects.
Tables, Procedures ,Functions and Packages.
Do not use SYSADM schema as it is reserved for Oracle own objects.
cLast
VARCHAR2,
cTitle
VARCHAR2,
cBusPhone VARCHAR2,
cEmail
VARCHAR2,
cPass
VARCHAR2,
cUser
VARCHAR2,
cCustID
VARCHAR2,
cUserT1
VARCHAr2 );
END;
/
cEmail VARCHAR2, cPass VARCHAR2, cUser VARCHAR2, cCustID VARCHAR2, cUserT1VARCHAr2 ) AS
nNext NUMBER;
BEGIN
nNext := PB_NextNumber('V_CONTACT'); IF nNext<>-1 THEN nNext := nNext + 1; UPDATE NEXT_NUMBER_GEN SET NEXT_NUMBER=nNext 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 (nNext, cFirst, cLast,cTitle,cBudPhone,cEmail,cPass, cUser, cCustId,cUserT1); COMMIT; -- -- commit removes lock as well. -- ELSE NULL; -- -- Do something if this session cannot lock table -- END IF;
END; END;
/
HTH
Thomas Olszewicki
CPAS Systems Inc.
"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; > > >Received on Thu Jul 04 2002 - 05:57:36 CEST