By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE PRODUCTS (PRODUCT_ID VARCHAR2(10), VENDOR VARCHAR2(10), ITEM VARCHAR(10));
CREATE TABLE PRODUCT_FILTER (PRODUCT_ID VARCHAR2(10));
CREATE TABLE PRODUCT_LOG (LOG_DATE DATE, LOG_TEXT VARCHAR2(4000));
CREATE TYPE varchar2_250_list IS TABLE OF VARCHAR2(250);
CREATE OR REPLACE PACKAGE PKG_PRODUCTS AS
PROCEDURE SET_FILTER(
p_vendor IN varchar2_250_list,
p_item IN varchar2_250_list
);
END PKG_PRODUCTS;
/
CREATE OR REPLACE PACKAGE BODY PKG_PRODUCTS AS
PROCEDURE SET_FILTER(
p_vendor IN varchar2_250_list,
p_item IN varchar2_250_list
)
IS
v_text VARCHAR2(4000);
BEGIN
INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT)
SELECT SYSDATE,
'p_vendor:'
|| LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1)
FROM TABLE(p_vendor);
INSERT INTO PRODUCT_LOG (LOG_DATE, LOG_TEXT)
SELECT SYSDATE,
'p_item:'
||LISTAGG(Column_Value, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP (ORDER BY 1)
FROM TABLE(p_item);
INSERT INTO PRODUCT_FILTER (PRODUCT_ID)
SELECT PRODUCT_ID
FROM PRODUCTS
WHERE ( p_vendor IS EMPTY
OR VENDOR MEMBER OF p_vendor)
AND ( p_item IS EMPTY
OR ITEM MEMBER OF p_item);
END SET_FILTER;
END PKG_PRODUCTS;
/
SELECT id,
emit_date,
noffered_count,
tabandon_count
FROM (
SELECT ID,
NOFFERED_COUNT,
NOFFERED_EMIT_DATE,
TABANDON_COUNT,
GREATEST(TABANDON_EMIT_DATE, NOFFERED_EMIT_DATE) AS TABANDON_EMIT_DATE
FROM METRIC_INTERVAL
WHERE conversation_id = 'tk421'
)
UNPIVOT (
(emit_date, cnt) FOR type IN (
(noffered_emit_date, noffered_count) AS 'NOFFERED',
(tabandon_emit_date, tabandon_count) AS 'TABANDON'
)
)
PIVOT (
MAX(cnt) FOR type IN (
'NOFFERED' AS noffered_count,
'TABANDON' AS tabandon_count
)
)
ORA-00942: table or view does not exist