I have a generic SQL (with three parameters), that I run at specific periods each day. When I have finished running each SQL, I export the results to CSV files which are automatically linked to an Access DB located on a server. The access DB holds all the data and serves as a tool for reporting and data analysis purposes.
My preferred outcome would be:
1. MS Command Script opens SQL developer
2. A macro (not sure what this would look like or if even possible) creates a connection instance, runs the SQL statement and exports each output file to my server
3. SQL developer is closed
Is anyone able to help me out here?
CF
SELECT a.dc_id || a.phys_whse_id AS LocNo, a.prod_id AS Product, b.sdesc AS Description, a.po_id AS Purchase_Order, f.qty_ord/ c.vend_cse_rtl_unit AS Orig_Ord_Qty_Cse, TO_CHAR(a.create_dtim, 'DD/MM/YYYY') AS Receipt_Date, a.cse_qty AS Cases_Rec, c.stor_ti AS Ti, c.stor_hi AS Hi, TO_CHAR(a.cde_dt, 'DD/MM/YYYY') AS Code_Date, a.lot_no AS Lot_No, e.vend_id AS Vendor_ID, e.buyer AS Buyer, b.act_dept AS FineDept, b.cde_dt_var AS Min_Date, b.prod_max_dt AS Max_DateFROM aothd a, iprod b, iprdd c, irctd d, ipo e, ipod fWHERE a.prod_id = b.prod_idAND b.prod_id = c.prod_idAND a.lic_plt_id = d.lic_plt_idAND a.po_id = e.po_idAND a.po_id = f.po_idAND a.prod_id = f.prod_idAND f.pod_id = (SELECT MIN(x.pod_id) FROM ipod x WHERE f.dc_id = x.dc_id AND f.po_id = x.po_id AND f.prod_id = x.prod_id )AND a.phys_whse_id = 37AND a.lhty_id IN ('PU','MP')AND TRUNC(a.create_dtim) BETWEEN to_date('31/01/2011', 'DD/MM/YYYY') AND to_date('06/02/2011', 'DD/MM/YYYY')ORDER BY a.prod_id, a.create_dtim, a.po_id;
I know you probably like sql developer but have you considered maybe sqlplus?
You can create a sqlplus script and that can be easily sceduled.
0
creativefusionAuthor Commented:
Sorry I am limited to choices here. The business provides SQL Developer only and would not consider SQLPlus as an option.
Thanks for the suggestion.
CF
0
Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.
I've never used sql developer so I cannot confirm if what you want to do is possible. I've looked around and cannot find anything even hinting that it is possible.
You might contact Oracle Support to see if this can be done without some key of keyboard scripting program.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
SQL Developer is not intended for that purpose, and there is no way to implement it besides heavy hacking like using AutoIt or the like, as slightwv correctly stated.
SQL*Plus is part of the Oracle client, and hence should be available anyway.
I believe the issue is: SQL Developer doesn't require a client install. It's about the only Oracle product that doesn't.
I strongly suggest you recommend to your IT staff they allow you at least the Oracle Instant Client w/ SQL Plus. It doesn't take up much room nor does it create a log of registry/garbage in the system.
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
Haven't been aware of that you do not need a client for Developer (using it seldom myself).
For using SQL*Plus, and not having any kind of client installed, you can trick into using the server installation files to execute commands (by having a Oracle share defined, and extending PATH to include the binaries - requires that the server is the same platform as the client). But having an Instant Client is a much better option.
0
creativefusionAuthor Commented:
Okay guys. Great help thanks. I''l look into this and post back the outcome. Cheers, CF
0
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
The thing to note here is you are limited to a 32K size single line. If a single line will exceed that it will require more work.
Open in new window