Re: newbie passing parameters from Web page to Stored Procedure

shall_at_uaex.edu wrote:
> I'm trying to pass "20060821" as myUserID from a web page.
> The problem is that it does NOT find any records.
>
> I think it is in my First SQL statement.
>
> What I'm trying eventually to do is to LOCK QTY on hand and subtract
> the amount the customer ordered. Example QTY on HAND is 100 and
> user orders 5. I want to lock the QTY and replace it with 95 then
> unlock it.
> I'm using Oracle 9i.
>
> ===============================================
> CREATE OR REPLACE PACKAGE pkgpending AS
> Procedure open_join_cursor1 (myUserID IN VARCHAR2);
> END pkgpending;
> /
>
> CREATE OR REPLACE PACKAGE BODY pkgpending
> IS
> CURSOR io_cursor
> IS
> select P.sessionid,P.itemnumber,M.Ndesclow
> ,P.person,M.Qty,P.Origqty ,P.Qty AS MMQty
> FROM pending P, pub M
> WHERE ( P.Sessionid LIKE 'myUserID'
> AND P.Itemnumber=M.Itemnumber)
> for update of M.qty,M.Ndesclow;
> ao_rec io_cursor%ROWTYPE;
>
> Procedure open_join_cursor1 (myUserID IN VARCHAR2)
> IS
> myqty pub.qty%TYPE:=1;
> newqty pub.qty%TYPE;
> Itemnumber pub.Itemnumber%TYPE;
> MMqty pub.qty%TYPE;
> hiqty pub.qty%TYPE;
> origqty pub.qty%TYPE:=0;
> lcItemnum VARCHAR2(100);
> lc2Out VARCHAR2(100);
> lnNEMPNO NUMBER;
> BEGIN
> OPEN io_cursor;
> lc2Out :='ABCDE';
> LOOP
> FETCH io_cursor INTO ao_rec;
> EXIT WHEN io_cursor%NOTFOUND;
> lnNEMPNO := MMQty;
> lcItemnum := Itemnumber;
> hiqty := ao_rec.qty;
> newqty:=ao_rec.qty-lnNEMPNO;
> IF newqty>=0 THEN
> update pub set qty=newqty
> ,ndesclow=lc2Out
> where ( itemnumber like 'lcItemnum');
> END IF;
> commit;
> END LOOP;
> END open_join_cursor1;
> END pkgpending;
> /
> =======================
> TIA
> Steve

Your code doesn't do anything.

You are fetching into ao_rec and then doing nothing with ao_rec.

This may work in SQL Server where Microsoft controls the database and the operating system but it is a waste of CPU with Oracle.

What you likely want to do is return a REF CURSOR. Look at the demos in Morgan's Library at www.psoug.org under REF CURSOR.

PS: 9i is meaningless. It could be 9.0.1 or 9.2.0.7 and there are big differences between them: Be specific to four decimal places.

Also, for future reference, a cursor loop should almost never be used for anything in Oracle after version 8.1.7.

-- 
Puget Sound Oracle Users Group