Schedule query

Hello,

My Excel file has a table linked to a temp.txt file.  The data in the .txt file comes from a manually pulled report in TOAD.

I would like to have the .txt file updated automatically every morning.  I would also like the temp.txt files to save a copy of itself to an archive folder with the format ReportA_yyyy_mm_dd.txt.


I've run SQL through Excel VBA before, but it seems less stable than TOAD.  Are there any issues doing it through Excel VBA?  Can I do it through TOAD, or something else entirely?

(There are actually 4 queries that need to be ran, but I assume if you can teach me to do one, I can do the others.)

Thanks!




TelMacoAsked:
Who is Participating?
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.

SiddharthRoutCommented:
I have never worked with TOAD.

have you checked their tutorials?

http://www.toadsoft.com/features.html

Sid
0
slightwv (䄆 Netminder) Commented:
If I understand the question:  you want to schedule creation of a CSV file from an Oracle database?

That is straight forward using xmlagg and sqlplus.  As for scheduling and creating archive copies, a simple BAT script and task scheduler should work.

Please clarify my assumption.  If correct, please provide table definitions, sample data and expected results.
0
slightwv (䄆 Netminder) Commented:
>>I have never worked with TOAD.

Then why post and only a link to Toad's home page?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

SiddharthRoutCommented:
That is because it has some very good tutorials (Did you see them) which might help the OP ;)


Sid
0
TelMacoAuthor Commented:
Yeah  a CSV file would be perfect.  I don't know anything about xmlagg or BAT script.  I've used SQLplus, but only once before.

I don't know what you need for table definitions or sample data.  Can we keep it simple and just assume a SELECT * FROM someschema.sometable; ?  

I'm currently using a TNS connection through TOAD.  It requires a username and pw, which would need to be part of the automation.  I'll attach a modified sample of the TNS info.

The expected result would be the csv output file saved to c:\test as temp_rpt_a.txt, and to c:\archive as rpt_a_yyyy_mm_dd.xls, date being the day of the report


Thanks

ServerA = 
  (DESCRIPTION = 
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = somehost)
      (PORT = xxxxx)
    )
    (ADDRESS = 
      (PROTOCOL = TCP)
      (HOST = somehost)
      (PORT = xxxxx)
    )
    (LOAD_BALANCE = yes)
    (CONNECT_DATA = 
      (SERVER = DEDICATED)
      (SERVICE_NAME = ServerA)
      (FAILOVER_MODE = 
        (TYPE = SELECT)
        (METHOD = BASIC)
        (RETRIES = 180)
        (DELAY = 5)
      )
    )
  )

Open in new window

0
slightwv (䄆 Netminder) Commented:
You might not need t he XML piece depending on the data.  As long as the rows aren't greater than 4000 characters you can go with a simple concat.

Given the table and samle data below, create a '.sql' script in the other code window.

Then from a command line, it's a simple:
sqlplus -s username/password@ServerA @myscript

The 'archiving' of previous runs can easily be added simply by grabbing the current date then a simple copy.  My BAT is old and I would need to Google it but it you cannot find it, I'll see what I can do.
--create a sqlplus script called: myscript.sql
set pagesize 0
set trimspool on
set lines 32767
set feedback off


spool myFile.txt
-- or spool myFile.csv
SELECT col1 || ',' || col2
           myCSV
FROM tab1
/

spool off

Open in new window

drop table tab1 purge;
create table tab1 (col1 char(1), col2 char(1));

insert into tab1 values('a','b');
insert into tab1 values('c','d');
commit;

Open in new window

0
TelMacoAuthor Commented:
Hi,

I didn't understand what the second bit of code you attached was for?  the drop table/create table bit?  Doesn't matter lol.  I think I got it.

I'd never even created a .sql or .bat file before, so I had to look up how to do that, but it works just fine now.

Steps I used:
1. Create a .sql file containing the query and other details (used notepad and change the extension)

2. Create a .bat file containing the commands (used notepad and changed the extension)

3. set the .bat to run as a scheduled task

*****************
additional details, in case anyone else needs more clarity
the .sql file looks like this:

set heading off;
set linesize 10000;
set pagesize 10000;
set feedback off;

spool temp.csv;

SELECT  a || ', ' ||
               b || ', ' ||
               c || ', ' ||
FROM sometable;

spool off;

exit;

the .bat file looks like this:

chdir "C:\Program Files\someplace"

sqlplus username/password@server@query1.sql


*******************

The last bit would be how do I copy and save the temp file to the archive folder?
The temp file is here: C:\Program Files\someplace\temp.csv
I'd like a copy here: C:\Program Files\archive\query1_yyyy_mm_dd.csv




0
slightwv (䄆 Netminder) Commented:
>>the drop table/create table bit?

Just for a complete 'tested' solution.

>>the .bat file looks like this:

You might need to add the Oracle environment variables like ORACLE_HOME and entries to PATH when you go to schedule this to run.

>>The last bit would be how do I copy and save the temp file to the archive folder?

Let me see if I can find some samples.
0
TelMacoAuthor Commented:
cool, I have not actually tested out a scheduled run yet, but manually the .bat file works fine.  Will give it a shot later tonight and let you know.
0
slightwv (䄆 Netminder) Commented:
First Google link (I tested it with XP and it seems to work fine).

If you just want the YYYYMMDD, just remove the HHMM pieces.

http://forums.techguy.org/dos-other/323716-append-date-filename-dos.html


Finally got it sussed used the following for my rename.bat

@echo off
for /F "tokens=2-4 delims=/ " %%i in ('date /t') do set yyyymmdd=%%k%%i%%j
echo Date: %yyyymmdd%
for /F "tokens=1-2 delims=: " %%l in ('time /t') do set hhmm=%%l%%m
echo Time: %hhmm%
rem -- or use both
echo %yyyymmdd%%hhmm%
rem -- here is the copy command with just the date appended

REN E:\Sx3\FTP\pl.txt pl%yyyymmdd%%hhmm%.txt
REN E:\Sx3\FTP\llpg.txt llpg%yyyymmdd%%hhmm%.txt




>>sqlplus username/password@server@query1.sql

Just for readability add a space:
sqlplus username/password@server @query1.sql

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
TelMacoAuthor Commented:
No clue what any of that means or how it works, I read that linked post until my eyes bled.  Terribly confusing to me... I think I tried about 50 different times before finding something that looked right.

Finally used this:

chdir "C:\temp"
@echo off
for /F "tokens=1-4 delims=/ " %%i in ('date /t') do set mmddyyyy=%%i%%j%%k
echo Date: %mmddyyyy%
echo %mmddyyyy%%

REN file.txt file_%mmddyyyy%%.txt

And the file came out like this:

File_YYYYMMDD

Good enough  (=

Thanks getting me on track!

Bit of a followup though.  I've hit a bug with the .CSV file, it does not include the headers and I need defined headers included.  Do you know what needs changing?

I tried setting heading to on, but that didn't return the correct results.  I need to set the header names.  I also tried putting HEADER = KEY || ',' || ORDERNO || ',' || TYPE || right before the SELECT statement, but that failed as well.

Thoughts?

Thanks!
set heading off;
set linesize 10000;
set pagesize 10000;
set feedback off;

spool temp_cso_pool.csv;
SELECT  a || ', ' ||
        b || ', ' ||
        c
FROM sometable;

spool off;

exit;

Open in new window

0
TelMacoAuthor Commented:
Nevermind, I just created a txt file with the Headers already in it.

then included this in the BAT file:
copy Header.txt + test.csv new.csv
0
slightwv (䄆 Netminder) Commented:
Headers don't work that way unfortunately.

Just hard code them.  Just above the select add:

Prompt KEY,ORDERNO,TYPE
0
slightwv (䄆 Netminder) Commented:
>>Nevermind

Lol.  Too late.  I was already typing...
0
TelMacoAuthor Commented:
I graded as a "B" b/c the solution was a link and did not pinpoint the specifics on what I needed.  Never having done this before, it was like reading a foreign language.  While the linked post offered many different approaches none worked for me. It was difficult for me to grasp what needed to be modified to make it work for my situation.  I still appreciate the help though, I just like to understand the solution, and in this case, I have little clue as to how some parts of it actually work.  In any case, thank you!
0
slightwv (䄆 Netminder) Commented:
I would have rather had you ask for clarification on anything you didn't understand.  I would have gladly assisted.

I had to assume that after 11 days you had everything you needed.
0
TelMacoAuthor Commented:
Sweet, thanks for the tip on the headers. When I tried it though, I get a gap between the headers and the other data ( I open the csv in Excel) Row 1 has the headers, row 3 and down has the query results.


0
slightwv (䄆 Netminder) Commented:
My bad.

Change your script to:
set pagesize 0

Also add for grins:
set trimspool on
0
TelMacoAuthor Commented:
That does the trick (=
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.

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.