Re: Newbie: Stored Proc Help/Debugging Needed

Hey Thomas,

I'm actually using a separate test schema and do have another login with more restricted rights that I will eventually be using, the schema's been under renovation while I've been doing this development so and that login kept going on/off so I decided to work with sysadm. And you bet I'll be reading up more on PL/SQL, anything I can do to make this creaky old db work better will help everyone (or at least help me not break it too badly). Fact of the matter is SPs/Triggers/Sequences aren't used well in the design, a lot of the manipulation, like the one I'm atempting is done programmatically. Yep, makes me shake my head too, done properly, this SP wouldn't be necessary. Thanks for your reply, I'll pour over it and see what I can come up with.

Cheers,
Jim

"Thomas Olszewicki" <ThomasO_at_noSpm.cpas.com> wrote in message news:QIPU8.8271$zGH.4658_at_news01.bloor.is.net.cable.rogers.com...
> Jim,
> 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.
>
> Second:
> Read some of the manuals (HTML help) about Pl/Sql programming as it is
 very
> 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;
> /
>
> 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,
>
> cBusPhone VARCHAR2,
> cEmail
> VARCHAR2,
> cPass
> VARCHAR2,
> cUser
> VARCHAR2,
>
 cCustID
> VARCHAR2,
>

 cUserT1
> VARCHAr2 ) 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 - 15:10:47 CEST