I am suspecting this table function might be somehow adding special characters. I need to output this data to a table to analyze. How can we do this pls? is there a short method without having to create 43 variables to hold the 43 columns?
FUNCTION cast_property_table(i_PropertyTable IN tt_property_update) RETURN SYS_REFCURSOR IS
l_Property SYS_REFCURSOR;
TYPE y is table of varchar2(4000) index by pls_integer;
t y;
BEGIN
--Return the object table as a ref cursor
OPEN l_Property FOR
SELECT PropertyKey
,PropName
,PageDescr
,parentPropertykey
,parentPropertyval
,parentDataAliasID
,VirtualColName
,DomainKey
,DomainFilterValue
,DataType
,DataSubType
,ReqdFlg
,DfltValue
,UserReadFlg
,UserWriteFlg
,MinSize
,MaxSize
,MaxOccurs
,EditMask
,MINVALUE
,MAXVALUE
,PropEffectFlg
,DateStart
,DateEnd
,decode(row_id
,NULL
,nvl(BaseValue
,DfltValue)
,BaseValue) BaseValue
,PropClob
,decode(row_id
,NULL
,nvl(FinalValue
,DfltValue)
,FinalValue) FinalValue
,Row_id
,RowVersion
,hyperClientObjectID
,hyperURL
,hyperPropertyKey
,hyperpskey
,dfltfiltervalue
,seq
,viewrowversion
,primarydisplayflg
,dfltFilterType
,dfltFilterOperator
,UiDisplayMethod
,DfltValueDStart
,'N' PrimaryKey
FROM TABLE(CAST(i_PropertyTable AS tt_property_update)) prop
WHERE prop.primarydisplayflg = 'Y'
ORDER BY prop.seq;
-- I want to do this
CREATE TABLE table1 AS SELECT * FROM tablefunction OR SYSREFCURSOR;
RETURN l_Property;
EXCEPTION
WHEN OTHERS THEN
RAISE;
END cast_property_table;