Oracle cursor's return type

Question

I want to declare the following cursor:

CURSOR some_cursor RETURN oks_trips.trip_id % TYPE IS
    SELECT trip_id FROM oks_trips;

But i get an error:

Error(5,36): PLS-00320: the declaration of the type of this expression is incomplete or malformed

oks_trips.trip_id type is NUMBER(3, 0), so i tried just NUMBER instead of oks_tripd.trip_id % TYPE but i still get the error.

I can't omit the RETURN statement because i declare cursor in package and oracle demands it there.

So the question is WHY can't i use NUMBER or some_field % TYPE in cursor's RETURN clause?

Answer 1

From the concepts guide:

You can declare a cursor explicitly within a procedure, function, or package to facilitate record-oriented processing of Oracle Database data. The PL/SQL engine can also declare cursors implicitly.

The important phrase there is 'record-oriented'. The syntax for explicit cursor declaration also clearly shows the return type has to be a rowtype, which it defines as:

The data type of the row that the cursor returns.

You are asking it to return the datatype of a single column, not of a row/record. If you don't want to use an existing %ROWTYPE then Oracle provides the mechanism to declare a record type instead, as another answer has already shown.

You seem to be complaining that the documentation doesn't say that you can't use a scalar value as the return. It also doesn't say that you can't return a package, or a view, or a role. It doesn't need to exhaustively list everything you cannot do, since it clearly tells you exactly what you can do, which is to return a type that represents a row.

In your case that row type only needs to contain a single column, but there is still no reason you should be able to - or expect Oracle to - let you take a shortcut in that very limitd scenario. It doesn't seem unreasonable to provide a single consistent mechanism - it's not much of a hardship for you to declare the record, whereas them bulding, testing and maintaining a seperate path for this would be a considerable overhead.