packaging cursors

[suggs]

I have just started writing packages in pl/sql and am having difficulty with cursors. Basically I want a cursor that will use a parameter passed into the procedure I have written the following code but when I try to compile it I get the error :

LINE/COL ERROR
-------- ------------------------------------------------
4/3 PL/SQL: SQL Statement ignored
4/42 PLS-00201: identifier 'V_OWNER' must be declared

The code is in two files spec:

CREATE OR REPLACE PACKAGE archive_database AS

PROCEDURE select_data (v_owner VARCHAR2);
CURSOR c1 RETURN dba_tables%ROWTYPE;

END archive_database;
/


and body :


CREATE OR REPLACE PACKAGE BODY archive_database AS

CURSOR c1 RETURN dba_tables%ROWTYPE IS
SELECT * FROM dba_tables where owner = v_owner;

PROCEDURE select_data (v_owner VARCHAR2) IS

fHandle UTL_FILE.FILE_TYPE;
my_record dba_tables%ROWTYPE;

BEGIN
fHandle:=UTL_FILE.FOPEN('F:\oracle\orasvr\admin\CLDTEST\arch\','archivedata.txt','w');
OPEN archive_database.c1;
LOOP
FETCH archive_database.c1 INTO my_record;
EXIT WHEN archive_database.c1%NOTFOUND;
UTL_FILE.PUT(fHandle, my_record.table_name || ':' || my_record.owner);
UTL_FILE.PUT_LINE(fHandle,';');
END LOOP;

CLOSE archive_database.c1;
UTL_FILE.FCLOSE(fHandle);

END select_data;
END archive_database;
/

Also can anyone give me some sample code where you can query a cursor with the results from another cursor like a nested loop, if it is possible.

cheers.

Suggs

 

[sem]

You have to declare v_owner as a cursor parameter:

CURSOR c1(v_owner varchar2) RETURN dba_tables%ROWTYPE IS
SELECT * FROM dba_tables where owner = v_owner

and pass the parameter while opening:

OPEN archive_database.c1(some_value);


With parametrized cursor you may easily organize nested loop, passing the appropriate value(s) to the inner cursor.

 

[carp]

You're running into a scoping problem. Your v_owner variable is only defined within the select_data procedure; it is undefined outside of the procedure (where your cursor resides).

However, you might try parameterizing your cursor as follows:

CURSOR c1 (p_owner VARCHAR2) IS
SELECT * FROM dba_tables where owner = p_owner;

and then open it in your procedure:

OPEN archive_database.c1(v_owner);

I believe this will work for you.