This content has been marked as final. Show 5 replies
TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
ERROR at line 2:
ORA-06550: line 2, column 24:
PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
ORA-06550: line 2, column 3:
PL/SQL: Item ignored
In the above code, SELECT COUNT(*) ... returns a NUMBER. I know it's an aggregate function, but it returns a single value.
Why can't I return a one row one column value into a NUMBER?
How can I change the SQL so that I can do this?
The exception is at line 2: your cursor declaration. And the answer is in the text you linked to
The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.
You declared the CURSOR to return NUMBER. And as the text says it must be a 'record'.
Also, I'm wondering about the use of FETCH with a count(*)....FETCH is supposed to fetch the next row...how does that work when you're selecting an aggregate such as count?
As you already said FETCH fetches the next row, if any. A query is a query is a query. It returns a result set. A query that uses aggregates returns a result set. A query that doesn't use aggregates returns a result set.
Your simple SELECT COUNT(*) query returns a result set that consists of ONE ROW and that one row has ONE COLUMN of datatype NUMBER. Even though there is only one column in the result set what is returned is a RECORD or ROW. So that is why you need to declare your cursor return datatype to be a RECORD using %ROWTYPE or %TYPE attributes or a record structure.
Weak and strong ref cursors? That IMO makes even less sense than using ref cursors (for PL/SQL code to use) in the first place.
The primary purpose of using the ref cursor data type for a SQL cursor, is to enable PL/SQL to pass the handle for that SQL cursor to the external client that owns the current db session.
The client will use the OCI describe call to determine the structure returned by the SQL cursor. So there is no need for a strong definition in PL/SQL code, as that is meaningless as far as the external client is concerned.
Using a ref cursor in PL/SQL code for PL/SQL code - that is an exception that needs justification. And a DBMS_SQL cursor is to PL/SQL, what a ref cursor is to an external client.
"The return value of a strongly typed REF CURSOR must be a record which can be defined using the %ROWTYPE and %TYPE attributes or as a record structure.
You declared the CURSOR to return NUMBER. And as the text says it must be a 'record'."
I missed that. I guess I'm getting confused between SELECT INTO or somesuch that can easily dump a value into a scalar variable.
Well I've never done client-server PL/SQL architecture so a lot of what you are saying is slightly gobbledegook to me, but I'll try to bear it in mind. Thanks.
Simple explanation. Do create ref cursors in PL/SQL. But do not use (consume/fetch) ref cursors in PL/SQL as the data type has not been designed to be used that way.
There are other data types for SQL cursors in PL/SQL specifically designed for use by PL/SQL code.