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

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

0
jung1975
Asked:
jung1975
  • 5
  • 3
  • 2
2 Solutions
 
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 OlutolaConsultantCommented:
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
sathyagiriCommented:
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:
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

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

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