Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Problem declaring and using a REF CURSOR

Status
Not open for further replies.

JA3395

Programmer
May 22, 2007
88
IE
I'm having a real problem using a REF CURSOR type

Here's the DECLARE and the start of the BEGIN I've so far developed.

DECLARE
TYPE r1 IS RECORD (
szvcapc_pidm szvcapc.szvcapc_pidm%TYPE,
szvcapc_term_code szvcapc.szvcapc_term_code%TYPE,
szvcapc_request_no szvcapc.szvcapc_request_no%TYPE);
szvcapc_rec r1;
TYPE cursor_1 IS REF CURSOR RETURN szvcapc_rec;
szvcapc_cv cursor_1;

TYPE r2 IS RECORD (
stvests_code stvests.stvests_code%TYPE
);
stvests_rec r2;
TYPE cursor_2 IS REF CURSOR RETURN stvests_rec;
stvests_cv cursor_2;

BEGIN

OPEN szvcapc_cv FOR
SELECT szvcapc_pidm, szvcapc_term_code, szvcapc_request_no
FROM szvcapc
WHERE szvcapc_passed_ind = 'Y'
AND szvcapc_award_credits = 'N';

LOOP
FETCH szvcapc_cv INTO szvcapc_rec;
EXIT WHEN szvcapc_cv%NOTFOUND;
END LOOP;

OPEN stvests_cv FOR
SELECT stvests_code
FROM stvests
WHERE stvests_eff_headcount = 'Y';

LOOP
FETCH stvests_cv INTO stvests_rec;
EXIT WHEN stvests_cv%NOTFOUND;
END LOOP;

SELECT *
FROM (
<snip>
INNER JOIN stvests_rec
ON SFBETRM.SFBETRM_ESTS_CODE = stvests_rec.STVESTS_CODE
<snip>
);


I later try to use the stvests_rec and szvcapc_rec in the main SELECT statement it doesn't recognise stvests_rec and szvcapc_rec as a "table".

I have to use a REF CURSOR as this code is ultimately for use in Oracle Reports.

What am I doing wrong?
 
The error is:

PL/SQL: ORA-00942: table or view does not exist

A slight change is

TYPE cursor_1 IS REF CURSOR RETURN r1;

as the compiler didn't like the previous version.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top