APEX

Hi,

I have a reports region which needs to bring up data based on PL/SQL. The PL/SQL references a package with several functions each returning a pl/sql collection (varray) which I need to pass as parameters into the SQL used to generate the report.

I realise that this is probably more of a general PL/SQL issue, but I have searched that forum too and followed examples but still not got it to work.

Below is a short example of some PL/SQL which should generate a report. I realise that I could rewrite this in straight SQL, but my actual PL/SQL is far more complex, I just want to get this to work first. Please can you identify what is wrong with this code - I am not sure whether it is the way I am handling the collection or the RETURN statement that is wrong.

Thanks, Lucy


DECLARE
type loc_array_typ is varray(100) of number;
loc_array loc_array_typ;

BEGIN
select fossloc_seq into loc_array from u_geoldb.fossil_locality where gazetteer_name like 'Williams Point';

RETURN 'select * from u_geoldb.fossil_link where locality_seq in (select column_value from TABLE(CAST('||loc_array||' as loc_array_typ)))';

END;