Can I create a variable from a resultset?

Posted on 2011-09-16
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?

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

Open in new window

Question by:nike_golf
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
LVL 77

Accepted Solution

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
LVL 13

Author Comment

ID: 36548799

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

LVL 13

Author Comment

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 13

Author Comment

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

LVL 13

Author Comment

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
LVL 77

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?
LVL 13

Author Comment

ID: 36550096
No offense intended.

I revisited your solution and it does work...

I will make the change and award the points.

LVL 77

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.
LVL 13

Author Comment

ID: 36551926
No problem.

I now have 2 solutions... ;-)


Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
how to trim oracle sql sentence in unix 17 70
Select and Insert Query running slow 4 58
PL SQL Developer 7 71
date show only hh:mm 2 38
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

749 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