schedule to run a script

SELECT RUN_DATE,      
TO_CHAR(RUN_DATE, 'DD-MON-YYYY HH24:MI'),
TABLE_NAME, FIELD_NAME, FACILITY_ID,
RECORD_COUNT, TABLE_NUMBER
FROM record

I would like to run the above script everynight ( automated) and save the result  to \\test\c$\.txt  with timestamp(i.e \\test\c$\072507.txt). How can I do this? i am using oracle 8 on window NT

jung1975Asked:
Who is Participating?
 
sathyagiriConnect With a Mentor Commented:
May be your query is too long..
Split your query into two or more lines.

For spooling the output, use

spool c:\filename -- This line should be in your .sql file
//select query here
spool off
0
 
Richard OlutolaConsultantCommented:
Oracle 8??? Windows NT???
You may simply use Windows Scheduled Tasks under Control Panel.
This will run a batch file which will call your sql script.

The batch file may contain following:

sqlplus username/pwd@sid @ sqlplusscript.sql >> \\test\c$\out.txt

I'll find the timestamp thing for you shortly.
0
 
Richard OlutolaConnect With a Mentor ConsultantCommented:
Here you go. I haven;t tried this but I've used the timestamping script to create directories automatically with backups.

Your batch file may look like this:

@echo off
call :GetDate year month day
:: echo/Today is: %year%-%month%-%day%
sqlplus username/pwd@sid @ sqlplusscript.sql > \\test\c$\%year%-%month%-%day%.txt
:: md %year%-%month%-%day%
goto :EOF

:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
:GetDate yy mm dd
::
:: By:   Ritchie Lawrence, 2002-06-15. Version 1.0
::
:: Func: Loads local system date components into args 1 to 3. For NT4/2K/XP
::
:: Args: %1 var to receive year, 4 digits (by ref)
::       %2 var to receive month, 2 digits, 01 to 12 (by ref)
::       %3 Var to receive day of month, 2 digits, 01 to 31 (by ref)
:::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
setlocal ENABLEEXTENSIONS
set t=2&if "%date%z" LSS "A" set t=1
for /f "skip=1 tokens=2-4 delims=(-)" %%a in ('echo/^|date') do (
  for /f "tokens=%t%-4 delims=.-/ " %%d in ('date/t') do (
    set %%a=%%d&set %%b=%%e&set %%c=%%f))
endlocal&set %1=%yy%&set %2=%mm%&set %3=%dd%&goto :EOF
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

 
jung1975Author Commented:
I create a batch file like below..
sqlplus sys/password@oracle10 as sysdba @C:\log.sql>>C:\out.txt

but only output that I am getting is (I've tried in oracle 10g):
SQL*Plus: Release 10.1.0.2.0 - Production on Wed Jul 25 20:56:47 2007

Copyright (c) 1982, 2004, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

  2  


No output.....

What am i doing wrong?
0
 
jung1975Author Commented:
I've tried like below.. but getting the same result..

sqlplus sys/password@oracle10 as sysdba;
 @C:\log.sql >> C:\out.txt;
0
 
jung1975Author Commented:
do i have to use the spool  command to return the outpout in log.sql script? if so, how?
0
 
sathyagiriCommented:
Try this


sqlplus sys/password@oracle10 as sysdba "@c:\log.sql"

In the .sql file make sure you end the statement with a semi colon.

0
 
jung1975Author Commented:
thanks.. it works but is there anyway i can just save the output of the query into .txt file  using SPOOL ?
0
 
jung1975Author Commented:
when i run the above script ...i saw "40 characters got truncated" on the dos prompt window... what is this meesage mean?
0
 
Richard OlutolaConsultantCommented:
I thought you wanted to include the timestamp in your output file? Is this no longer required? Have you tried the batch file I provided above?
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.

All Courses

From novice to tech pro — start learning today.