Help using Dynamic PL/SQL, function throwing exception

I am rather new at PL/SQL (esp. using Dynamic SQL), I created this function, and it r开发者_Python百科uns/compiles without any errors or warnings, but when I run the function....

SELECT schema.fa_awd_for_term('0000000','2003SPRING',NULL,NULL)
FROM DUAL;

....It throws an exception

Remove the EXCEPTION handlers and you will find out what the error actually is. You should not explicitly handle unexpected exceptions.

It would also be better to use bind variables e.g. change this:

    || 'substr(ta_xxxx_id,1,7) = ''sssssss'' '
    || 'and substr(ta_xxxx_id,instr(ta_xxxx_id,''*'',1,2)+1) = ''aaaa'' '

to

    || 'substr(ta_xxxx_id,1,7) = :sssssss '
    || 'and substr(ta_xxxx_id,instr(ta_xxxx_id,''*'',1,2)+1) = :aaaa '

then add a USING clause to the OPEN statement:

OPEN CUR_faawards FOR thequery USING id, v_year;

and remove the REPLACE code for these. I'm afraid you are stuck with 'xxxx' due to the awful table design - a column for each year?!?!