Creating an Oracle Package Body

Question

I'm trying to lay the foundation for a package, but I have problems even getting started. I have successfully created the basic specification of a package and I just want to test the package body, but I had problems compiling it. Specification Code:

CREATE OR REPLACE PACKAGE synchronize_my_data AS PROCEDURE synchronize_data(p_run_date IN date); END synchronize_my_data; 

and here is the package case code:

 CREATE OR REPLACE PACKAGE BODY synchronize_my_data IS PROCEDURE synchronize_data(p_run_date IN date) IS PROCEDURE process_deletes(p_run_date IN date) IS BEGIN dbms_output.put_line('Run Date: ' || to_char(p_run_date, 'MM/DD/YYYY')); END process_deletes; BEGIN process_deletes(p_run_date); END synchronize_data; END synchronize_my_data; 

I keep getting a compilation error, but can't figure out what is wrong with the code. It seems like the base code, will I just miss something obvious?

Answer 1

This code seems to be compiling for me. What error do you get?

 SQL> CREATE OR REPLACE PACKAGE synchronize_my_data 2 AS 3 PROCEDURE synchronize_data(p_run_date IN date); 4 END synchronize_my_data; 5 / Package created. SQL> CREATE OR REPLACE PACKAGE BODY synchronize_my_data 2 IS 3 PROCEDURE synchronize_data(p_run_date IN date) IS 4 PROCEDURE process_deletes(p_run_date IN date) IS 5 BEGIN 6 dbms_output.put_line('Run Date: ' || to_char(p_run_date, 'MM/DD/YYYY')); 7 END process_deletes; 8 BEGIN 9 process_deletes(p_run_date); 10 END synchronize_data; 11 12 END synchronize_my_data; 13 / Package body created. 

From a general stylistic point of view, as a rule, it makes little sense to define a procedure as part of another procedure in the package body. One of the benefits of using packages is that you can have both public and private procedures. You can create the process_deletes procedure as a private procedure by simply defining it in the body without defining it in the specification.

 CREATE OR REPLACE PACKAGE BODY synchronize_my_data IS PROCEDURE process_deletes(p_run_date IN date) IS BEGIN dbms_output.put_line('Run Date: ' || to_char(p_run_date, 'MM/DD/YYYY')); END process_deletes; PROCEDURE synchronize_data(p_run_date IN date) IS BEGIN process_deletes(p_run_date); END synchronize_data; END synchronize_my_data; 

This should not have anything to do with any error you get. But this should make it easier to work with your code.