Oracle Anonymous Block as Dataset query?

I am trying to have an anonymous block as a query text in an SSRS report dataset, problem is I don’t want to use Stored Procedure (odd client requirement).

I have tried the below but unable to get the output in the Dataset Fields. The block executes properly in Toad.

DECLARE

    pCTRY varchar2(1000);

    p_cur_OUT SYS_REFCURSOR;

   

BEGIN

IF(:pRegion = 'ALL') THEN

    OPEN p_cur_OUT FOR

    SELECT DISTINCT COUNTRY FROM INVENTORY.HAL_INVENTORY_ASSET_V;

ELSE

    OPEN p_cur_OUT FOR

    SELECT DISTINCT COUNTRY FROM INVENTORY.HAL_INVENTORY_ASSET_V WHERE REGION = (:pRegion);

END IF;

   LOOP

      FETCH p_cur_OUT INTO pCTRY;

      EXIT WHEN p_cur_OUT%NOTFOUND;

      DBMS_OUTPUT.PUT_LINE(pCTRY);

   END LOOP;

END;

Looking forward for your solutions.

Tweet