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!
crishna1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.