Is there a difference between declaring a bind variable with a leading colon (:myVar) and declaring one with surrounding pound signs (#myVar#)? I ask because of the following scenario.
I recently modified our ETL automation scripts, which included moving an Import task from inside of an If container to just before it. The import step in one of the scripts captures a rowcount variable (Import_VCAST_COV_Role_Dsgn_RCOUNT), and it is used to decide which branch of the If task is taken and is used in a Execute Script task that is part of one of the branches (value used in the WHERE clause). This worked fine in my versions of the script that run against our dev and our test databases, but suddenly started failing when I moved it and reworked it to run against production. I finally isolated the issue to the bind variable and the Execute Script task. If the variable had the leading colon, the SQL threw an ORA-00972: identifier is too long error. Once I changed the variable to use the pound signs, everything worked again. Note that this same variable, using the leading colon, still worked fine in the If branch tests, and a similar variable above in the script (Import_VCAST_COV_Role_RCOUNT) still worked without error with the leading colon in an Execute Script task inside of an If task.
Has anyone seen this sort of behavior? Does the length of the variable name have any impact (33 characters for the problem var vs. 29 for the one still working)? If so, does that mean I’m suddenly running into the 30 char limit in Oracle for object names? Then why did changing the bind approach fix the issue? Color me confused.