I’ve been racking my brain over this and after scouring through numerous threads, I’m hitting a brick wall. The company I work for has a massive data warehouse (Oracle) and in writing queries, there’s a lot of redundant filters used throughout subqueries to assist in reducing the run times of the queries I’m pulling. What I’m trying to do is establish a global variable housed within the query itself. I’ve got a coworker using Toad For Oracle and he provided a sample that runs on his end:
SET DEFINE ON;
DEFINE VAR1 = 100
SELECT *
FROM DB.TABLE1 a
WHERE
A.FIELD1 = &&VAR1 AND
ROWNUM < 10;
Trying to run from Data Point, and I get an invalid option error message. After doing some digging on the forums, it seems like this option (SET DEFINE ON;) isn’t available in Data Point. Is there another way to accomplish global variable definition without me receiving a prompt to key in the value? If so, I need it to work for a single variable (ie: A = 1, A.FIELD1 = &&VAR1) and for multiple values (ie: A IN (1,2,3), A.FIELD1 IN (&&VAR1)).
Thanks,
Chris
Found the option to execute the script in an external program. Selected that and it opens Toad’s Script Runner. The above script executed with no issue. I guess this is an exclusion in Toad Data Point. Is there any way to make this availabe in future Data Point updates?
It was an unintended regression to hide the action that runs external apps. I already entered QAT-3951 to fix for the next release. Support for SQL*Plus will always be done in the Script Runner App.
Thanks Debbie! So will Data Point in the future be able to process those variables/scripts without relying on the Script Runner App to execute? That would be great, especially during automation.
No. This is not on our road map. While Oracle is still our number one database type, supporting SQL*Plus does not seem to be a priority. I believe Toad for Oracle supports this.
have you tried it using a bind variable? just replace each instance of &&Var1 with :Var1 (you don’t need the Define Var1 anymore) and when you run the query it will prompt you for the value. If you use it in automation just use a Set Variable step to set the value to 100 prior to running the sql.
I agree, use a bind var by putting colon before bind var name. We will determine the datatype by the column it is joined to or you can set in the bind variable window.
I appreciate your responses Debbie & Greg!
I knew that we could define those bind variables through the automation process, my hope for this was for streamlining my sql and eliminating the need for redundancies within my queries. Also I was hoping to leverage things like code snippets and the auto-replace features in the editor itself to speed up my development time.