title: How to Declare, Populate and Use a Variable using Oracle PL/SQL
body: <p>I swear this has been asked so many times previously, yet I cannot seem to apply other examples to my use case:</p>
  
  <p>First things first, this query will be executed as part of an Informatica SQL Source Qualifier, and in some circumstances, be passed-through from an SQL Server OpenQuery statement, so please be mindful of this, and
  that SQL Plus will not be used be used; Oracle SQL Developer is used only for code development.</p>
  
  <p>My history is primarily SQL Server &amp; Teradata, but as the title suggests, I now have a requirement where I need to declare, populate and use a variable in Oracle, all within the same procedure. <em>Not SP, so no In/Out declarations</em></p>
  
  <p>In SQL Server, this code will work as expected (line numbers added for clarity):</p>
  
  <pre><code>1.  Declare @MaxDate Int
  2.
  3.  With f_data (cal_period) As (Select 201904 As cal_period)
  4.
  5.  Select @MaxDate = Max(cal_period) From f_data
  6.
  7.  Select
  8.  Case
  9.      When (@MaxDate%100) &lt; 12 Then @MaxDate+1
  10.     Else (@MaxDate+100) - ((@MaxDate%100)-1)
  11. End As dt
  </code></pre>
  
  <ul>
  <li>Line 1: These are YYYYMM date periods defined as int</li>
  <li>Line 3: I am using an inline view (CTE) here for illustration, and to
  make it easier for you to copy and paste, but in reality, this is
  actually a physical control table, so would not normally be visible in the
  script.</li>
  <li>Line 5: Populates the parameter (SQL Server prefixes parameters with
  the at symbol) with the single-value resultset</li>
  <li>Line 7-11: Is simply the logic to progress the period by one, the
  percentage-mark in SQL Server is the Modulus function, Oracle is
  written as Mod(@MaxDate,100)</li>
  </ul>
  
  <p>For those unfamiliar with SQL Server, it does not need a reference table such as Dual ("Sys.Dual") in order to execute the query, such that for Oracle a "From Dual" statement is necessary on the missing Line 12</p>
  
  <p>My requirement is essentially a carbon-copy of the above T-SQL, so I need to declare a one-time use variable, to populate that variable with the results of an SQL query, and then to use this variable in a transformation - the result of which is captured to an Informatica and SSIS variable for later use.</p>
  
  <p>So far, I have tried declaring a variable, this seemed to work (by which I mean it didn't return an error):</p>
  
  <pre><code>Declare MaxPeriod Int;
  Begin
      Select 201904 Into MaxPeriod From Dual;
  End;
  </code></pre>
  
  <p>And populating from an SQL statement is also showing as successfully completed:</p>
  
  <pre><code>Declare MaxPeriod Int;
  Begin
      Select Max(MaxPeriodVal) Into MaxPeriod From CtrlTable;
  End;
  </code></pre>
  
  <p>Although I can't seem to get beyond this to actually test the variable as put.line statements fail, as do simple Case checks:</p>
  
  <pre><code>Declare MaxPeriod Int;
  Begin
      Select 201904 Into MaxPeriod From Dual;
  End;
  
  
  Select
  Case
      When 201904 = MaxPeriod Then 'Match'
      Else 'No Match'
  End As dteChk
  From Dual;
  </code></pre>
  
  <p>I have attempted to prefix the MaxPeriod in the check with a colon, and, to have prefixed,suffixed/both with an ampersand eg :MaxPeriod; &MaxPeriod; MaxPeriod&amp;; &amp;MaxPeriod&amp;
  All of which failed.</p>