PL/SQL Using a Variable in an Adhoc SELECT

Question

I'm (attempting) to write a query which will use the same date at several points in the query. I'd like to use a variable to make it easier to run the query for different dates. How would I do that? I'm getting assorted errors when I try to execute the query as it is.

ORA-06550: line 25, column 5: PLS-00103: Encountered the symbol "END" when expecting one of the following:

DECLARE 
    CalendarDate Date := TO_DATE('1/20/2015','MM/DD/YYYY');
BEGIN

SELECT 
    TIME_OF_DAY, COUNT(TIME_OF_DAY) ISSUE_ACTIVITY_COUNT
FROM
    (SELECT
        case 
            when to_char(CREATED_DT,'mi') between '00' and '14' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '15' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '15' and '29' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '30' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '30' and '44' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '45' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '45' and '59' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '60' minute,'HH24:MI')
        end as TIME_OF_DAY
    FROM
        CE.ISSUE_ITEM i
    WHERE
        (TRUNC(i.CREATED_DT) = CalendarDate))
    GROUP BY
        TIME_OF_DAY
    ORDER BY
        TIME_OF_DAY;

EXECUTE IMMEDIATE     
END;

The "execute immediate" construct execute a dynamic sql and the syntax you have used is wrong. https://docs.oracle.com/cd/B19306_01/appdev.102/b14261/executeimmediate_statement.htm. What is the final goal of your code? — bdn02, Jan 18 '16 at 20:18
What are you trying to do with the SELECT statement in your procedure? As noted by @bdn02, the `EXECUTE IMMEDIATE` is A) in the wrong place, and B) unnecessary as there's no dynamic SQL being used here. Are you trying to open a cursor on the SELECT statement in your procedure and return that? Are you trying to execute this SELECT statement in a query tool and have the results displayed? Or something else..? Please edit your question and add further explanation. Thanks. — Bob Jarvis - Слава Україні, Jan 18 '16 at 20:28
additionally if this was dynamic SQL you would need to alias the inline view. Most PL/SQL doesn't like having unaliased inline views. Also unlike t-SQL, PL/SQL requires the creation of a refcursor.or sysrefcursor to return a recordset which I think is what you're trying to do.. http://stackoverflow.com/questions/2153053/how-to-return-a-resultset-cursor-from-a-oracle-pl-sql-anonymous-block-that-exe — xQbert, Jan 18 '16 at 20:40

score 1 · Answer 1 · answered Jan 18 '16 at 21:16

To use a date several times in the query use WITH clause:

with t as (select to_date('1/20/2015','mm/dd/yyyy') calendar_date from dual)
select t.calendar_date, <other columns>
  from CE.ISSUE_ITEM i, t
 where trunc(i.created_dt) = t.calendar_date

I hope my guess about what you need is correct.

score 0 · Answer 2 · answered Jan 18 '16 at 21:34

The error that you've made

... is related to the EXECUTE IMMEDIATE statement, which simply doesn't make any sense there. I don't know what you wanted to do with that, so I won't fix your answer, but show you alternatives:

Reusing bind variables

You don't need to use PL/SQL for that. Use an ordinary named bind variable:

SELECT 
    TIME_OF_DAY, COUNT(TIME_OF_DAY) ISSUE_ACTIVITY_COUNT
FROM
    (SELECT
        case 
            when to_char(CREATED_DT,'mi') between '00' and '14' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '15' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '15' and '29' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '30' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '30' and '44' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '45' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '45' and '59' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '60' minute,'HH24:MI')
        end as TIME_OF_DAY
    FROM
        CE.ISSUE_ITEM i
    WHERE
        (TRUNC(i.CREATED_DT) = :CalendarDate)) -- Bind variable here
    GROUP BY
        TIME_OF_DAY
    ORDER BY
        TIME_OF_DAY;

You can then re-use that bind variable throughout your SQL statement.

Using common table expressions (subquery factoring in Oracle-speak)

In principle, you could use the following kind of query to introduce variables for the scope of a single statement:

WITH vars (CalendarDate) AS (
    SELECT DATE '2015-01-20' FROM DUAL
)
SELECT 
    TIME_OF_DAY, COUNT(TIME_OF_DAY) ISSUE_ACTIVITY_COUNT
FROM
    (SELECT
        case 
            when to_char(CREATED_DT,'mi') between '00' and '14' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '15' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '15' and '29' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '30' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '30' and '44' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '45' minute,'HH24:MI')
            when to_char(CREATED_DT,'mi') between '45' and '59' then TO_CHAR(trunc(CREATED_DT,'HH24') + interVal '60' minute,'HH24:MI')
        end as TIME_OF_DAY
    FROM
        CE.ISSUE_ITEM i CROSS JOIN vars
    WHERE
        (TRUNC(i.CREATED_DT) = vars.CalendarDate)) -- "Variable" from CTE here
    GROUP BY
        TIME_OF_DAY
    ORDER BY
        TIME_OF_DAY;

I personally find this a bit obscure, though. In order to access the variables, you have to cross join them, which isn't really very readable.

PL/SQL Using a Variable in an Adhoc SELECT

2 Answers2

The error that you've made

Reusing bind variables

Using common table expressions (subquery factoring in Oracle-speak)