Oracle SQL: Declaring variables to use in queries & subqueries

Question

I'm somewhat new to this Oracle database and I've inherited a large-ish query with several sub-queries. I'd like to optimize it by declaring a few variables to reference later on within the queries, but I can't seem to get it right.

Here is an extremely dumbed-down version of my query that, if I can get this in the right format, I think I can get the full version working:

DECLARE
  outage_start_time INTEGER := 1456894800;
  outage_end_time INTEGER := 1457586000;
  DST_offset INTEGER := 0;
  time_zone_offset INTEGER := 4;
BEGIN
  WITH subquery AS (
  SELECT DISTINCT
    mytable.tickets AS "TicketID"
  FROM mytable
  WHERE
    mytable_start_time >= outage_start_time AND
    mytable_end_time < outage_end_time
  )
  SELECT DISTINCT
    subquery."TicketID" AS "Ticket ID",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24),'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
    --other stuff
  FROM mytable
  LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
  ;
END;

The error that I'm getting is:

Error starting at line : 1 in command -
DECLARE
  outage_start_time INTEGER := 1456894800;
  outage_end_time INTEGER := 1457586000;
  DST_offset INTEGER := 0;
  time_zone_offset INTEGER := 4;
BEGIN
  WITH subquery AS (
  SELECT DISTINCT
    mytable.tickets AS "TicketID"
  FROM T528
  WHERE
    mytable.start_time >= outage_start_time AND
    mytable.end_time < outage_end_time
  )
  SELECT DISTINCT
    subquery."TicketID" AS "Ticket ID",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
    --other stuff
  FROM T528
  LEFT OUTER JOIN subquery ON mytable.tickets = subquery."TicketID"
  ;
END;
Error report -
ORA-06550: line 7, column 3:
PLS-00428: an INTO clause is expected in this SELECT statement
06550. 00000 -  "line %s, column %s:\n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

It looks like my errors are coming at lines 1 and 7, so I clearly have no idea how to write this properly. A bit of help would be GREATLY appreciated. Thanks!

Can you please post the structure of your table ( the relevant fields at least) and the error you are getting? — Aleksej
– Aleksej, Commented Mar 16, 2016 at 14:08
What error are you getting? anyway , you are supposed to use single quote mark for column names ' . Also, why subquery."TicketID" and not subquery.ticketID? — sagi
– sagi, Commented Mar 16, 2016 at 14:12
sagi: the double-quotes for the column names in the subquery is because that's what works. I don't get it, frankly (I come from a basic mySQL background), but that's what works in the existing query, so whatever. — Jim Johnson
– Jim Johnson, Commented Mar 16, 2016 at 14:23
@JimJohnson - you can read about quoted identifiers in the docs; if the column was defined like that then you have to use the quotes. Do you actually want to be using PL/SQL for this - what will you do with the results of the query? — Alex Poole
– Alex Poole, Commented Mar 16, 2016 at 16:52

Aleksej · Accepted Answer · 2016-03-16 15:23:27Z

3

You need an INTO clause, to say in which variables to fetch the result of your query:

SELECT DISTINCT
    subquery."TicketID" AS "Ticket ID",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.start_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage Start",
    TO_CHAR(TO_DATE('1970/01/01 00:00:00','YYYY/MM/DD HH24:MI:SS')+(mytable.end_time/((60*60)*24)-(time_zone_offset + DST_offset)/24), 'MM/DD/YYYY HH:MI:SS PM') AS "Outage End"
    --other stuff
INTO variable1, variable2, ...
  FROM T528

So, define a variable for each column you need to fetch, with corresponding type, and add the INTO clause to fetch the result of your query in your variables.

The solution above works well if your query returns exactly one row, fetching the values into scalar variables. If your query returns more than one row, you need some array variables, to handle all the values; one way to fetch it could be:

define a type as an array; you may need arrays of numbers, varchar2, ..., dending on the type of the fetched columns
define a variable for each column you need to fetch
add a BULK COLLECT INTO clause, to say it to massively fetch all the rows into the array variables

After the statement, youll'have your array variables filled with the result set of your query

For example:

declare
  type tyTabNuber is table of number index by pls_integer;
  type ty...
  --
  vTabNumber tyTabNumber;
  ...
begin
  select ...
  BULK COLLECT INTO vTabNumber, ...
  FROM ...
  ...
end

edited Mar 16, 2016 at 15:23

answered Mar 16, 2016 at 14:32

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jim Johnson Over a year ago

Eek.. you really lost me in that second part.

Aleksej Over a year ago

@JimJohnson, Edited my answer with some more informations

Collectives™ on Stack Overflow

Oracle SQL: Declaring variables to use in queries & subqueries

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related