Re: Newbie Question on Creating procedure

Hi,

Maybe I'm missing the problem, but what's wrong with:

create or replace procedure YourProc (p_product in varchar2, p_rev in number)
is
begin
  insert into test (product, rev, info, qty)   select p_product, rev+1, info, qty
  from test
  where product = p_product
  and rev = p_rev;
end;

>I've got the following PL/SQL statement to add new revision data to a file. It
>works fine as is but I would like to create it as a procedure but everytime I
>do I am getting
>errors. Could someone please show me the proper syntax to do this.
>
>DECLARE
> NewProd VARCHAR2(10);
> NewRev NUMBER(3);
> NewInfo VARCHAR2(8);
> NewQty NUMBER(4);
> CURSOR MyCursor IS
> SELECT PRODUCT,REV+1,INFO,QTY
> FROM TEST
> WHERE PRODUCT = 'TEST1' AND REV = 2;
> BEGIN
> OPEN MyCursor;
> LOOP
> FETCH MyCursor INTO NewProd,NewRev,NewInfo,NewQty;
> EXIT WHEN MyCursor%NOTFOUND;
> INSERT INTO TEST
> VALUES(NewProd,NewRev,NewInfo,NewQty);
> END LOOP;
> CLOSE MyCursor;
> COMMIT WORK;
> END;
>
>I would like to be able to pass the product (TEST1) and the REV (2) in as the
>parameters.

• Martin Jesterhoudt
• martinj(replace this by @)xs4all.nl

  ---