Executing Dynamic SQL with Ref Cursors

Here is the SP to execute Dynamic SQL built at Runtime:

CREATE OR REPLACE procedure SP_TEST_REF_CURSOR(i_n_proposalId number)as


TYPE FacilityDetails IS REF CURSOR;

facilities_data FacilityDetails;

l_s_facQuery varchar2(10000);

lengthOfString integer;

l_s_status_code varchar2(20);
l_n_edit_code number;

l_n_counter number:=0;
begin

dbms_output.put_line('hello world');


l_s_facQuery:='SELECT FACILITY_STATUS_CODE,FACILITY_ID
FROM
fdrdbo.v_credit_facility@RODR_DEV.NY.SONU.COM
WHERE
(facility_id=198000589 and
facility_version_no =2 and
facility_edit_no=0 and
facility_edit_session_no=0) or (facility_id=299074678 and
facility_version_no =1 and
facility_edit_no=0 and
facility_edit_session_no=0) ';

lengthOfString:=length(l_s_facQuery);

--dbms_output.put_line(lengthOfString);

--put_line(l_s_facQuery,lengthOfString);
--l_s_facQuery:='select proposal_element_id from srgt_proposal_element where proposal_id=489';

OPEN facilities_data FOR l_s_facQuery;

LOOP

FETCH facilities_data into l_s_status_code,l_n_edit_code;

EXIT WHEN facilities_data%NOTFOUND;
l_n_counter:=l_n_counter+1;


dbms_output.put_line('FacilityStatus Code is : ' || l_s_status_code || 'facilityId is :' || l_n_edit_code);


--DBMS_OUTPUT.PUT_LINE('hello world');
--DBMS_OUTPUT.PUT_LINE(l_s_status_code || ' ' || l_n_edit_code);
END LOOP;

CLOSE facilities_data;

DBMS_OUTPUT.PUT_LINE('hello world' || l_n_counter);

end;
/