How to execute the
PLSQL in Oracle cloud HCM?
Navigation: Reports and Analytics à New à Data Model à Create 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;
