nike_golf
asked on
Can I create a variable from a resultset?
I would like to use a variable to hold the resultset of query lets just say the sysdate for the moment and then spool it off with my the reult from my other query.
Is this possible?
NG,
Is this possible?
NG,
set feedback off
set verify off
set head off
set echo off
set linesize 30
set pages 0
Declare Vsysdate
Vsysdate = select sysdate into Vsysdate from dual;
spool W:\scripts\Wk1.csv
Prompt Vsysdate
SELECT * from table 1;
spool off
quit
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well I tried the following without success..
col Vsysdate new_value Vsysdate
select sysdate Vsysdate from dual;
spool W:\scripts\Wk1.csv
Prompt Wkly2011
Prompt Vsysdate
Prompt 37
'------------------------- ---------- -- Results -------------------------- -----
wkly2011
Vsysdate
37
col Vsysdate new_value Vsysdate
select sysdate Vsysdate from dual;
spool W:\scripts\Wk1.csv
Prompt Wkly2011
Prompt Vsysdate
Prompt 37
'-------------------------
wkly2011
Vsysdate
37
ASKER
I ended up having to use a bind variable and was able to get it to work.
Thanks,
Thanks,
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for nike_golf's comment http:/Q_27311816.html#36549520
for the following reason:
Self answered
Accepted answer: 0 points for nike_golf's comment http:/Q_27311816.html#36549520
for the following reason:
Self answered
Just because you chose a different approach does not mean what I posted does not work. I have to object.
>>Where does the SELECT statement set the sysdate to the variable Vsysdate?
in the NEW_VALUE of the column command.
>>Prompt Vsysdate
Look at what I posted.
It should be: Prompt &Vsysdate
Did you run what I posted to see it work?
>>Where does the SELECT statement set the sysdate to the variable Vsysdate?
in the NEW_VALUE of the column command.
>>Prompt Vsysdate
Look at what I posted.
It should be: Prompt &Vsysdate
Did you run what I posted to see it work?
ASKER
No offense intended.
I revisited your solution and it does work...
I will make the change and award the points.
NG,
I revisited your solution and it does work...
I will make the change and award the points.
NG,
I apologize if you thought I was offended. Intent is so hard to 'type'.
I was just trying to point out the solution was what you asked.
Glad it worked for you.
I was just trying to point out the solution was what you asked.
Glad it worked for you.
ASKER
No problem.
I now have 2 solutions... ;-)
NG,
I now have 2 solutions... ;-)
NG,
ASKER
Where does the SELECT statement set the sysdate to the variable Vsysdate?