Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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,
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

Open in new window

0
nike_golf
Asked:
nike_golf
  • 6
  • 3
1 Solution
 
slightwv (䄆 Netminder) Commented:
declare is PL/SQL.  Not sqlplus.

Try this:

col sysdate new_value Vsysdate

select sysdate from dual;
prompt &Vsysdate
0
 
nike_golfAuthor Commented:
hmmm...

Where does the SELECT statement set the sysdate to the variable Vsysdate?

0
 
nike_golfAuthor Commented:
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
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
nike_golfAuthor Commented:
I ended up having to use a bind variable and was able to get it to work.

Thanks,
0
 
nike_golfAuthor Commented:
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
0
 
slightwv (䄆 Netminder) Commented:
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?
0
 
nike_golfAuthor Commented:
No offense intended.

I revisited your solution and it does work...

I will make the change and award the points.

NG,
0
 
slightwv (䄆 Netminder) Commented:
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.
0
 
nike_golfAuthor Commented:
No problem.

I now have 2 solutions... ;-)

NG,
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now