• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 315
  • Last Modified:

script to output table record info to a txt file

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
crishna1
Asked:
crishna1
  • 4
  • 3
  • 2
1 Solution
 
sdstuberCommented:
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
 
crishna1Author Commented:
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
 
sdstuberCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
crishna1Author Commented:
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
 
sdstuberCommented:
the batch file syntax is above too, it would be just one line
0
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
Mark GeerlingsDatabase AdministratorCommented:
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
 
crishna1Author Commented:
markgeer, thank you for the input , i think we already achieved what is required by sdstuber's suggestions.

Thanks!
0
 
crishna1Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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