converting an anomynous block to a package function

[Guinea]

Hi Everyone,

I am trying to convert the anonymous PL/SQL block to a function that is part of a package.


anonymous block

SET SERVEROUTPUT ON;
ACCEPT p_cust_id PROMPT 'What is the customer ID: ';
ACCEPT p_start_date PROMPT 'What is the start date (MM/DD/YYYY): ';
ACCEPT p_end_date PROMPT 'What is the end date (MM/DD/YYYY): ';


DECLARE
v_start_date VARCHAR2(12) := '&p_start_date';
v_end_date VARCHAR2(12) := '&p_end_date';
v_custid ord.custid%TYPE := &p_cust_id; --customer ID
v_cust_salestot item.itemtot%TYPE :=0; --total sales for customer

/* cursor to loook up order numbers for customer */
CURSOR get_order_ids_cursor IS

SELECT ordid
FROM ord
WHERE custid = v_custid AND (orderdate BETWEEN TO_DATE(v_start_date ,'MM/DD/RRRR') AND TO_DATE(v_end_date,'MM/DD/RRRR'));

/* cursor to add up item totals from separate orders */
CURSOR item_tot_cursor (v_ord_id NUMBER) IS

SELECT itemtot
FROM item
WHERE ordid = v_ord_id;

BEGIN
FOR get_order_ids_record IN get_order_ids_cursor LOOP
FOR item_tot_record IN item_tot_cursor
(get_order_ids_record.ordid) LOOP

v_cust_salestot := v_cust_salestot + item_tot_record.itemtot;

END LOOP;
END LOOP;
DBMS_OUTPUT.PUT_LINE('***********************************');
DBMS_OUTPUT.PUT_LINE('For customer: '||v_custid);
DBMS_OUTPUT.PUT_LINE('Total Sales is: $'||v_cust_salestot);

EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLCODE);

END;
/

PACKAGE SPECIFICATION

CREATE OR REPLACE PACKAGE SAL_STATS

IS


FUNCTION cust_total_sales (v_start_date IN VARCHAR2, v_end_date IN VARCHAR2, v_cust_id IN ord.custid%TYPE)
RETURN NUMBER;

PRAGMA RESTRICT_REFERENCES(cust_total_sales, WNDS);


END SAL_STATS;

/

PACKAGE BODY


CREATE OR REPLACE PACKAGE BODY SAL_STATS IS


FUNCTION cust_total_sales (v_start_date IN VARCHAR2, v_end_date IN VARCHAR2, v_cust_id IN ord.custid%TYPE)
RETURN NUMBER

IS

v_cust_salestot NUMBER(7,2) :=0;
v_ord_id ord.ordid%TYPE;



CURSOR get_order_ids_cursor IS

SELECT ordid
FROM ord
WHERE custid = v_custid AND (orderdate BETWEEN TO_DATE(v_start_date ,'MM/DD/RRRR')
AND TO_DATE(v_end_date,'MM/DD/RRRR'));


CURSOR item_tot_cursor (v_ord_id ord.ordid%TYPE) IS

SELECT itemtot
FROM item
WHERE ordid = v_ord_id;

BEGIN
FOR get_order_ids_record IN get_order_ids_cursor LOOP
FOR item_tot_record IN item_tot_cursor
(get_order_ids_record.ordid) LOOP

v_cust_salestot := v_cust_salestot + item_tot_record.itemtot;

END LOOP;
END LOOP;


EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLCODE);

RETURN (v_cust_salestot);


END cust_total_sales;

END SAL_STATS;
/


The package specification compiles fine but I get the error
"Warning: Package Body created with compilation errors."
when I compile the body.

How can I see what the compilation errors are?

Any tips on what I am doing wrong in the package body?

Any help would be greatly appreciated!!!

-Marc
Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[karluk]

Execute the following SQL*Plus command. Let us know the results.

SHOW ERRORS PACKAGE BODY SAL_STATS

 

[Guinea]

Thanks Karluk,

Here are the results of the command

LINE/COL ERROR
-------- -----------------------------------------------------------------
19/3 PL/SQL: SQL Statement ignored
21/18 PLS-00201: identifier 'V_CUSTID' must be declared
33/3 PL/SQL: Statement ignored
34/5 PLS-00364: loop index variable 'GET_ORDER_IDS_RECORD' use is
invalid

-Marc Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com

 

[karluk]

I think the key error is on line 21, where you use the variable 'V_CUSTID' without declaring it. It looks to me as if all the other errors arise from this.

Most likely this is a simple typo. You have a variable 'v_cust_id' in your function definition. You probably wanted to reference this variable, but didn't notice the different spelling.

 

[Guinea]

Yes, that was part of the problem. I also needed to declare the records returned from the cursors.

Thanks so much for your help!!!! '___'

Hopefully soon I will have enough experience to contribute to the forum too.

Below is the code that did compile without errors.



CREATE OR REPLACE PACKAGE BODY SAL_STATS AS

FUNCTION cust_total_sales (v_start_date IN VARCHAR2, v_end_date IN VARCHAR2, v_cust_id IN ord.custid%TYPE)
RETURN NUMBER

IS

v_cust_salestot NUMBER(7,2) :=0;
v_ord_id ord.ordid%TYPE;


TYPE get_order_ids_record IS RECORD
(ordid NUMBER);

CURSOR get_order_ids_cursor

IS

SELECT ordid
FROM ord
WHERE custid = v_cust_id AND (orderdate BETWEEN TO_DATE(v_start_date ,'MM/DD/RRRR')
AND TO_DATE(v_end_date,'MM/DD/RRRR'));

TYPE item_tot_record IS RECORD
(itemtot NUMBER);


CURSOR item_tot_cursor (v_ord_id ord.ordid%TYPE) IS

SELECT itemtot
FROM item
WHERE ordid = v_ord_id;




BEGIN
FOR get_order_ids_record IN get_order_ids_cursor LOOP
FOR item_tot_record IN item_tot_cursor
(get_order_ids_record.ordid) LOOP

v_cust_salestot := v_cust_salestot + item_tot_record.itemtot;

END LOOP;
END LOOP;


EXCEPTION WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE(SQLCODE);

RETURN (v_cust_salestot);


END cust_total_sales;

END SAL_STATS;
/

Guinea pigs make the world a nicer place!

Piggydad30@hotmail.com