Oracle 18.3.0.0
Hi,
I get the following error when running the below code:
Error:
Error report -
ORA-06550: line 24, column 46:
PLS-00642: local collection types not allowed in SQL statements
ORA-06550: line 24, column 40:
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
ORA-06550: line 11, column 1:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
DECLARE
TYPE vc_array IS TABLE OF VARCHAR2(60);
--MULTIDRAW 01
AnalysisTbl vc_array := vc_array('PL_STATS01_N1','PL_STATS01_N2','PL_STATS01_N3','PL_STATS01_N4','PL_STATS01_N5','PL_STATS01_N6');
cCol vc_array := vc_array('N1','N2','N3','N4','N5','N6');
BEGIN
MERGE INTO PL_STATS01_TEC_LASTDRAW TblUpd
USING (
SELECT ANALYSIS_DATE
,NUM_ASSOC_FORM
,N_VALUE
,CFIELD_REP
,MINDIST
,LOW_LIMIT
,DISTMEDIAN
,NDISTTODAY
FROM PL_STATS01_TEC_LASTDRAW
WHERE (NDISTTODAY > 0 AND REMOVE = 0 AND DIFF_MIN >= 11)
AND ((CFIELD_REP < DISTMEDIAN) AND (NDISTTODAY < MINDIST) )
AND NUM_ASSOC_FORM IN (SELECT * FROM TABLE(cCol))
) SubQry
ON (SubQry.ANALYSIS_DATE = TblUpd.ANALYSIS_DATE AND SubQry.NUM_ASSOC_FORM = TblUpd.NUM_ASSOC_FORM AND SubQry.N_VALUE = TblUpd.N_VALUE)
WHEN MATCHED THEN
UPDATE SET TblUpd.REMOVE = 1
WHERE TblUpd.REMOVE = 0;
IF('N1' MEMBER OF CCOL) THEN
DBMS_OUTPUT.PUT_LINE('========');
END IF;
END;
On line 24 I have tried to use AND NUM_ASSOC_FORM IN (SELECT * FROM TABLE(cCol)) also tried AND MEMBER OF (cCol)
But the error is the same
The datatype of NUM_ASSOC_FORM is VARCHAR2 (15 CHAR)
any idea why do I get this error and how to fix it
The IF at the end is working