avoorheis
asked on
automated query in oracle
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
So, looing for ideas on how to proceed. Read a little about sql*plus, but, haven't done anything yet.
thanks
alan
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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;
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
posted last question to new thread
https://www.experts-exchange.com/questions/26485779/tnsnames-org-set-up.html?anchorAnswerId=33717839#a33717839
https://www.experts-exchange.com/questions/26485779/tnsnames-org-set-up.html?anchorAnswerId=33717839#a33717839
ASKER
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.
ASKER