cursor variable

[suggs]

I am starting to use cursor variables but need a good boot in the general direction.

The package spec compiles OK but when I compile the package body I get the following error:

SQL> sho err
Errors for PACKAGE BODY ARCHIVE_DATABASE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
33/3 PL/SQL: Statement ignored
33/29 PLS-00330: invalid use of type name or subtype name
61/2 PLS-00306: wrong number or types of arguments in call to
'GET_TAB_DATA'

61/2 PL/SQL: SQL Statement ignored


I've defined a weak cursor so as I can pass the table name in as a parameter and return
the cursor for that table data.
I have defined the type as 'dba_tab_columns.table_name%TYPE' which may be the problem
but I am lost on this one and cursor variable as a whole, I just can't seem to get my head round
them.
If there is any real guru's who don't mind me phoning them personally could you please drop
me your number this is the last bit I just need to crack.

regards in advance
Claude


CREATE OR REPLACE PACKAGE archive_database AS

CURSOR get_tables(p_owner VARCHAR2) RETURN dba_tables%ROWTYPE;
CURSOR get_columns(p_table VARCHAR2) RETURN dba_tab_columns%ROWTYPE;
TYPE get_tab_data IS REF CURSOR;
PROCEDURE select_data (v_owner VARCHAR2);
PROCEDURE get_data(alldata IN OUT get_tab_data,ptable IN dba_tab_columns.table_name%TYPE);

END archive_database;
/


CREATE OR REPLACE PACKAGE BODY archive_database AS

CURSOR get_tables(p_owner VARCHAR2) RETURN dba_tables%ROWTYPE IS
SELECT * FROM dba_tables WHERE owner = p_owner;

CURSOR get_columns(p_table VARCHAR2) RETURN dba_tab_columns%ROWTYPE IS
SELECT * FROM dba_tab_columns WHERE table_name = p_table ORDER BY column_name;


PROCEDURE select_data (v_owner VARCHAR2) IS

fHandle UTL_FILE.FILE_TYPE;
tab_record dba_tables%ROWTYPE;
col_record dba_tab_columns%ROWTYPE;
ltable dba_tab_columns.table_name%TYPE;
lowner dba_tables.owner%TYPE;

BEGIN
fHandle:=UTL_FILE.FOPEN('F:\oracle\orasvr\admin\CLDTEST\arch\','archivedata.txt','w');
UTL_FILE.PUT_LINE(fHandle,'File Begin<000123>');

lowner := UPPER(v_owner);
OPEN archive_database.get_tables(lowner);
LOOP
FETCH archive_database.get_tables INTO tab_record;
EXIT WHEN archive_database.get_tables%NOTFOUND;
ltable := tab_record.table_name;

UTL_FILE.PUT(fHandle, '<schema>:' || tab_record.owner || ':<table>:' || tab_record.table_name || ':');

OPEN archive_database.get_columns(ltable);

archive_database.get_data(get_tab_data, ltable);

LOOP
FETCH archive_database.get_columns INTO col_record;
EXIT WHEN archive_database.get_columns%NOTFOUND;

IF archive_database.get_columns%ROWCOUNT = 1 THEN
UTL_FILE.PUT(fHandle, col_record.column_name);
ELSE
UTL_FILE.PUT(fHandle, ',' || col_record.column_name);
END IF;

END LOOP;

UTL_FILE.PUT_LINE(fHandle,';');
CLOSE archive_database.get_columns;
END LOOP;

CLOSE archive_database.get_tables;
UTL_FILE.PUT_LINE(fHandle,'End File<000123>');
UTL_FILE.FCLOSE(fHandle);

END select_data;

PROCEDURE get_data(alldata IN OUT get_tab_data, ptable IN dba_tab_columns.table_name%TYPE) IS

BEGIN

OPEN get_tab_data FOR SELECT * FROM ptable;

END get_data;

END archive_database;
/

 

[carp]

I believe you have crossed cursors with functions. Cursors do not use RETURN. Take out the RETURN xxx and see if the error goes away.