Hi everyone,
I am working on Oracle 11g DB and i am using plsql allround automation tool.
I created a procedure with the help of object type. But when i was trying to execute the procedure it is giving me ORA-00947 error. I was hoping if you can help me in finding what i am missing.
Posted the types and procedure
CREATE TYPE grpprod_type
AS OBJECT (
"GROUP_ID" NUMBER,
"COVERAGE_ID" NUMBER,
"COVERED_ITEMS" varchar2(100),
"QUANTITY" NUMBER,
"COVERAGE_COST" NUMBER,
"TYPE_VALUE2" varchar2(100)
);
CREATE TYPE grpprod_tab AS TABLE OF grpprod_type;
CREATE OR REPLACE PROCEDURE krgazula.getcoverages( P_CNTRCT_IDInput IN NUMBER, P_PRODUCT_VERSION_IDInput IN NUMBER, p_arr OUT grpprod_tab )
AS
BEGIN
SELECT
POC.GROUP_ID,
POC.COVERAGE_ID,
POC.COVERED_ITEMS,
TO_CHAR(COC.COVERAGE_QUANTITY) QUANTITY,
NVL((SELECT TO_CHAR(CONTRACT_COVERAGES.COVERAGE_GROUP_COST)
FROM CONTRACT_COVERAGES
WHERE CONTRACT_COVERAGES.GROUP_ID = POC.GROUP_ID
AND CONTRACT_COVERAGES.COVERAGE_ID = POC.COVERAGE_ID
AND CONTRACT_COVERAGES.CONTRACT_ID = P_CNTRCT_IDInput),
(SELECT TO_CHAR(NVL(CONTRACT_COVERAGES.COVERAGE_COST, 0))
FROM CONTRACT_COVERAGES
WHERE CONTRACT_COVERAGES.GROUP_ID IS NULL
AND CONTRACT_COVERAGES.COVERAGE_ID = POC.COVERAGE_ID
AND CONTRACT_COVERAGES.CONTRACT_ID = P_CNTRCT_IDInput)) COVERAGE_COST,
COD.TYPE_VALUE2
BULK COLLECT INTO p_arr
FROM (SELECT PCGM.GROUP_ID,
PCGM.COVERAGE_ID,
C.EXTERNAL_DESCRIPTION COVERED_ITEMS
FROM group_product PCGM
LEFT OUTER JOIN coverage C
ON C.COVERAGE_ID = PCGM.COVERAGE_ID
WHERE PCGM.PRODUCT_VERSION_ID = P_PRODUCT_VERSION_IDInput
UNION
SELECT NULL, PC.COVERAGE_ID, C.EXTERNAL_DESCRIPTION COVERED_ITEMS
FROM coverage_product PC
LEFT OUTER JOIN coverage C
ON C.COVERAGE_ID = PC.COVERAGE_ID
WHERE PC.OPTIONAL_BASE_TYPE = 'OPTIONAL'
AND PC.PRODUCT_VERSION_ID = P_PRODUCT_VERSION_IDInput) POC
LEFT OUTER JOIN coverage_contract COC
ON NVL(COC.GROUP_ID, 0) = NVL(POC.GROUP_ID, 0)
AND COC.COVERAGE_ID = POC.COVERAGE_ID
AND COC.CONTRACT_ID = P_CNTRCT_IDInput
LEFT OUTER JOIN detail_coverages COD
ON POC.COVERAGE_ID = COD.COVERAGE_ID
AND COD.PRODUCT_VERSION_ID = P_PRODUCT_VERSION_IDInput
AND COD.TYPE_PREFIX = 'PVCOVTYPE'
AND COD.TYPE_CODE = 'COVTYPE'
WHERE POC.COVERED_ITEMS IS NOT NULL
ORDER BY NVL(POC.GROUP_ID, 999999), POC.COVERED_ITEMS;
END;