I have a Shell Script as:
##################################################
sqlplus -s dailydev/dailydev@prada <<SQLEND
WHENEVER SQLERROR EXIT;
set serveroutput on
CREATE OR REPLACE PACKAGE types AS
TYPE cursortyp is REF CURSOR;
END;
/
CREATE OR REPLACE PACKAGE test_ref_cursor AS
PROCEDURE main;
FUNCTION get_cursor_ref() RETURN types.cursortyp;
PROCEDURE process_cursor();
END;
/
show errors
CREATE OR REPLACE PACKAGE BODY test_ref_cursor AS
PROCEDURE main IS
BEGIN
process_cursor();
END;
FUNCTION get_cursor_ref() RETURN types.cursortyp IS
cur types.cursortyp;
BEGIN
OPEN cur FOR select USERNAME from users;
RETURN cur;
END;
-- Process rows for an EMP or DEPT cursor
PROCEDURE process_cursor() IS
empRec emp%ROWTYPE;
BEGIN
LOOP
FETCH cur INTO empRec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('EMP ROW: '||empRec.OWNER);
END LOOP;
EXCEPTION
WHEN ROWTYPE_MISMATCH THEN
LOOP
FETCH cur INTO empRec;
EXIT WHEN cur%NOTFOUND;
dbms_output.put_line('DEPT ROW: '||empRec.OWNER);
END LOOP;
END;
END;
/
show errors
EXEC test_ref_cursor.main;
/
SQLEND
####################################################
When a run/execute the script in terminel i am getting the follwing o/p or error messages:
Package created.
Warning: Package created with compilation errors.
Errors for PACKAGE TEST_REF_CURSOR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
3/29 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current
3/53 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
. ( , * @ % & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
into || bulk
Warning: Package Body created with compilation errors.
Errors for PACKAGE BODY TEST_REF_CURSOR:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/27 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current
10/52 PLS-00103: Encountered the symbol "IS" when expecting one of the
following:
. ( , * @ % & - + / at mod rem <an identifier>
<a double-quoted delimited-identifier> <an exponent (**)> as
into || bulk
LINE/COL ERROR
-------- -----------------------------------------------------------------
18/28 PLS-00103: Encountered the symbol ")" when expecting one of the
following:
<an identifier> <a double-quoted delimited-identifier>
current
BEGIN test_ref_cursor.main; END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00905: object DAILYDEV.TEST_REF_CURSOR is invalid
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
####################################
Could anyone provide me a sample/example that runs so i can customize to run.