Inconsistent datatypes: expected CURSOR got CLOB-CodePudding

While compiling this piece of code as oracle stored precedure, I get the error "Inconsistent datatypes: expected CURSOR got CLOB". I don't know how to put CLOB variable in output cursor.

CREATE OR REPLACE PROCEDURE sp_getPolygon 
(
    CityId IN INT,
    Out_CUR OUT SYS_REFCURSOR
) AS 
BEGIN
DECLARE
  cola_b_geom SDO_GEOMETRY;
  returned_json CLOB;
    BEGIN
    open Out_CUR for
        With cte as(SELECT GEOMETRY FROM GISBRANCHES WHERE "FBranchesId" IN (SELECT "Id" FROM "CreBranches" WHERE "FCitiesId" = CityId))
        SELECT GEOMETRY into cola_b_geom FROM cte;
        returned_json := SDO_UTIL.TO_GEOJSON(cola_b_geom);
        --dbms_output.put_line( returned_json );
        SELECT returned_json into Out_CUR FROM DUAL;
    END;
END sp_getPolygon;

CodePudding user response：

Not exactly like that; I can't test it as I don't have your tables, but - procedure should look like this:

CREATE OR REPLACE PROCEDURE sp_getpolygon 
(
    cityid  IN  INT,
    out_cur OUT SYS_REFCURSOR
) AS 
  returned_json CLOB;
BEGIN
  OPEN out_cur FOR
    WITH cte AS
        (SELECT geometry 
         FROM gisbranches 
         WHERE "fbranchesid" IN (SELECT "id" 
                                 FROM "crebranches" 
                                 WHERE "fcitiesid" = cityid
                                )
        )
  SELECT sdo_util.to_geojson (geometry) as geometry_out  
  FROM cte;
END sp_getpolygon;