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,
First of all.
[Quoted] 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.
Second:
Read some of the manuals (HTML help) about Pl/Sql programming as it is very
[Quoted] different from TSQL.
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,
cLast
VARCHAR2,
cTitle
VARCHAR2,
cBusPhone VARCHAR2,
cEmail
VARCHAR2,
cPass
VARCHAR2,
cUser
VARCHAR2,
cCustID
VARCHAR2,
cUserT1
VARCHAr2 );
END;
/
[Quoted] CREATE OR REPLACE PACKAGE BODY UpdateMax AS
--
- Local function: can be useed only inside package body.
--
FUNCTION PB_NextNumber( cTable VARCHAR2) RETURN NUMBER AS --- Lock table and return number -- nRtn NUMBER := -1; CURSOR curNext IS SELECT NEXT_NUMBER FROM NEXT_NUMBER_GEN WHERE TABLE_NAME =cTable FOR UPDATE NOWAIT; recNext curNext%ROWTYPE; BEGIN OPEN curNext; FETCH curNext INTO recNext; IF curNext%FOUND THEN nRtn := recNext.NEXT_NUMBER; END IF; CLOSE curNext; RETURN nRtn; END;
--
- Implementation of your public procedure;
--
PROCEDURE ContactIDGenerator ( cFirst VARCHAR2, cLast VARCHAR2, cTitle VARCHAR2,
cEmail
VARCHAR2,
cPass
VARCHAR2,
cUser
VARCHAR2,
cCustID
VARCHAR2,
cUserT1
VARCHAr2 ) AS
nNext NUMBER;
BEGIN
[Quoted] 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...
[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] > 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. > [Quoted] [Quoted] > 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