0

Using an Oracle database (currently 18g) through dbVisualizer, I have SQL that has parameters peppered throughout. Some of the parameters are in multiple places. The query I'm working with right now has 13 distinct parameter names with 56 total references in the code. I have some standard values I use for testing. I don't want to use the interface dbVisualizer presents for entering them manually. I want to use a text editor to enter my test values and put them all at the beginning of my code so I don't risk fat-fingering my SQL logic and so I can delete or comment out that section when I'm done testing.

I've done this for decades in SQL Server...

declare @varname varchar(50)
set @varname = 'asdf'

select @varname

...but I don't see a way to do this in Oracle. I have read many posts regarding how to do this using PL/SQL or SQL*Plus (like https://dba.stackexchange.com/questions/3652/how-do-i-declare-and-use-variables-in-oracle) that don't work for my environment. I have also seen posts with accepted answers that just don't work (like Declare a variable in Oracle SQL to use in a query).

How can I declare variables in Oracle SQL through dbVisualizer as easily as I can in SQL Server using SSMS?

dougp
  • 2,810
  • 1
  • 8
  • 31
  • By "answers that just don't work", you mean the syntax used by other client tools is not available in DBVisualizer. – William Robertson Jan 29 '22 at 10:47
  • https://stackoverflow.com/questions/19371822/declare-a-variable-in-oracle-sql-to-use-in-a-query does not specify a query tool. I assumed the answer was generic to Oracle SQL. – dougp Feb 01 '22 at 00:04
  • I agree that answer should have said what client tools it will work in (SQL\*Plus and compatible environments, not DBVis apparently), and the question it was answering should have mentioned the target environment as well, instead of just "I am using an unspecified development tool, how do I define a client-side variable?" There seem to be a lot of assumptions about that kind of detail, unfortunately. – William Robertson Feb 01 '22 at 18:45

1 Answers1

1

I have needed this functionality for about 3 years now. Since I finally found enough information to figure it out, and since I've never found this info on StackExchange, I'm asking and answering my own question in the hopes that others don't suffer needlessly as long as I have.

I found a post (https://support.dbvis.com/support/discussions/topics/1000076719) describing how to do this, but it looks like some of the post got lost in a forum upgrade. Fiddling around a bit I found that this will work:

@echo ${ MY_VARCHAR1 ||My Value|| String }$
@echo ${ MY_VARCHAR2 || My Other Value || String }$
@echo ${ MY_INTEGER || 13467 || Integer }$
@echo ${ MY_DATE || 1983-08-25 || Date }$
;

SELECT ${MY_VARCHAR1}$ "VarcharVal"
, REPLACE(${MY_VARCHAR2}$, ' ', '[SPACE]') "VarcharValWithLeadingAndTrailingSpaces"
, ${MY_INTEGER}$ "IntegerVal"
, ${MY_DATE}$ "DateVal"
FROM DUAL
;

Notice the results for MY_VARCHAR2. While integers and dates are handled cleanly, you want to be sure to not have spaces around the value for string data.

Update: Adjusted code per dbVisualizer docs. Same result, though.

dougp
  • 2,810
  • 1
  • 8
  • 31
  • This seemed promising with the sample code and simple queries. Now that I'm into non-trivial code (multiple common table expressions, one recursive + real business logic) I'm getting `java.sql.SQLException: Invalid column index`. This appears to be a dbVis error, not an Oracle error. The code dbVis says it produced works only after massaging (adding date casts and quotes) the parameter values. – dougp Feb 08 '22 at 19:12
  • Just tried your query as-is in DbVisualizer and it runs fine. – roger Feb 09 '22 at 11:55