Solved

Oracle SQL Developer (2.1.1.64) Script Automation

Posted on 2011-03-02
8
1,141 Views
Last Modified: 2012-05-11
Hi All,

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_Date
FROM aothd a,
  iprod b,
  iprdd c,
  irctd d,
  ipo e,
  ipod f
WHERE a.prod_id  = b.prod_id
AND b.prod_id    = c.prod_id
AND a.lic_plt_id = d.lic_plt_id
AND a.po_id      = e.po_id
AND a.po_id      = f.po_id
AND a.prod_id    = f.prod_id
AND 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 = 37
AND 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;

Open in new window

0
Comment
Question by:creativefusion
  • 4
  • 2
  • 2
8 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35022800
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
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 35022871
Here's a quick example of what I'm thinking.

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.
--set up some test tables and data
drop table t1 purge;
create table T1 (
	prod_id varchar2(10),
  	sdesc  varchar2(10),
  	po_id number
);

insert into t1 values('1234','desc A',1);
insert into t1 values('4321','desc B',2);
commit;

--sqlplus script starts here
--to run from a BAT/CMD file:  sqlplus -s /nolog @myscript.sql
set pages 0
set lines 32767
set trims on
set feedb off
set echo off
connect bud/bud
spool myFile.csv
SELECT EXTRACT(XMLAGG(XMLELEMENT("s",
           	prod_id || ',',
           	sdesc || ',',
           	po_id
         )), '/s/text()').getclobval()
           column1
FROM t1
group by prod_id
/
spool off

Open in new window

0
 

Author Comment

by:creativefusion
ID: 35023005
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35026587
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.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 35034464
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35036216
>>SQL*Plus is part of the Oracle client,

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.
0
 
LVL 69

Expert Comment

by:Qlemo
ID: 35036427
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
 

Author Comment

by:creativefusion
ID: 35045130
Okay guys. Great help thanks. I''l look into this and post back the outcome. Cheers, CF
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to SQL Trace a SPECIFIC query 24 70
How to load 2 images in same column in Delphi 2 45
Oracle - SQL Parse String 5 33
ORA-02288: invalid OPEN mode 2 20
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
APEX (Application Express) is used to develop a web application from Oracle. SQL Workshop is one of the tools that comes with Oracle APEX to query or modify the database objects or to make any changes to the structure.
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 shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

808 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