In PL/SQL, how do I use a varaible in a SELECT without using an INTO?

Question

I'm experimenting with queries in an Oracle database (Using Oracle SQl Developer and PL/SQL Developer)

If I run a simple query: (SELECT * FROM myTable WHERE id = 1234) the results display in a nice grid in a lower pane of the SQL tool.

Now, how do I rewrite that query, using a variable for the 1234, And STILL have the results spill out into the results pane. Everything I've tried either won't compile, or requires me to do a SELECT...INTO and then manually output the results.

I just want to do something along the lines of this, and have it work:

 DECLARE p0 = 1234;
 SELECT * FROM myTable WHERE id = p0;

UPDATE: In the actual query I'm working on, the variables will be more like:

 DECLARE p0 = to_date('1/15/2014 7:11:05 AM','MM/DD/YYYY HH:MI:SS PM');
         p1 = p0 + .0007; -- one minute later.

So being able to write that in code is important.

Using `:p0` has it ask me the value to use in a pop-up dialog box, instead of using the value in the code. — James Curran, Aug 20 '14 at 19:00

tbone · Answer 1 · 2014-08-20T19:56:02.053

In Toad (or sqlplus or SQL Developer) you would do:

define x='20140820';
select to_date(&x, 'YYYYMMDD') from dual;

And run as a Script (important).

In Toad, the Output grid would be:

old: select to_date(&x, 'YYYYMMDD') from dual
new: select to_date(20140820, 'YYYYMMDD') from dual

TO_DATE(20140820,'YYYYMMDD')
----------------------------
20-AUG-2014                 
1 row selected.

And Grid1 would just show the results in table grid format.

Note that you can suppress the old/new in output by doing:

set verify off

at top of script.

score 1 · Answer 2 · answered Aug 20 '14 at 19:59

One thing you can try is to create a bind variable named cur, for example, of type REFCURSOR, use OPEN :cur FOR SELECT ... in your PL/SQL block and then PRINT the cursor out. Here's an example with a very simple query:

VARIABLE cur REFCURSOR

BEGIN
  OPEN :cur FOR SELECT * FROM DUAL;
END;
/

PRINT cur

This works in SQL*Plus and SQL Developer, although in the latter it will only work if you use the 'Run Script (F5)' button, not the 'Run Statement (Ctrl+Enter)' button. Also, you don't get a table with the results in, just the query output in preformatted text. Also, it won't necessarily work in other tools, as VARIABLE and PRINT are not part of SQL nor PL/SQL - SQL*Plus and SQL Developer both understand them and can interpret them.

In PL/SQL Developer you can use a Test Window for this kind of thing. For example, enter into a Test Window a PL/SQL block such as

BEGIN
  OPEN :cur FOR SELECT * FROM DUAL;
END;

Then, either add the variable cur of type 'Cursor' to the variables table at bottom of the window, (or choose Scan Variables from the context menu. Note that PL/SQL Developer won't necessarily get the type right; you may well still have to change the type. Once you run the block, the results from the cursor can be obtained in a separate window by clicking the '...' button in the row in the variables table for cur.

score 0 · Answer 3 · answered Jul 19 '16 at 13:58

As a minor variation on Luke's excellent answer, you don't even need a ref cursor if you just want to display a variable's value. Put this in a Test window:

declare
    p0 date := to_date('1/15/2014 7:11:05 AM', 'MM/DD/YYYY HH:MI:SS PM');
begin
    :p1 := p0 + .0007;
end;

then set up the p1 bind variable in the lower panel. Executing it will display the value.

In PL/SQL, how do I use a varaible in a SELECT *without* using an INTO?

3 Answers3

In PL/SQL, how do I use a varaible in a SELECT without using an INTO?