PL/SQL: ORA-00904: "CONTAINS": invalid identifier
create or replace
PACKAGE BODY PF_SEARCH
IS
PROCEDURE PF_QUICK_SEARCH
(
searchWord VARCHAR2 DEFAULT NULL,
locale VARCHAR2 DEFAULT NULL,
catalog VARCHAR2 DEFAULT NULL,
pricelist VARCHAR2 DEFAULT NULL,
minCount INTEGER DEFAULT 5,
maxCount INTEGER DEFAULT 1000,
searchCount OUT INTEGER,
categorySet OUT cursorType,
productSet OUT cursorType
)
AS
BEGIN
SELECT count(*)
INTO searchCount
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_price t3
WHERE t1.product_id = t3.product_id
AND t3.price_list = pricelist
AND t2.product_id = t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale));
IF searchCount <= minCount THEN
OPEN productSet for
SELECT t1.product_id
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_price t3
WHERE t1.product_id = t3.product_id
AND t3.price_list = pricelist
AND t2.product_id=t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale));
ELSE
IF searchCount > maxCount THEN
OPEN categorySet for
SELECT DISTINCT t3.cat_lvl1_id, t3.cat_lvl2_id, t3.cat_lvl3_id
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_pf_search_cat t3,
dcs_price t4
WHERE t1.product_id = t4.product_id
AND t4.price_list = pricelist
AND t2.product_id=t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale))
AND t2.product_info_id = t3.product_info_id
ORDER BY t3.cat_lvl1_id, t3.cat_lvl2_id, t3.cat_lvl3_id;
ELSE
OPEN categorySet for
SELECT DISTINCT t3.cat_lvl1_id, t3.cat_lvl2_id, t3.cat_lvl3_id
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_pf_search_cat t3,
dcs_price t4
WHERE t1.product_id = t4.product_id
AND t4.price_list = pricelist
AND t2.product_id = t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale))
AND t2.product_info_id = t3.product_info_id
ORDER BY t3.cat_lvl1_id, t3.cat_lvl2_id, t3.cat_lvl3_id;
OPEN productSet for
SELECT t1.product_id
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_price t3
WHERE t1.product_id = t3.product_id
AND t3.price_list = pricelist
AND t2.product_id=t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale));
END IF;
END IF;
END PF_QUICK_SEARCH;
PROCEDURE PF_CATEGORY_SEARCH
(
searchWord VARCHAR2 DEFAULT NULL,
locale VARCHAR2 DEFAULT NULL,
catalog VARCHAR2 DEFAULT NULL,
pricelist VARCHAR2 DEFAULT NULL,
parentCat VARCHAR2 DEFAULT NULL,
productSet OUT cursorType
)
AS
BEGIN
OPEN productSet for
SELECT t4.product_id
FROM dcs_pf_product_trans t1,
dcs_prd_prdinfo t2,
dcs_price t3,
dcs_product t4
WHERE t1.product_id = t3.product_id
AND t3.price_list = pricelist
AND t2.product_id=t1.product_id
AND (((CONTAINS(t1.product_id,searchWord,0) > 0
OR CONTAINS(t1.manufacturer_part_number,searchWord,1) > 0
OR CONTAINS(t1.vendor_name,searchWord,2) > 0
OR CONTAINS(t1.display_name,searchWord,3) > 0
OR CONTAINS(t1.long_description,searchWord,4) > 0)
AND t2.catalog_id = catalog)
AND (t1.locale_id = locale))
AND t1.product_id = t4.product_id
AND t4.parent_cat_id = parentCat;
END PF_CATEGORY_SEARCH;
PROCEDURE PF_GENERIC_FULL_SEARCH
(
sqlquery VARCHAR2 DEFAULT NULL,
sqlCountQuery VARCHAR2 DEFAULT NULL,
categoryQuery VARCHAR2 DEFAULT NULL,
minCount INTEGER DEFAULT 5,
maxCount INTEGER DEFAULT 1000,
count OUT INTEGER,
countSet OUT cursorType,
categorySet OUT cursorType,
productSet OUT cursorType
)
AS
searchCount INTEGER;
BEGIN
OPEN countSet for sqlCountQuery;
FETCH countSet INTO searchCount;
count := searchCount;
IF searchCount <= minCount THEN
OPEN productSet for sqlquery;
ELSE
IF searchCount > maxCount THEN
OPEN categorySet for categoryQuery;
ELSE
OPEN categorySet for categoryQuery;
OPEN productSet for sqlquery;
END IF;
END IF;
END PF_GENERIC_FULL_SEARCH;
PROCEDURE PF_GENERIC_PROD_SEARCH
(
sqlquery VARCHAR2 DEFAULT NULL,
productSet OUT cursorType
)
AS
BEGIN
OPEN productSet for sqlquery;
END PF_GENERIC_PROD_SEARCH;
END PF_SEARCH;
----------------------------------------
Package Body PFCA4.PF_SEARCH@US_DEV_WD1_PFCA4
Error(22,2): PL/SQL: SQL Statement ignored
Error(34,5): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
Error(43,5): PL/SQL: SQL Statement ignored
Error(54,5): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
Error(61,7): PL/SQL: SQL Statement ignored
Error(73,14): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
Error(82,7): PL/SQL: SQL Statement ignored
Error(94,14): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
Error(102,6): PL/SQL: SQL Statement ignored
Error(113,5): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
Error(137,5): PL/SQL: SQL Statement ignored
Error(149,5): PL/SQL: ORA-00904: "CONTAINS": invalid identifier
---------------