48098

How do you assign the result of an expression to an SQL substitution variable?

Question:

Can you evaluate an expression and assign the result to a substitution variable?

In my case, I need to call an old script which contains a substitutions variable. I need to calculate a value for that variable before calling the script. I'm using Oracle SQL and SQL*Plus

Here's the basic problem:

def this_num = 2+2 @old_script

Inside old_script.sql

select '&this_num' from dual; -- Probably shouldn't change this

Yields: '2+2'

Is there a way to force evaluation so that the substitution variable gets the <strong>result</strong> of an expression rather than the expression itself?

Answer1:

def this_num = 2+2 @old_script

In the old_script, you can say

select &this_num from dual;

You don't need to use '' around the variable name. This should work.

Answer2:

<h2>The Working Solution</h2>

I found a working answer here: <a href="https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5" rel="nofollow">https://blogs.oracle.com/opal/entry/sqlplus_101_substitution_varia#2_5</a>

Here's the relevant text.

<blockquote>

2.5 Storing a Query Column Value in a Substitution Variable

Data stored in the database can be put into substitution variables:

SQL> column last_name new_value mynv SQL> select last_name from employees where employee_id = 100;

The NEW_VALUE option in the COLUMN command implicitly creates a substitution variable called "mynv". The variable is not physically created until a query references the column LAST_NAME. When the query finishes, the variable "mynv" holds the last retrieved value from column "last_name":

SQL> define mynv DEFINE mynv = "King" (CHAR) </blockquote>

So you do it like this:

column DUMMY_COLUMN_NAME new_value THIS_NUM select 2+2 DUMMY_COLUMN_NAME from dual; select '&&THIS_NUM' from dual; '4' ------------ 4

Tada!

<h2>The Evil Solution</h2>

For entertainment value, here's a really evil workaround which would break if the variable ever happens to be used outside of quotes:

define this_num = "' || 2+2 ||'" (CHAR)

Then:

select '&&this_num' from dual;

evaluates to:

select '' || 2+2 ||'' from dual;

Which yields:

<h2>'</h2>

4

Recommend

  • MySQL select/insert/update, does column order matter?
  • SQL Where Clause with multiple values
  • Android: avoid calling onCreate() when back from another activity
  • Getting Facebook error 11
  • Middleman: referencing URL stored in a data file from markdown
  • ASP.NET MVC2 Error: No parameterless constructor defined for this object
  • R h2o.glm - issue with max_active_predictors
  • IE6 changes DOCTYPE to a bad one
  • Doctrine/Symfony entity generator and generating entity from one table
  • Default parameter as generic type
  • How to set ini file attributes during an Inno install
  • Yii2: Config params vs. const/define
  • Python CGI os.system causing malformed header
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • RectangularRangeIndicator format like triangular using dojo
  • C# - Serializing and deserializing static member
  • recyclerView does not call the onBindViewHolder when scroll in the view
  • Cross-Platform Protobuf Serialization
  • WinForms: two way TextBox problem
  • Perl system calls when running as another user using sudo
  • Arrow is showed instead of the material design version hamburger icon. Why doesn't syncState in
  • Jquery - Jquery Wysiwyg return html as a string
  • R: gsub and capture
  • Calling of Constructors in a Java
  • jqPlot EnhancedLegendRenderer plugin does not toggle series for Pie charts
  • Arrays break string types in Julia
  • Traverse Array and Display in markup
  • Transpose CSV data with awk (pivot transformation)
  • Comma separated Values
  • Matrix multiplication with MKL
  • Revoking OAuth Access Token Results in 404 Not Found
  • WPF Applying a trigger on binding failure
  • Why can't I rebase on to an ancestor of source changesets if on a different branch?
  • Hits per day in Google Big Query
  • Java static initializers and reflection
  • File not found error Google Drive API
  • XCode 8, some methods disappeared ? ex: layoutAttributesClass() -> AnyClass
  • Converting MP3 duration time
  • How to load view controller without button in storyboard?