Re: SQL stuff

From: Job Miller <jobmiller_at_yahoo.com>
Date: 4 Jun 2002 12:07:00 -0700
Message-ID: <2edf8ed9.0206041107.5b3d491f@posting.google.com>

in cust_list you open a cursor that isn't defined anywhere.

PROCEDURE cust_list(suburb in varchar2)
IS
BEGIN
OPEN custcurs;
 FETCH custcurs INTO custrec;
 IF custcurs%found THEN
  while custcurs%found
   loop
    dbms_output.put_line(RPAD(CUSTREC.NAME, 30, '.')||' '||CUSTREC.PHONE);     fetch custcurs into custrec;
   end loop;
   CLOSE custcurs;
 ELSE

  dbms_output.put_line('-------------------------------------------');

  dbms_output.put_line('There are no customers in '||UPPER(suburb));

  dbms_output.put_line('-------------------------------------------');

 END IF;
END cust_list;

you didn't give us the errors, so it is hard to know what is going wrong.

"Sandy H" <sandyhayman_at_dodo.com.au> wrote in message news:<3cfcc5a7_at_news.comindico.com.au>...
> Hi
> I'm not sure if I'm posting this in the right place but here goes.
> I have two procedures and one function that I'm trying to put into a
> Package.
> Having had no experience with Packages, I have written the following but it
> is not working.
> Could someone pls have a look and tell me what I'm doing wrong (assuming the
> whole thing isn't wrong.
> The first part of the code is the Package and the second part is the
> original procedures and function - these were working prior to creating the
> package.
>
> Thanks in advance
> Sandy
>
> ------------------------------------
> Package I'm trying to create
> ------------------------------------
>
> -- Create a package to contain the procedures and functions
>
> CREATE OR REPLACE PACKAGE
> acme
> AS
> PROCEDURE delete_cust(custid in number);
> PROCEDURE cust_list(suburb in varchar2);
> FUNCTION average_order RETURN number;
> END acme;
> CREATE OR REPLACE PACKAGE BODY
> acme
> AS
> -- Number 3
> -- Procedure to delete customers
> PROCEDURE delete_cust(custid in number)
> IS
> BEGIN
> -- Select the record
> SELECT name INTO custname
> FROM customer
> WHERE id = custid;
> -- Delete the record
> DELETE FROM customer
> WHERE id = custid;
> dbms_output.put_line('Customer '||custname||' deleted.');
> EXCEPTION
> -- If record is not found
> WHEN NO_DATA_FOUND THEN
> RAISE_APPLICATION_ERROR(-20010, 'Cannot find employee '||custid);
> END delete_cust;
>
> -- Procedure to list customer names and phone numbers in a certain suburb
> PROCEDURE cust_list(suburb in varchar2)
> IS
> BEGIN
> OPEN custcurs;
> FETCH custcurs INTO custrec;
> IF custcurs%found THEN
> while custcurs%found
> loop
> dbms_output.put_line(RPAD(CUSTREC.NAME, 30, '.')||' '||CUSTREC.PHONE);
> fetch custcurs into custrec;
> end loop;
> CLOSE custcurs;
> ELSE
> dbms_output.put_line('-------------------------------------------');
> dbms_output.put_line('There are no customers in '||UPPER(suburb));
> dbms_output.put_line('-------------------------------------------');
> END IF;
> END cust_list;
>
> -- Function to return the average number of days it takes to fill an order
> FUNCTION average_order RETURN NUMBER
> IS
> BEGIN
> SELECT TRUNC(avg(datesupplied - dateordered))
> INTO average_days
> FROM orders
> WHERE datesupplied is not null;
> RETURN average_days;
> END average_order;
> END acme;
> /
> SHOW ERRORS PACKAGE acme;
>
> ---------------------------------------
> -- Original Procedures and Functions
> ---------------------------------------
>
> CREATE OR REPLACE PROCEDURE
> delete_cust(custid in number)
> AS
> custname customer.name%type;
> BEGIN
> -- Select the record
> SELECT name INTO custname
> FROM customer
> WHERE id = custid;
> -- Delete the record
> DELETE FROM customer
> WHERE id = custid;
> dbms_output.put_line('Customer '||custname||' deleted.');
> EXCEPTION
> -- If record is not found
> WHEN NO_DATA_FOUND THEN
> RAISE_APPLICATION_ERROR(-20010, 'Cannot find employee '||custid);
> END;
> /
> SHOW ERRORS PROCEDURE delete_cust;
>
> -- Number 4
> -- Procedure to display customer name and phone number
> CREATE OR REPLACE PROCEDURE
> cust_list (suburb in varchar2)
> AS
> CURSOR custcurs IS
> SELECT name,
> phone
> FROM customer
> WHERE address like '%'||UPPER(suburb);
> custrec custcurs%rowtype;
> BEGIN
> OPEN custcurs;
> FETCH custcurs INTO custrec;
> IF custcurs%found THEN
> while custcurs%found
> loop
> dbms_output.put_line(RPAD(CUSTREC.NAME, 30, '.')||' '||CUSTREC.PHONE);
> fetch custcurs into custrec;
> end loop;
> CLOSE custcurs;
> ELSE
> dbms_output.put_line('-------------------------------------------');
> dbms_output.put_line('There are no customers in '||UPPER(suburb));
> dbms_output.put_line('-------------------------------------------');
> END IF;
> END;
> /
> SHOW ERRORS PROCEDURE cust_list;
>
> -- Mumber 5
> -- Function to calculate the avg no of days to fill an order
> CREATE or REPLACE FUNCTION
> average_order
> RETURN number IS Average_Days NUMBER(3,0);
> BEGIN
> SELECT TRUNC(avg(datesupplied - dateordered))
> INTO average_days
> FROM orders
> WHERE datesupplied is not null;
> RETURN average_days;
> END;
> /
> SHOW ERRORS FUNCTION average_order;
Received on Tue Jun 04 2002 - 14:07:00 CDT