Data Point Error: Must Declare the Scalar Variable ...

Toad Data Point
1

I have a SQL query that I’m running against SQL server using Data Point Beta 3.4. In the query I define a Date variable called @InForceDate.

The query works fine in other editors. (Toad for SQL Server, SSMS, Excel)

However, when I execute the query in Data Point Beta 3.4 I get the following error message: Must declare the scalar variable “@InForceDate”.

Is this some sort of bug in Data Point ?

`DECLARE @InForceDate Date
SET @InForceDate = ‘8/15/2013’

SELECT blah blah…`

I also tried assigning the date using CONVERT(Datetime,‘08/15/2013’, 101) but that didn’t help at all.

2

Try to define variable as :InForceDate.

It should work.

3

I replaced all instances of @InForceDate with :InForceDate.

Now I get the following error:

The variable name ‘@InForceDate’ has already been declared. Variable names must be unique within a query batch or stored procedure.

4

Could you show a script that throws the error?

Regards

Aleksey

5

I think I figured it out.

I had been using Execute Current Statement (F9) instead of Execute SQL (F5). I’m not exactly sure what is considered the “Current Statement” but it seems to ignore the variable declaration unless my cursor is before the variable declaration. This is different from how Execute Current Statement (F9) works in Toad for SQL Server where it does execute the variable declaration.

6

Good!

7

This is still an issue in Data Point 4.3.0.718 (64 bit).

The workaround is to use F5 instead of F9.

But come on…5 years later and the same bug is there?

8

This is not a bug and that is why things are not changing. This is original behavior from Toad for Oracle.

There are different execution needs depending on what you are doing.

When you have a script you might want to only execute the SQL you are working on. For this you use F9 and we figure out what the current statement is based on your cursor and only execute that.

Or when you have a script you might want to execute all in the script from top down. For this you use F5.

There is a third option which is to execute from the current statement to the end. For this there is a menu action but no hot key.

In the case of a SQL that contains a script variable you can’t execute the current statement as the variable must be declared in block of code that is being sent to the server. So in this case F5 is the only option.

I don’t see a bug here however I am all ears if you think I have missed something