How to execute the PLSQL in Oracle cloud HCM

Navigation: Reports and Analytics à New à Data Model à Create a new Data set

Name: PLSQL (we can give any name)

Data Source: ApplicationDB_HCM

Type of SQL: Procedure Call

SQL Query:

DECLARE

type sys_refcursor is REF CURSOR;

xdo_cursor  sys_refcursor;

l_InQuery varchar2(32000);

BEGIN

l_InQuery := :query1;

OPEN :xdo_cursor FOR l_InQuery;

END;

Create the parameters:

Here query1 and xdo_cursor are the parameters

Run the data set then we will get the data of the sql that we pass as a parameter.

Example2 :

DECLARE      

    type precursor is REF CURSOR;

       xdo_cursor refcursor;

    BEGIN

      OPEN :xdo_cursor FOR

       SELECT *

       FROM PER_ALL_PEOPLE_F PAPF

       WHERE PAPF.PERSON_NUMBER = :Parameter;

       COMMIT;

   END;