Skip to content

Instantly share code, notes, and snippets.

@setap

setap/muliset.sql

Last active August 29, 2015 14:16
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save setap/a5e0ee6ff81a814f71d0 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save setap/a5e0ee6ff81a814f71d0 to your computer and use it in GitHub Desktop.
Download ZIP
Oracle cast multiset and refcursor examples
Raw
muliset.sql
CREATE OR REPLACE TYPE ProtocolType AS OBJECT ("ID" VARCHAR2(100),
"Comment" CLOB, "CHECKIN" VARCHAR2(255))
CREATE OR REPLACE TYPE ProtocolTableType AS TABLE OF ProtocolType
CREATE OR REPLACE VIEW ProtocolView AS
SELECT CAST(MULTISET
(SELECT t."ID", t."COMMENTS", t.atc_portal_checkin
FROM HPCPROTOCOLM1 t) AS ProtocolTableType) ppdd
FROM dual
SELECT VALUE(p)
FROM TABLE
(SELECT ppdd
FROM ProtocolView) p
SELECT *
FROM protocolview
SELECT VALUE(p).ProtocolComment
FROM TABLE
(SELECT ppdd
FROM ProtocolView) p
CREATE OR REPLACE FUNCTION get_mass_inc (userid IN varchar2) RETURN ProtocolTableType AS v_ret ProtocolTableType;
BEGIN
SELECT CAST ( multiset (
SELECT CAST(MULTISET
(SELECT t."ID", t."COMMENTS", t.atc_portal_checkin
FROM HPCPROTOCOLM1 t) AS ProtocolTableType) ppdd INTO v_ret
FROM dual;
RETURN v_ret;
END get_mass_inc;
----------------------------
CREATE OR REPLACE TYPE mass_inc_type AS OBJECT ("IncidentID" VARCHAR2(100),
InteractionID VARCHAR2(10),
"STATUS" VARCHAR2(60),
ASSIGNMENT VARCHAR2(255),
ASSIGNEE VARCHAR2(255),
DESCRIPTION CLOB, CI VARCHAR2(255),
SERVICE VARCHAR2(255),
"PRIORITY" VARCHAR2(40),
"CATEGORY" VARCHAR2(60),
SOLUTION CLOB,
CREATED_DATE DATE,
ACTIVE CHAR(1),
DATE_DEADLINE DATE)
CREATE OR REPLACE TYPE mass_inc_table_type AS TABLE OF mass_inc_type
CREATE OR REPLACE FUNCTION get_mass_inc (userid IN varchar2) RETURN mass_inc_table_type AS v_ret mass_inc_table_type;
BEGIN
SELECT CAST(MULTISET (
SELECT i."NUMBER" AS "ID",
i4.IntId AS "InteractionID",
i2.HPC_STATUS AS "STATUS",
i2.HPC_ASSIGNMENT AS "ASSIGNMENT",
i2.HPC_ASSIGNEE AS "ASSIGNEE",
i.ACTION AS "DESCRIPTION",
i.logical_name AS "CI",
i2.affected_item AS "SERVICE",
i.priority_code AS "PRIORITY",
i.CATEGORY AS "CATEGORY",
i.resolution AS "SOLUTION",
i.open_time AS "CREATED_DATE",
i.flag AS "ACTIVE",
i3.hpc_next_breach
FROM probsummarym1 i
LEFT OUTER JOIN PROBSUMMARYM2 i2 ON i2."NUMBER"=i."NUMBER"
LEFT OUTER JOIN PROBSUMMARYM3 i3 ON i3."NUMBER"=i."NUMBER"
LEFT OUTER JOIN
(SELECT t1.INCIDENT_ID AS IntId,
t2.ITC_RELATEDINCID AS RelInc
FROM INCIDENTSM1 t1
LEFT OUTER JOIN INCIDENTSM2 t2 ON t1.INCIDENT_ID=t2.INCIDENT_ID
WHERE t1.CONTACT_NAME=userid) i4 ON i4.RelInc=i."NUMBER"
WHERE i3.HPC_IS_MASS='t'
) AS mass_inc_table_type) INTO v_ret
FROM dual;
RETURN v_ret;
END get_mass_inc;
Raw
refcursor.sql
CREATE OR REPLACE FUNCTION get_mass_inc (contactid IN VARCHAR2) RETURN sys_refcursor IS c_result sys_refcursor;
BEGIN OPEN c_result
FOR
SELECT i."NUMBER" AS "ID",
i4.IntId AS "InteractionID",
i2.HPC_STATUS AS "STATUS",
i2.HPC_ASSIGNMENT AS "ASSIGNMENT",
i2.HPC_ASSIGNEE AS "ASSIGNEE",
i.ACTION AS "DESCRIPTION",
i.logical_name AS "CI",
i2.affected_item AS "SERVICE",
i.priority_code AS "PRIORITY",
i.CATEGORY AS "CATEGORY",
i.resolution AS "SOLUTION",
i.open_time AS "CREATED_DATE",
i.flag AS "ACTIVE"
FROM probsummarym1 i
LEFT OUTER JOIN PROBSUMMARYM2 i2 ON i2."NUMBER"=i."NUMBER"
LEFT OUTER JOIN PROBSUMMARYM3 i3 ON i3."NUMBER"=i."NUMBER"
LEFT OUTER JOIN
(SELECT t1.INCIDENT_ID AS IntId,
t2.ITC_RELATEDINCID AS RelInc
FROM INCIDENTSM1 t1
LEFT OUTER JOIN INCIDENTSM2 t2 ON t1.INCIDENT_ID=t2.INCIDENT_ID
WHERE t1.CONTACT_NAME=contactid) i4 ON i4.RelInc=i."NUMBER"
WHERE i3.HPC_IS_MASS='t';
RETURN c_result;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment