Create simple PL/SQL variable - Use Variable in WHERE clause

Question

Thanks for looking...

I've spent hours researching this and I can't believe it's that difficult to do something in PL/SQL that is simple in TSQL.

I have a simple query that joins 2 tables:

     Select DISTINCT
      to_char(TO_DATE('1899123000', 'yymmddhh24')+ seg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date"
    , cd.CODE
    , EMP.ID
    , EMP.SHORT_NAME

    FROM
     EWFM.GEN_SEG seg join EWFM.SEG_CODE cd ON seg.SEG_CODE_SK = cd.SEG_CODE_SK
     join EMP on seg.EMP_SK = EMP.EMP_SK
    where NOM_DATE = vMyDate;

I use Toad Date Point and I'm querying against an Oracle Exadata source. The resulting query will be dropped into a visualization tool like QlikView or Tableau. I'd like to create a simple variable to use the the WHERE clause as you can see in the code.

In this example, NOM_DATE is an integer such as 42793 (2/27/2017) as you can see in the first row "Record Date". Nothing new here, not very exciting... Until... I tried to create a variable to make the query more dynamic.

I've tried a surprising variety of examples found here, all have failed. Such as:
declare myDate number(8); Begin myDate := 42793; --Fail ORA-06550 INTO Clause is expected

variable nomDate NUMBER
DEFINE nomDate = 42793
EXEC : nomDate := ' & nomDate'
...where NOM_DATE = ( & nomDate) ;

--ORA-00900: invalid SQL statement

and

variable nomDate NUMBER;
EXEC nomDate := 42793;
select count(DET_SEG_SK) from DET_SEG
where NOM_DATE = :nomDate;

--ORA-00900: invalid SQL statement

and several more.. hopefully you get the idea. I've spent a few hours researching stackoverflow for a correct answer but as you can see, I'm asking you. From simple declarations like "Var" to more complex " DECLARE, BEGIN, SELECT INTO...." to actually creating Functions, using cursors to iterate the output.... I still can't make a simple variable to use in a Where clause.

Please explain the error of my ways.

--Forlorn SQL Dev

Firstly, I assume you mean PL/SQL, not TSQL. Secondly, I tried you first example, and I do not see a problem; it works for me. So I think it's must be something very simple or very bizarre ! Can you cut/paste your whole (first) example? — BobC, Mar 01 '17 at 19:11
Inside PL/SQL (a named package/procedure/function etc) you can just declare variables in the `declare` section and then refer to them wherever you like. Your example beginning `declare mydate number(8);` works fine (just needs an `end;`) and I can't see how it could give `INTO clause is expected` since that only applies to `select` statements. The `variable` and `define` syntax is really from the SQL*Plus command line tool which is emulated in various ways by desktop applications so you may need to explain how you are running it. — William Robertson, Mar 01 '17 at 19:14
My guess is that the problem is not the variable declaration but the report output - something like http://stackoverflow.com/questions/351489/is-it-possible-to-output-a-select-state-from-a-pl-sql-block/351752 — William Robertson, Mar 01 '17 at 19:19
BobC: here's the first example in it's entirety ' declare myDate number(8); Begin myDate := 42793; SELECT DISTINCT to_char(TO_DATE('1899123000', 'yymmddhh24')+ seg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date" , cd.CODE , EMP.ID , EMP.SHORT_NAME , seg.MEMO , seg.AUDIT_INS_DATE FROM EWFM.GEN_SEG seg join EWFM.SEG_CODE cd ON seg.SEG_CODE_SK = cd.SEG_CODE_SK join EMP on seg.EMP_SK = EMP.EMP_SK where NOM_DATE = myDate; end; ' Thanks for the interest — mf.cummings, Mar 01 '17 at 19:43
William Roberts: I agree the DECLARE format seems most likely but of the several different attempts I tried, I received the same error. Here's the code: 'Declare myDate number(8) := 42793; BEGIN Select DISTINCT to_char(TO_DATE('1899123000', 'yymmddhh24')+ eg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date" , cd.CODE , EMP.ID , EMP.SHORT_NAME FROM EWFM.GEN_SEG seg join EWFM.SEG_CODE cd ON seg.SEG_CODE_SK = cd.SEG_CODE_SK join EMP on seg.EMP_SK = EMP.EMP_SK where NOM_DATE = myDate; END;' ORA-06550: line 6, column 5: PLS-00428: an INTO clause is expected in this SELECT statement — mf.cummings, Mar 01 '17 at 19:57

MT0 · Answer 1 · 2017-03-01T20:32:56.457

1

VARIABLE vMyDate NUMBER;

BEGIN
  :vMyDate  := 42793;
END;
/

-- or
-- EXEC :vMyDate := 42793;

SELECT DISTINCT
       TO_CHAR( DATE '1899-12-30' + seg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date"
     , cd.CODE
     , EMP.ID
     , EMP.SHORT_NAME
FROM   EWFM.GEN_SEG seg
       join EWFM.SEG_CODE cd
       ON seg.SEG_CODE_SK = cd.SEG_CODE_SK
       join EMP
       on seg.EMP_SK = EMP.EMP_SK
WHERE  NOM_DATE = :vMyDate;

edited Mar 01 '17 at 20:32

answered Mar 01 '17 at 20:25

MT0

86,097
7
42
90

So Close! I typed it almost exactly omitting the / after END; (I was under the impression that was a SQL* command to treat the lines as a block) and it failed. I did it again with the / and the Bind Variable box popped up 2x, I entered the correct value in it both times and it ran as expected. I have more to learn about binding variables but this is the closest to correct answer yet. – mf.cummings Mar 01 '17 at 21:13

score 1 · Accepted Answer · answered Mar 03 '17 at 19:24

Since you are using an implicit cursor, you have to select then INTO variables. Now I d not know the data types of you variables, so I have just guessed in this example below, but hopefully you get the point.

Two other things I should mention

Why are you TO_CHARing you DATE. Just use a DATE datatype. Also, I think your format mask is wrong too 1899123000 does not match yymmddhh24.
In explicit cursor expects exactly one row; no rows and you get NO_DATA_FOUND; more than one and you get TOO_MANY_ROWS

Declare 
   myDate number(8) := 42793; 
    /* These 4 variable data types are a guess */
   v_record_date varchar2(8);
   v_cd_code varchar2(10);
   v_emp_id number(4);
   v_emp_short_name varchar2(100);
 BEGIN 
 Select DISTINCT to_char(TO_DATE('1899123000', 'yymmddhh24')
                     + eg.NOM_DATE, 'mm/dd/yyyy') AS "Record Date" 
 , cd.CODE 
 , EMP.ID 
 , EMP.SHORT_NAME 
 INTO v_record_date, v_cd_code, v_emp_id, v_emp_short_name
 FROM EWFM.GEN_SEG seg 
 join EWFM.SEG_CODE cd 
   ON seg.SEG_CODE_SK = cd.SEG_CODE_SK 
 join EMP 
     on seg.EMP_SK = EMP.EMP_SK 
 where NOM_DATE = myDate; 
 END;
 /

score 0 · Answer 3 · answered Mar 01 '17 at 19:11

0

You put the variables with getter and setter in a package.

Then use a view that uses the package getter

Personally I prefer to use a collection that way I can do a select * from table (packagage.func(myparam))

answered Mar 01 '17 at 19:11

Frank

1
1

Create simple PL/SQL variable - Use Variable in WHERE clause

3 Answers3