Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

automated query in oracle

Posted on 2010-09-19
12
Medium Priority
?
616 Views
Last Modified: 2012-05-10
I've just installed the oracle sql developer and have connected to our remote database. I have some queries I need to use to collect some data and copy to some excel spread sheets. I'm looking to automate this process and am new to oracle, but, have done this with other DB like access and sql server (using cmd line commands).
So, looing for ideas on how to proceed. Read a little about sql*plus, but, haven't done anything yet.

thanks
alan
0
Comment
Question by:avoorheis
[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
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 11

Accepted Solution

by:
jgiordano earned 400 total points
ID: 33712672
you could use windows batch processing to run a sql plus query and schedule it.

http://www.oracle-base.com/articles/misc/OracleShellScripting.php
0
 

Author Comment

by:avoorheis
ID: 33712874
thanks, that sounds good, now I just have to find where to d/l sql plus. Didn't see it at your link or find in the several oracle pages i searched. I've installed the sql deverloper, but, I don't know if I need something else installed. Can it be started from a dos command prompt?
0
 
LVL 11

Expert Comment

by:jgiordano
ID: 33712885
is sql developer from oracle? if so then it might contain sqlplus

do a search for the sqlplus binary or download the oracle db from oracle for windows and only install the client tools when prompted instead of the full database.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Assisted Solution

by:ravindran_eee
ravindran_eee earned 400 total points
ID: 33713307
You can go this in multiple ways.. Following is what I generally used to do!!

If the excel is very sensitive to formatting and all, you can use Excel VBA macros. You can write some VB code to get data from Oracle database and populate it in your excel spreadsheet whatever way you want. This is very flexible and you don't have to do even the formatting manually.
http://www.adp-gmbh.ch/blog/2007/01/24.php

Another thing you can do is develop an Oracle stored procedure and schedule it using Oracle jobs. The stored procedure would just spool out the contents to a csv file or text file. You can then manually copy paste them in your excel spreadsheets
http://www.devshed.com/c/a/Oracle/Writing-to-Text-Files-in-Oracle-PLSQL/
http://www.dba-oracle.com/tips_oracle_dbms_job.htm

Another option is what has been discussed already. You can write a simple SQL script (a file named .sql) which would use spool command of Oracle to generate the csv or text file. It will have to be called from a shell script (in case of UNIX) or DOS batch file (in case of Windows). Then make use of native OS scheduling feature to execute it periodically
http://www.computing.net/answers/dbase/oracle-spool-output-to-file-/37.html

Let me know if there are any questions! Based on your requirement, you can choose one of the options. There could be other as well!!
0
 
LVL 7

Expert Comment

by:sumit2906
ID: 33713828
I use a script like this for similar purpos. create a batch script, which can be sceduled or executed manually, which calls a sql script to spool data, I use a saperator, so that easily convert the file in excel.
***my_script.bat
sqlplus user/pwd@orcl11 @my_script.sql

***my_script.sql
set colsep |
set feedback off;
SET HEADS OFF;

spool file_name.csv
Prompt some_text;
select * from my_tables;

Prompt some_other_text;
select * from tab2;
spool off;

Open in new window

0
 

Assisted Solution

by:mnshah9
mnshah9 earned 400 total points
ID: 33714055
Hi

Best option to create file automatically and that should also open in excel you need to create CSV file.

CSV file will easily open in Excel and we can generate CSV file easily

like if u have emp table and it contains id and name then use below query and fire same from SQL promot

connect to Database using SQL Plus
spool emp.csv
select id || ',' || name from emp;

Above statement will generate CSV file and same can be open into excel



0
 

Author Comment

by:avoorheis
ID: 33717207
thanks everyone, will take a bit to digest this.

But, in the mean time, I did find sqlplus and I think the issue I'm having with that is the same I'm having with the ODBC. When I'm trying to config the ODBC, it's asking for a TNS Service Name during the ODBC driver configuration, which I don't have (I used an LDAP Server when setting up the connection in the Oracle SQL Developer). And, when starting sqlplus, it errors with "ORA-12560: TNS:protocol adapter error" after I enter user-name and password.

I did find a tnsnames.ora file...maybe I need to enter info in there, right?
Will that help with the ODBC connection, or, will I need the correct tns name to enter into the ODBC configuration?
0
 
LVL 7

Assisted Solution

by:sumit2906
sumit2906 earned 800 total points
ID: 33717489
you need tnsnames.ora file, if your database is running on a different machine. You will need database host name/IP address, service name/SID and listner port (default 1521) to configure tnsnames file.
You can assign any alias which you will have to use while connecting:
sqlplus user/pwd@tnsalias
0
 

Author Comment

by:avoorheis
ID: 33717650
ok, working on figuring out how to fill in tnsnames.ora. Doesn't seem like devleoper connection info maps to the tns:
developer connection info is:
Connection Name: Tst Srvr1 (that's just an alias, right?)
user: xxx
password: xxx
role: default, connection type: LDAP, OS Authentication Kerberow Auth. and Proxy Conn. not checked
LDAP Server: oid.xxx.com:123:456   (not sure what this is)
DB Service: POST.xxx.com  (not sure what this is)

So, need to fill in tnsnames.ora
(ADDRESS = (PROTOCOL = TCP)     ---leave that as is?
(HOST = <hostname or IP>)   ---will oid.xxx.com:123:456 work?
(PORT = <port>)   ---no clue here
not sure on the following, except service might be POST.xxx.com
(CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = <database service name>)

then, does it matter wher this file is saved?
0
 
LVL 7

Assisted Solution

by:sumit2906
sumit2906 earned 800 total points
ID: 33717731
In your case, it will be like this:
tstsrvr1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = <hostname or IP>)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = POST.xxx.com)
    )
  )

you should be able to ping the hostname or IP, the default port is 1521, it could be different.
Also, the file location is fixed, it is under oracle_home\network\admin directory. You can also use command tnsping test to see where is it expecting the file.

The best way is to take the tns entry from your server machine, and use it.

Also, this is not a related question, so better to open a new question, that way you will get more responses.
0
 

Author Comment

by:avoorheis
ID: 33717844
0
 

Author Comment

by:avoorheis
ID: 33734627
turned out to be several things, one was that I only had the instant client installed, there was old info in the tnsnames file plus some typos on my part...thanks again for all the feedback, it was helpful.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to take different types of Oracle backups using RMAN.

610 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