Solved

Can I create a variable from a resultset?

Posted on 2011-09-16
9
380 Views
Last Modified: 2012-05-12
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
Comment
Question by:nike_golf
  • 6
  • 3
9 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36548677
declare is PL/SQL.  Not sqlplus.

Try this:

col sysdate new_value Vsysdate

select sysdate from dual;
prompt &Vsysdate
0
 
LVL 13

Author Comment

by:nike_golf
ID: 36548799
hmmm...

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

0
 
LVL 13

Author Comment

by:nike_golf
ID: 36548862
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
 
LVL 13

Author Comment

by:nike_golf
ID: 36549520
I ended up having to use a bind variable and was able to get it to work.

Thanks,
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 13

Author Comment

by:nike_golf
ID: 36549920
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
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36549921
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
 
LVL 13

Author Comment

by:nike_golf
ID: 36550096
No offense intended.

I revisited your solution and it does work...

I will make the change and award the points.

NG,
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36550301
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
 
LVL 13

Author Comment

by:nike_golf
ID: 36551926
No problem.

I now have 2 solutions... ;-)

NG,
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
querying by the sum of a column in decimal 7 56
export Oracle diagram from Oracle DB including VIEWS 8 105
Oracle SQL 6 48
su - oracle could not open session 6 52
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now