5 Replies Latest reply: Feb 11, 2013 11:16 PM by Billy~Verreynne RSS

    PLS-00362: invalid cursor return type; 'NUMBER' must be a record type

    9423755
      Hi

      Having a bit of trouble with the following example code provided at http://www.dba-oracle.com/plsql/t_plsql_cursor_variables.htm :
        1  DECLARE
        2    TYPE t_ref_cursor IS REF CURSOR RETURN NUMBER;
        3    c_cursor  t_ref_cursor;
        4    l_row   NUMBER;
        5  BEGIN
        6    DBMS_OUTPUT.put_line('Strongly typed REF CURSOR using SCALAR type. Expect an error!');
        7    OPEN c_cursor FOR
        8      SELECT COUNT(*) cnt
        9      FROM   cursor_variable_test;
       10    LOOP
       11      FETCH c_cursor
       12      INTO  l_row;
       13      EXIT WHEN c_cursor%NOTFOUND;
       14      DBMS_OUTPUT.put_line(l_row);
       15    END LOOP;
       16    CLOSE c_cursor;
       17* END;
       18  /
        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?

      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?

      Many thanks,
      Jason
        • 1. Re: PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
          rp0428
          >
          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.
          • 2. Re: PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
            Billy~Verreynne
            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.
            • 3. Re: PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
              9423755
              "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.
              Many thanks.
              • 4. Re: PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
                9423755
                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.
                • 5. Re: PLS-00362: invalid cursor return type; 'NUMBER' must be a record type
                  Billy~Verreynne
                  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.