Dynamic SQL with sys_refcursor
When the end user attempts to use it returns:
base {System.Data.Common.DbException} = {"ORA-00911: invalid character\nORA-06512: at \"PRODDTA.SP_ITEM_GETBYFILTER\", line 13\nORA-06512: at line 1\n"}
When I look at the procedure, line 13 is the 'open oraCursor for ' statement.
I can not find the problem.
Can someone please tell me what I've done wrong.
FYI: Filter is the 'where' clause and is passed in from the application.
******************************************************************************************************************************************************************
CREATE PROCEDURE "PRODDTA"."SP_ITEM_GETBYFILTER" (
CommId in number
, Filter in varchar2
, oraCursor out SYS_REFCURSOR
)
is
FullStatement varchar2(4000) ;
SelectStatement constant varchar2(2000) := 'select distinct CAST(A.IT_1I2ITMPK AS INT) AS "Id",A.IT_1I2ITMAC AS "Action",CAST(A.IT_1I2MFRPK AS INT) AS "ManufacturerId",A.IT_1I2ITMCO AS "Country",A.IT_1I2ITMIN AS "Industry",A.IT_1I2ITMNU AS "Number",A.IT_1I2EAN AS "Ean",A.IT_1I2UPCFL AS "Upc_Flag", A.IT_1I2BCTYP AS "Barcode_Type",A.IT_1I2MFRNU AS "Mfr_Catalog_Number",A.IT_1I2TSCNU AS "Tsc_Catalog_Number",A.IT_1I2LUNUM AS "Lookup_Catalog_Number", A.IT_1I2ALTNU AS "Alt_Catalog_Number",A.IT_1I2TRDNM AS "Trade_Name",A.IT_1I2INDWT AS "Indv_Weight",A.IT_1I2WTUOM AS "Indv_Weight_Uom", A.IT_1I2GSTFL AS "GTSFlag",CAST(A.IT_1I2REPIT AS INT) AS "Replaced_By_ItemId",CAST(A.IT_1I2ITREP AS INT) AS "Replaces_ItemId", A.IT_1I2COORG AS "Country_Of_Origian",A.IT_1I2USRNU AS "User_Num",A.IT_1I2ITMST AS "Status",A.IT_1I2MSDS AS "Msds",A.IT_1I2MSDEF AS "Msds_Eff_Date", A.IT_1I2MSFRM AS "Msds_Form_Num",CAST(A.IT_1I2SICCD AS INT) AS "Item_Sic_Code",A.IT_1I2ITMSZ AS "Item_Size",A.IT_1I2LFCLS AS "Item_Class", A.IT_1I2MNTCD AS "Maintenance_Code",A.IT_1I2MNTMM AS "Maintenance_Month",CAST(A.IT_1I2UNS AS INT) AS "Unspsc",A.IT_1I2UNS40 AS "Unspsc40", A.IT_1TSBRNM AS "Brand_Name",A.IT_1TSMRBUP AS "Mfr_Replaced_By_Unc",A.IT_1TSMRBCN AS "Mfr_Replaced_By_Cat_No",A.IT_1TSMRUPC AS "Mfr_Replaces_Upc", A.IT_1TSMRCAT AS