Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2002
    Posts
    22

    Unanswered: pl/sql procedure help--getting error

    guys i have the following cust search procedure, but it failed while compiling
    any help would be appreciated. This procedure takes zipcode as input
    parameter & displays zip, cust name, & total sales for each cusomer;
    ************
    create or replace procedure zipCust
    (v_zip IN Customer.zip%type
    v_zip OUT customer.zip%type;
    v_name OUT Customer.name%type;
    v_sum OUT Ord.Total%type;
    )
    as
    cursor cur_cust (v_in_cur INcustomer.zip%type) is
    select zip, name, sum(total) INTO
    v_zip, v_name, v_sum
    FROM customer c, Ord o
    where c.custid =o.custid;
    group by zip, name;
    begin
    open cur_cust (v_zip);
    loop
    fetch cur_cust into v_zip, v_name, v_sum;
    exit when cur_cust%notfound;
    dbms_output.put_line ( ' customers in:'|| v_zip || v_name || v_sum );
    end loop;

    end;

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    After trying to compile ... and it gives a compilation error:

    sql> sho error
    or
    sql> select text from all_errors where name = 'ZIPCUST';

    What is the error it is giving you??

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    well, for one, you have a lot of syntax errors.
    clean those up.

    I am not gonna bother doing those for you.
    just look at it. You are missing semi-colons and spaces in areas.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Dec 2003
    Location
    Oklahoma, USA
    Posts
    354
    Actually, I only see a couple of erros:
    > (
    > v_zip IN Customer.zip%type
    > v_zip OUT customer.zip%type;
    > v_name OUT Customer.name%type;
    > v_sum OUT Ord.Total%type;
    > )

    Since this is a parameter list, the variable should be separated by commas "," instead of semi-colons, and the last one "v_sum" should have nothing before the closing parenthesis.

    The other error is here:

    > FROM customer c, Ord o
    > where c.custid =o.custid;
    > group by zip, name;

    The parser is finding the end of the cursor declaration to be after your WHERE clause, however you have a GROUP BY expression after it. Remove the semi-colon after the second line, and it should be fine.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •