CV8_Domaci

CREATE OR REPLACE PACKAGE pck_reports AS
PROCEDURE getReports(p_cursor OUT SYS_REFCURSOR);
PROCEDURE getReportHeader(p_rep_id NUMBER, p_cursor OUT SYS_REFCURSOR);
PROCEDURE executeReport(p_rep_id NUMBER, p_cursor OUT SYS_REFCURSOR, p_result OUT CLOB);
END pck_reports;

CREATE OR REPLACE PACKAGE BODY pck_reports AS
   PROCEDURE getReports(p_cursor OUT SYS_REFCURSOR) AS
   BEGIN
      OPEN p_cursor FOR SELECT rep_id, rep_description FROM IDAS2.reports;
   END getReports;
   PROCEDURE getReportHeader(p_rep_id NUMBER, p_cursor OUT SYS_REFCURSOR) AS
   BEGIN
      OPEN p_cursor FOR SELECT rco_column_visible_name FROM IDAS2.reports_columns WHERE rep_id = p_rep_id ORDER BY rco_column_rank;
   END getReportHeader;
   PROCEDURE executeReport(p_rep_id NUMBER, p_cursor OUT SYS_REFCURSOR, p_result OUT CLOB) AS i_report_query IDAS2.reports.rep_sql%TYPE;
   BEGIN
      SELECT rep_sql INTO i_report_query FROM IDAS2.reports WHERE rep_id = p_rep_id;
      OPEN p_cursor FOR i_report_query;
      p_result := 'OK';
   EXCEPTION
      WHEN OTHERS THEN
        p_result := 'Chyba: ' || SQLERRM;
        OPEN p_cursor FOR SELECT NULL FROM dual;
   END executeReport;
END pck_reports;