Yoiks!

Guess that falls under the 'be careful what you ask for' category, eh?:)

This may take a bit to sort through. I don't see anything obviously wrong with the proc, however.

I might suggest adding LS_CCN and LD_FISCAL_PROC_DATE to your DBMS_OUTPUTs to verify that they actually have the values you expect.

Oh, wait. What version of Oracle are you using?? There was a bug in 7.x. Actually, it was in the PL/SQL engine that 7.x used, although I don't know how they version that engine. Anyway, the problem was using variables in SQL statements. I never found the actual bug 'according to Oracle', but it definitely existed. The problem is hard to define, but here are the variables involved:

- Non-dynamic SQL statement within PL/SQL
- Variable used in SQL statement
- SQL statement had 3 or more levels of 'nesting'
-- You have UPDATE...SET X=(SELECT...FROM (SELECT...
-- That is 3 levels of nesting (by my definition)

The engine would 'lose its mind' with this kind of statement and return no results and no error, simply blow through the statement rather quickly, much as you are describing.

My usual workarounds were:
- put the 'variable' into a table and access it that way
- re-write the statement to reduce the nesting levels
- Turn it into dynamic SQL

The advantage of doing dynamic SQL is that you don't have to re-think anything. Just remember to de-reference the variable when you build the string.

SQL := "SELECT * FROM FRED WHERE COL1 = " || x

Actually, I never tried it with binds, "SELECT * FROM FRED WHERE COL1 = :x" and then binding x. That *might* work as well. That would be more beneficial from the SGA's point of view. However, I would theorize that using a bind is what the PL/SQL engine is already to do with the statement.

In other words, if you have SELECT * FROM FRED WHERE COL1 = local_variable, the way PL/SQL processes it is to turn it into SELECT * FROM FRED WHERE COL1 = :x, followed by a bind of x. So, it depends wherein the error lies as to whether using a bind would work or not. Unfortunately, I no longer have access to any 7.x databases to test this out.

My, but that was quite the ramble. Try the dynamic SQL thing and let me know what happens.

Hope this helps,

- Chris