×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Problem declaring and using a REF CURSOR

Problem declaring and using a REF CURSOR

Problem declaring and using a REF CURSOR

(OP)
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?

RE: Problem declaring and using a REF CURSOR

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close