Bind variable question

Toad Data Point
1

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.

2

These are two different variable types – one of them is SQL bind variable and it starts with leading colon and may be used in SQL statements. Another type of variables is specific to automation and they surrounded with pound signs.

So in your case the variable with leading colon is treated as SQL bind variable. It’s very strange that it works in test environment.

Regard

Aleksey

3

I stand corrected. Just double checked and my If branch tests do use #MyVar# instead of :MyVar, whereas the SQL statement uses :MyVar. But yes, confusion still stands, why did it work fine for dev and test but then error in prod. Not sure if this has anything to do with it, but we are in the midst of an Oracle upgrade. Dev and test for this project are now 12c, but prod is still back on 11g until a week from Friday, when it too will be upgraded.

4

Binding a variable is done by Oracle and the length of the var name is am Oracle limitation. When you use the #var# method Toad Data Point edits the SQL and replaces the value prior to execution. This is the difference between binding and literal replacement.