Package Body error

[suggs]

I've only started trying to program PL/SQL packages and am having quite a lot of trouble, procedures on there own I can handle but when I try to compile the following code I get the subsequent error Any ideas why:

CREATE OR REPLACE PACKAGE archive_database AS

PROCEDURE select_data(v_owner IN VARCHAR2);
CURSOR c1 IS SELECT table_name FROM DBA_TABLES WHERE OWNER = v_owner;

END archive_database;
/
CREATE OR REPLACE PACKAGE BODY archive_database AS

PROCEDURE select_data(v_owner IN VARCHAR2) IS

fHandle UTL_FILE.FILE_TYPE;

BEGIN

fHandle:=UTL_FILE.FOPEN('F:\oracle\orasvr\admin\CLDTEST\arch\','archivedata.txt','w');
UTL_FILE.PUT_LINE(fHandle,'File Begin');
OPEN c1;
LOOP

FETCH c1 INTO BULK;
EXIT WHEN c1%NOTFOUND;
UTL_FILE.PUT(fHandle,'<schema>:' || 'CLAUDEDEMO:' || '<table>:' || c1.table_name);

END LOOP;

UTL_FILE.PUT_LINE(fHandle,'End File');
UTL_FILE.CLOSE(fHandle);

END select_data;
END archive_database;
/



Errors for PACKAGE BODY ARCHIVE_DATABASE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0 PL/SQL: Compilation unit analysis terminated
1/14 PLS-00905: object CLAUDEDEMO.ARCHIVE_DATABASE is invalid
1/14 PLS-00304: cannot compile body of 'ARCHIVE_DATABASE' without its
specification


cheers.

 

[sem]

Your error is in package specification. You're probably not granted to select from DBA_TABLES. To use it from the stored procedure you must have explicit (not via role) rights on referenced objects.

 

[carp]

You might want to try breaking your package specification code into a different script file and run it separately. That way, you may be able to get a specific error message for the package spec.

I suspect sem may be right. A quick way to check this would be to change DBA_TABLES to ALL_TABLES and then see if the error goes away.

 

[listener22]

Can't you skip the definition of c1 in the package,
and define it in the package body? From what I've
seen, you only need to declare functions, procedures,
and global variables in the package; the rest is done
in the body. I guess error spec might be an exception
to what I just wrote. Jim

oracle, vb

 

[jeet]

I think it is always a better idea to create to separate script files for package header and body. The problem in your case is that the package header has compilation errors because of which is it is flagged as invalid. Since Oracle does not allow you to create to body for an invalid header you get the error
PLS-00304: cannot compile body of 'ARCHIVE_DATABASE' without its specification.

My suggestion is to create separate script for header. Run it and check for compilation error using show errors. Then tackle the body