Oracle cursor's return type
oracle, plsql
Solution
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.
Problem
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?