Toad World® Forums

Bind Variables cause TDP to freeze

Hello,

I am creating scripts with two bind variables to produce year end or month end reports for our Finance area. The reporting tool that we've used for years is being desupported, so we are moving all those reports to TDP. Almost all these reports prompt for a fiscal year and/or fiscal period (month).

In TDP I created the bind variable in the where clause with the ':'. Such as, field name = :FISCAL_YEAR and field name = :FISCAL_PERIOD.

The problem that I've ran into after recreating about 15 of these reports using sql editor is that when I click run, using either Run SQL or Run Script (I've tried both), and the bind variable window pops up TDP will freeze if I try to change either of the variables. I have to go to Task Manager and force TDP to close.

Is this because I'm using the same variable in all these reports (:FISCAL_YEAR and :FISCAL_PERIOD)?

Several of these reports I've put to Automation and the first activity is the Set Variable. When running in Automation, I don't have this same issue.

I'm not seeing this behavior in my TDP Pro version 5.1.3.179. I strung several SQL Statements together in my script, using the same bind variables in the WHERE clauses throughout the script.... I have been able to change the values for each SQL statement.

BTW, the Execute button image is an overloaded button.

See snap below for a look at the different modes of execution. In particular, be sure to use F5 (execute SQL ((meaning editor contents as SCRIPT)) ) vs. F9 (execute just the SQL that your editor cursor is camped on).

image

Forgot to ask what version of TDP you're experiencing this with, and on which database? I used Oracle for my testing above.

@Gary.Jerep

I am on TDP 5.1.4.189(32 bit) and connected to OraClint 11g.

I will try your suggestions above. I forgot to note in my original post that I have several scripts (statements?) in the same .sql file. All separated by ; so that they will all run to different sets.

Yes, let me know if you resolve, as I have the same setup (11gR2) although I may want to install the latest TDP and re-test

Issue is still not resolved and I just had it happen when creating a template from the Export Wizard in Automation.

Can I send you two sample files, so you can see what I've written?

Sure, send the files to my e-mail address through ToadWorld. You should see my e-mail address in my User Bio.