Will an error generated when dynamic sql is executed be caught in the exception block?

I have a situation where the site I maintain calls a plsql package/procedure (Oracle 11g). The procedure builds a string of dynamic sql using the parameters passed in (call the string "v_select"). After the string is built, a ref cursor is opened using the v_select dynamic sql string. Pseudo code below.

OPEN ref_cursor FOR v_select
USING  variables set to input parameters ;

Now, the procedure has an exception block (WHEN OTHERS) to catch any exceptions. In the exception block, any errors are written to a table in the DB. When an error occurs while the dynamic sql is executed, the error does not seem to be caught by the exception block (no entry is being inserted into the error table), but I can see the error at the .net level, so I know it's an Oracle invalid_number error.

So, finally, my question is this....from开发者_运维技巧 what I see online (which wasn't much), if the dynamic sql chokes, this should be caught by the exception block. Is this correct?

This error usually isn't detected until the query actually executes, which may not happen until you try to fetch the first row from the cursor. The generated SQL is correct logically and syntactically; it's just doing a type conversion that is invalid for some of the rows in your database. So the error isn't caught in the stored procedure; when the stored procedure is done it hasn't happened yet.

This has nothing to do with the query being dynamic. A non-dynamic version of the same query would still not throw the error until you fetch from the cursor.