Help using Dynamic PL/SQL, function throwing exception

Question

I am rather new at PL/SQL (esp. using Dynamic SQL), I created this function, and it runs/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

Tony Andrews · Accepted Answer · 2011-06-09 15:05:34Z

1

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?!?!

answered Jun 9, 2011 at 15:05

Tony Andrews

133k23 gold badges230 silver badges266 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Mr Man Over a year ago

yes, I know :( it is poorly designed. I will try these suggestions, and get back to you. Thanks for the tips.

Mr Man Over a year ago

okay, I tried moving the bottom exception (where it returns 'ER2'), and I get an ora-06512, unhandled exception, and the error above it is "table or view does not exist (I have double checked my table names for accuracy) does this give any hint to the problem?

Tony Andrews Over a year ago

It could be any of the tables, but my suspicions would begin with the ta_xxxx tables. Try adding some debug messages e.g. using dbms_output.put_line.

Mr Man Over a year ago

okay, dumb, rookie question. Can i use dbms_output.put_line(); to print out a variable? So for instance can I do dbms_output.put_line(v_year); and have it output when the function is run?

Tony Andrews Over a year ago

Yes but only when running the function within a development environment like SQL Plus, SQL Developer or Toad. If you call the function from an application there is no easy access to the output of that. A common alternative is to write debug messages to a table using an autonomous transaction - see stackoverflow.com/questions/1568166/…

Collectives™ on Stack Overflow

Help using Dynamic PL/SQL, function throwing exception

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related