Can i use a variable in oracle sql script?

Question

I am using a SQL script to spool data and load data into XML

I want to check whether I can use variable in the SQL script which get it value from another SQL query.

Something like this:

var1= select count(1) from emp

spool filename


select * from dept where empcnt=var1

Will it work? Is this the best way or any other way of doing it?

Answer 1

If you are using SQLplus, and you only want SQL ( not PLSQL) you can do something like the following, with bind variables:

spool  yourFile
variable var1 number
select count(1) into :var1 from dual;
select * from dual where rownum = :var1;

Another way could be with substitution variables:

spool yourFile
column var1 new_value valueFor_var1
select count(1) as valueFor_var1 from dual;
select * from dual where rownum = &var1;

If you can use a PLSQL block, you can do:

spool yourFile
declare
    var1 number;
    vDummy varchar2(100);
begin
    select count(1) into var1 from dual;
    select dummy into vDummy from dual where rownum = var1;
    dbms_output.put_line(vDummy);
end;
/