I'm trying to store the result of a query in variable and then use the value in another query. The simplified script below works if I select all and hit F5. However, the result goes to the script output screen. If I select all and run statement to get a query result (which I want to export the data), the value from the variable is not passed through.
VARIABLE v_name number;
BEGIN
SELECT count(*)
INTO :v_name
FROM table;
END;
/
SELECT ID,
:v_name
FROM table
Thanks.
So I'm guessing you're using SQL Developer?
If you want to use "run statement", you have to pass the bind value at run time. So you need to note it down and pass it yourself.
Alternatively, you could run the whole thing as a script and use spool to send the output to a file:
create table t as
select rownum id from dual
connect by level <= 10;
VARIABLE v_name number;
BEGIN
SELECT count(*)
INTO :v_name
FROM T;
END;
/
spool out.log
SELECT ID, :v_name
FROM T;
spool offOut.log will hold the results of your query.
Of course, the real question here is:
Why do you need two statements?
In almost all situations you can combine them into one query. Either in a scalar subquery or by joining. For example, here you could do:
SELECT ID, (SELECT count(*) FROM T) ct
FROM T;
ID CT
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10
Or, even better, if you just want the count you don't even need a second query. Use count(*) over () and you'll get the total:
SELECT ID, count(*) over () ct
FROM T;
ID CT
1 10
2 10
3 10
4 10
5 10
6 10
7 10
8 10
9 10
10 10