Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

script to output table record info to a txt file

Posted on 2012-04-11
9
Medium Priority
?
307 Views
Last Modified: 2012-04-12
Dear experts,

I have a table with 10 columns. One of the column is LAST_UPDATE ( data type--NUMBER) which contains data like --789451600.

There are about 60 rows in the table and this is the only column that gets updated in the table , say once every couple of hours.

I need a batch file/script that would get max value of the LAST_UPDATE column ,
check if there are any records other than the max(LAST_UPDATE) value in that column and
if there are any ,it should output those value(s) and its corresponding column values into a txt file to a specified folder.

This database is located on a Windows Server. The script/batch file will be executed using as a task scheduler job on a regular basis.

Please let me know if any further information is required.

many thanks!
0
Comment
Question by:crishna1
[X]
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
  • 4
  • 3
  • 2
9 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 37834161
something like this?


spool 'c:\temp\olddata.txt';

select * from
(select t.*, max(last_update) over() max_last_update
from yourtable
)  where last_update != max_last_update;

spool off;
exit
0
 

Author Comment

by:crishna1
ID: 37834683
running that querry returned 10 rows with max LAST_UPDATE value. something like reverse of what was intended?

we need the column values for the rows that are other than max(last_update).

also, dont we need to make the conn to Oracle when the job kick's-off?
i am thinking that Oracle_home need to be set and then the connection string at the beggining of the file.

can you please add that syntax as well?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37835428
check the results again,  

the max_last_update shows the value of the last_update you are comparing those rose against.  If you don't want to see it in the results, simply list the columns you DO want to see instead of using * in the outer query


and yes that script is just the query portion.


you'll need to put that in a sql script  and call it with sqlplus.

either set the home and path or invoke it explicitly with something like this

c:\oracle\bin\sqlplus username/password@yourdb  @yourscript
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:crishna1
ID: 37835529
never mind, i see what you are saying , sorry for the confusion , thanks!

How about the systax for connecting to oracle etc. I am planning to set this up as a batch file and run it on a regular inetrval using Windows Task scheduler.

Thanks!
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37835647
the batch file syntax is above too, it would be just one line
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 37837330
You basically have only two options that are included with the Oracle database software (unless you add a reporting tool of some kind):
1. A *.SQL script that you run from SQL*Plus.  This can be automated to run from your O/S job scheduler (cron for UNIX/Linux or TaskScheduler for Windows).
2. A PL\SQL procedure that uses UTL_FILE to create an output file.  This output file will be created though on the database server (or possibly on NAS or SAN shared storage) but not directly on a client machine.

If you need help with one of these two options, let us know which one you prefer, and which server O/S you have.
0
 
LVL 35

Expert Comment

by:Mark Geerlings
ID: 37837335
I forgot to add this to option #2 above.  "This procedure can be scheduled in the database using either DBMS_JOB or DBMS_SCHEDULER."
0
 

Author Comment

by:crishna1
ID: 37838423
markgeer, thank you for the input , i think we already achieved what is required by sdstuber's suggestions.

Thanks!
0
 

Author Closing Comment

by:crishna1
ID: 37838440
to keep it simple , I had setup a Windows Scheduled Task to run a batch file , which contains the path to SQLPlus , Connection String to teh database and path to call the script.

The script contains the sql provided by sdtuber to obtain the desired result.

just FYI: this is on Oracle 9i.

Thank you!
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

618 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