-- USER SQL
CREATE USER "SPWS" IDENTIFIED BY "&1"
    DEFAULT TABLESPACE "DATA"
    TEMPORARY TABLESPACE "TEMP";

-- ROLES
GRANT "CONNECT" TO "SPWS";
GRANT "RESOURCE" TO "SPWS";
GRANT "DWROLE" TO "SPWS";

--Privileges
GRANT select_catalog_role TO spws;
GRANT UNLIMITED TABLESPACE TO "SPWS";
GRANT
    SELECT ANY DICTIONARY
TO spws;

-- SH Tables
GRANT SELECT ANY TABLE TO SPWS;

--Change to View and describe it to obtain column definitions
/* OPEN p_cv_weak FOR */
CREATE OR replace force view SP_TEMP_VIEW as
    SELECT
        prod_name,
        cust_id,
        channel_id,
        amount_sold
    FROM
        sh.sales      s,
        sh.products   p
    WHERE
        s.prod_id = p.prod_id
      AND 1=2;
        
 DESCRIBE sp_temp_view

CREATE OR REPLACE PACKAGE sp_pkg IS
    -- Output Type for Strong Cursor variable 
    TYPE sp_output IS RECORD (
        prod_name     VARCHAR2(50),
        cust_id       NUMBER,
        channel_id    NUMBER,
        amount_sold   NUMBER
    );
    TYPE cv_strong IS REF CURSOR RETURN sp_output;
END;

--procedure with paramters and a strong CV
CREATE OR REPLACE PROCEDURE sp_parms (
    p_channel_id    NUMBER,
    p_cust_id       NUMBER,
    p_boolean_val   VARCHAR2,
    p_cv_strong     IN OUT sp_pkg.cv_strong
) IS
BEGIN
    OPEN p_cv_strong FOR SELECT
                      CASE
                          WHEN p_boolean_val = 'TRUE' THEN
                              substr(prod_name, 1, 5)
                          ELSE
                              prod_name
                      END prod_name,
                      cust_id,
                      channel_id,
                      amount_sold
                  FROM
                      sh.sales      s,
                      sh.products   p
                  WHERE
                      s.prod_id = p.prod_id
                      AND s.channel_id = p_channel_id
                      AND s.cust_id = p_cust_id;
END;

--procedure without parameters and a weak CV
CREATE OR REPLACE PROCEDURE sp (
    p_cv_weak IN OUT SYS_REFCURSOR
) IS
BEGIN
    OPEN p_cv_weak FOR SELECT
                      prod_name,
                      cust_id,
                      channel_id,
                      amount_sold
                  FROM
                      sh.sales      s,
                      sh.products   p
                  WHERE
                      s.prod_id = p.prod_id;
END;

--Create Result Set Table with a Key Column
CREATE TABLE sp_result_set
    AS
        SELECT
            0 SP_RS_KEY,
            a.*
        FROM
            sp_temp_view a
        WHERE
            1 = 2;

DESCRIBE sp_result_set

curl -X POST -i -v https://<FQDN>/ords/spws/dv/sp/

DECLARE
    p_rc     sp_pkg.cv_strong;
    in_rec   p_rc%rowtype;
BEGIN
    DELETE sp_result_set
    WHERE
        sp_rs_key = :sp_rs_key;

    sp_parms(:channel_id, :cust_id, :boolean_val, p_rc);
    LOOP
        FETCH p_rc INTO in_rec;
        EXIT WHEN p_rc%notfound;
        INSERT INTO sp_result_set VALUES (
            :sp_rs_key,
            in_rec.prod_name,
            in_rec.cust_id,
            in_rec.channel_id,
            in_rec.amount_sold
        );

    END LOOP;

    COMMIT;
    CLOSE p_rc;
END;

CREATE OR REPLACE TYPE sp_output IS OBJECT (
    prod_name     VARCHAR2(50),
    cust_id       NUMBER,
    channel_id    NUMBER,
    amount_sold   NUMBER
);

CREATE OR REPLACE TYPE sp_output_table AS
    TABLE OF sp_output;

CREATE OR REPLACE FUNCTION sp_function RETURN sp_output_table
    PIPELINED
IS

    p_rc      sp_pkg.cv_strong;
    in_rec    p_rc%rowtype;
    out_rec   sp_output := sp_output(NULL, NULL, NULL, NULL);
BEGIN
    sp(p_rc);
    LOOP
        FETCH p_rc INTO in_rec;
        EXIT WHEN p_rc%notfound;
        out_rec.prod_name := in_rec.prod_name;
        out_rec.cust_id := in_rec.cust_id;
        out_rec.channel_id := in_rec.channel_id;
        out_rec.amount_sold := in_rec.amount_sold;
        PIPE ROW ( out_rec );
  end LOOP;

    CLOSE p_rc;
    return;
END;

SELECT * FROM TABLE ( sp_function );