Re: SQL stuff

Sandy H wrote:

> 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;

"It is not working" is about as informative as the messages I am getting from my crystal ball. I guess it isn't working either.

What happens? What doesn't happen? What are the error messages if any? And how are you trying to execute the procedures and functions? Hopefully: SQL> exec package_name.procedure_name(parameter).

But I must tell you that DBMS_OUTPUT has no place in any code except for debugging purposes which does not look like what you are doing. Strip it out and replace it with inserts into a log table if you want to know what is happening.

Daniel Morgan Received on Tue Jun 04 2002 - 10:09:47 CDT