Solved

script to output table record info to a txt file

Posted on 2012-04-11
9
301 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 500 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
Technology Partners: 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!

 

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

[Webinar] How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
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.

717 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