CODEWITHSUNDEEP
oracle-databasevariables
Steve Martin
Steve Martin

Reputation: 109

How to Declare, Populate and Use a Variable using Oracle PL/SQL

I swear this has been asked so many times previously, yet I cannot seem to apply other examples to my use case:

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.

My history is primarily SQL Server & 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. Not SP, so no In/Out declarations

In SQL Server, this code will work as expected (line numbers added for clarity):

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) < 12 Then @MaxDate+1
10.     Else (@MaxDate+100) - ((@MaxDate%100)-1)
11. End As dt

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

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.

So far, I have tried declaring a variable, this seemed to work (by which I mean it didn't return an error):

Declare MaxPeriod Int;
Begin
    Select 201904 Into MaxPeriod From Dual;
End;

And populating from an SQL statement is also showing as successfully completed:

Declare MaxPeriod Int;
Begin
    Select Max(MaxPeriodVal) Into MaxPeriod From CtrlTable;
End;

Although I can't seem to get beyond this to actually test the variable as put.line statements fail, as do simple Case checks:

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;

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&; &MaxPeriod& All of which failed.

Upvotes: 1

Views: 1278

Answers (2)

Popeye
Popeye

Reputation: 35930

You can use substitution variable using define in sql*plus as following.

Define MaxPeriod := 201904
Select
Case
    When &MaxPeriod = MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

Cheers!!

Upvotes: 0

kfinity
kfinity

Reputation: 9091

The basic issue is a variable scope problem. You're declaring MaxPeriod within the context of a PL/SQL anonymous block, so it will disappear (fall out of scope) when the block ends on line 4.

You could put your entire query inside the PL/SQL block, but there's not an easy way to return an entire result set from a PL/SQL block, so I don't think you want that.

I don't know how your Oracle driver handles native queries, but this might work:

var MaxPeriod number; -- bind variable declared as global scope for this script

Begin -- one of several ways to assign values to bind variables
    :MaxPeriod := 201904;
End;
/

Select
Case
    When 201904 = :MaxPeriod Then 'Match'
    Else 'No Match'
End As dteChk
From Dual;

If the var syntax doesn't work for you to declare a SQL bind variable, then you may have to look into some other way of passing a bind variable for the query string. You could probably pass a null value (for a number datatype, anyway) and then overwrite it in the SQL script.

Alternately, in your original example code, I think I'd use a CTE or an inline view instead of a variable anyway.

With f_data As (Select 201904 As cal_period from dual)
Select
Case
      When Mod(MaxDate,100) < 12 Then MaxDate+1
     Else (MaxDate+100) - (Mod(MaxDate,100)-1)
End As dt
from (Select Max(cal_period) as MaxDate From f_data) mp

Upvotes: 1

Related Questions