I'm getting these 2 errors and i can't fix them

Question

Error report -
ORA-06550: line 6, column 5:
PLS-00382: expression is of wrong type
ORA-06550: line 6, column 5:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:

This is the code:

SET SERVEROUTPUT ON;

CREATE OR REPLACE FUNCTION NUMBEROFHOTELS
    RETURN SYS_REFCURSOR AS
    cur_nbOfHotels SYS_REFCURSOR;
    BEGIN
    OPEN cur_nbOfHotels FOR
     SELECT COUNT(*), s.REGION 
     FROM HOTEL h, STATION s
     WHERE h.NUM_STATION = s.NUM_STATION AND h.CATEGORIE='3star'
     GROUP BY s.REGION;
    RETURN cur_nbOfHotels;
END NUMBEROFHOTELS;
/

show errors;

DECLARE
    V_nbHotels number :=0;
    v_reg STATION.REGION%TYPE;
    cur_nbhotels SYS_REFCURSOR := NUMBEROFHOTELS();
    BEGIN
    OPEN cur_nbhotels;
        LOOP
            FETCH cur_nbhotels INTO V_nbHotels,v_reg;
            EXIT WHEN cur_nbhotels%notfound;
            DBMS_OUTPUT.PUT_LINE(v_reg||' '||V_nbHotels);
        END LOOP;   
    CLOSE cur_nbhotels;
END;
/

score 0 · Accepted Answer · answered Jan 02 '19 at 21:00

Delete the following line in your anonymous PL/SQL block at the bottom:

    OPEN cur_nbhotels;

The cursor has already been opened in your NUMBEROFHOTELS function. You don't need to open it again.

I took your code, deleted that line and it ran successfully on some sample data I made up.

score 0 · Answer 2 · answered Jan 02 '19 at 21:04

0

You do not need to open the cursor in your second block as it is already open in your first block. have a look at the answer for this question for more details. Returning a ref cursor from a Oracle Function

answered Jan 02 '19 at 21:04

Shaun Peterson

1,735
1
14
19

score 0 · Answer 3 · answered Jan 02 '19 at 22:42

If you are doing a sort of example, that's fine, but you should keep in mind to avoid this line

CLOSE cur_nbhotels;

This should be done by another procedure like NUMBEROFHOTELS

By the way, you can use implicit cursors and avoid OPEN - CLOSE stuff. This is an example for your code

DECLARE
  CURSOR c_stations
  IS
    SELECT s.region, count(*) n_region 
      FROM hotel h
          ,station s
     WHERE h.categorie = '3star'
       AND h.num_station = s.num_station
    GROUP BY s.region;

BEGIN
  <<station_loop>>
  FOR r_station IN c_stations
  LOOP
    sys.dbms_output.put_line(r_station.region || ' ' || r_station.n_region);
  END LOOP station_loop;   
END;

i know i can do that but i am obliged to use a function because it's for a university project and this is what my doctor wants — daniel, Jan 03 '19 at 06:12

I'm getting these 2 errors and i can't fix them

3 Answers3