Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.
COURSE of the MONTH
13 days, 11 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Oracle SQL Developer (2.1.1.64) Script Automation
Posted on 2011-03-02
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
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;
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
4
2-
2
8 Comments
Active today
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.
You can create a sqlplus script and that can be easily sceduled.
Active today
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.
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
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
Thanks for the suggestion.
CF
Active today
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.
You might contact Oracle Support to see if this can be done without some key of keyboard scripting program.
Active today
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.
SQL*Plus is part of the Oracle client, and hence should be available anyway.
Active today
>>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.
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.
Active today
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.
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.
Featured Post
This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL).
Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to Export data from an Oracle database using the Original Export Utility. The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses
Course of the Month13 days, 11 hours left to enroll
800 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.