Solved

script to output table record info to a txt file

Posted on 2012-04-11
9
296 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 73

Accepted Solution

by:
sdstuber earned 500 total points
Comment Utility
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
Comment Utility
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 73

Expert Comment

by:sdstuber
Comment Utility
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
 

Author Comment

by:crishna1
Comment Utility
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
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 73

Expert Comment

by:sdstuber
Comment Utility
the batch file syntax is above too, it would be just one line
0
 
LVL 34

Expert Comment

by:Mark Geerlings
Comment Utility
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 34

Expert Comment

by:Mark Geerlings
Comment Utility
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
Comment Utility
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
Comment Utility
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

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.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
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…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

771 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

10 Experts available now in Live!

Get 1:1 Help Now