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?
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;
/
