> I have a sql server and mysql background. I am a total newbie with > using oracle packages so bear with me :) The DBA's here had not even > heard of the collect function so not getting much help here. The > package definition compiles fine (declaration). When I try to compile > the body I keep getting "invalid data type" for PRED_ARRAY. Anyone have > any idea what I am doing wrong? > > I have a package that I have: > > Type PRED_REC IS RECORD ( v_pred_id > PROJECT_TASK_PREDECESSORS.PRED_TASK_ID%type); > Type PRED_ARRAY IS TABLE OF PRED_REC INDEX BY BINARY_INTEGER; > > In the package body I have: > BEGIN > SELECT PROJECT_ID, CAST(COLLECT(pred_task_id) as PRED_ARRAY) as > preds > FROM PROJECT_TASK_PREDECESSORS > GROUP BY PROJECT_ID > END;
I don't know much about Oracle either, but it seems to me that the RECORD data type is the problem, since COLLECT is used to concatenate string values. I don't think you can cast an array of strings as an array of numeric values. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:319115 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4