Register
It is currently Thu Sep 18, 2014 3:40 am

How to connect to a remote ORACLE database?


All times are UTC - 6 hours


Post new topic Reply to topic  [ 5 posts ] 
Author Message
 PostPosted: Wed May 04, 2011 2:46 am   

Joined: Wed May 04, 2011 2:36 am
Posts: 3
Hi everyone :)

Is it possible to be connected to a remote ORACLE database?

I need to export my SQL data result to a CSV format in text file.

To do this, I made this script:
Code:
#!/bin/bash

julianDay=$(date +'%j')
formatDate=$(date +'%y-%m-%d')
formatDateSQL=$(date +'%d/%m/%y')

#SQL statement
SQLquery="SELECT "$julianDay", cal.date_calendrier, mc.coquille AS coque, 'EXPLOITATION' AS numeroSerie, 'EXPLO' AS type, c.nom_ligne_voyageur, DECODE(c.flag_real,-1,'NR',1,'P','R') AS f_real FROM t_course c, t_course_morceau_course cmc, t_calendrier cal, t_horaire_point hp, t_morceau_course mc, t_mission mi WHERE (cmc.id_course = c.id_course) AND c.id_mission = mi.id_mission AND mi.nom_ligne_voyageur = c.nom_ligne_voyageur AND mc.id_point_debut = hp.id_point AND cmc.id_morceau_course = mc.id_morceau_course AND cmc.classe_tm = mc.classe_tm AND c.jour_exploitation = cal.id_calendrier AND cal.id_calendrier = hp.jour_exploitation AND c.jour_exploitation = hp.jour_exploitation AND c.classe_tm = hp.classe_tm AND hp.id_course = c.id_course AND hp.id_course = cmc.id_course AND hp.classe_tm = cmc.classe_tm AND mc.classe_tm = hp.classe_tm AND c.classe_tm = mc.classe_tm AND c.classe_tm = cmc.classe_tm AND mi.type_mission = 1 AND mi.categorie_mission = 1 AND c.classe_tm = 3 AND c.flag_real <> -1 AND mc.coquille IS NOT NULL AND ((c.jour_exploitation = fnc_convert_date_to_id('"$formatDateSQL"') AND (hp.depart_heure > 10800)) OR (c.jour_exploitation = fnc_convert_date_to_id('"$formatDateSQL"') AND hp.depart_heure<10800)) GROUP BY cal.no_jour, cal.date_calendrier, mc.coquille, c.nom_ligne_voyageur, c.flag_real;"

#Export result
echo $SQLquery > SQLQuery.sql

#DataBase name
ORACLE_SID="bsy"
export ORACLE_SID

sqlplus 'login/passwd@(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=192.*.*.*)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=ora8i))) @SQLQuery.sql'  << FIN set echo off set colsep ; set pagesize 0 set trimspool on

SPOOL export.txt

SPOOL off

FIN

exit


Thanks for your help.

Have a nice day.


Regards,
Bdgta.


Top
 Profile  
 PostPosted: Wed May 04, 2011 9:54 am   
User avatar

Joined: Tue Apr 27, 2010 2:28 pm
Posts: 172
Location: Czech Republic
Yes, it is possible. Does your script work? What exactly does it?
What do you mean by the "set echo off set colsep..." after the FIN string?


Top
 Profile  
 PostPosted: Sat May 07, 2011 4:13 pm   

Joined: Wed May 04, 2011 2:36 am
Posts: 3
hi,

Can you just explain me how to be connected to a remote ORACLE database with a bash script ?

Thanks for your help. :)

set colsep ; means that each col are separated by ;

Example:

Before set colsep

id | name | surname
1 | test | test
2 | toto | tata


After set colsep

id ; name ; surname
1 ; test ; test
2 ; toto ; tata


Top
 Profile  
 PostPosted: Mon May 09, 2011 6:00 am   
User avatar

Joined: Tue Apr 27, 2010 2:28 pm
Posts: 172
Location: Czech Republic
bdgta wrote:
hi,

Can you just explain me how to be connected to a remote ORACLE database with a bash script ?

Have you googled? Have you read for example this: http://dbaforums.org/oracle/index.php?showtopic=886?
bdgta wrote:
set colsep ; means that each col are separated by ;

Example:

Before set colsep

id | name | surname
1 | test | test
2 | toto | tata


After set colsep

id ; name ; surname
1 ; test ; test
2 ; toto ; tata

But whatever appears after the FIN word on the same line will be interpreted by bash, not Oracle :x|


Top
 Profile  
 PostPosted: Mon May 09, 2011 6:17 am   

Joined: Wed May 04, 2011 2:36 am
Posts: 3
yes I already try this but it doesn't work!

Example: sqlplus ip_server@login/password @mySQLQuery.sql

But I can't be connected.


Thanks for your help


Top
 Profile  
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 5 posts ] 

All times are UTC - 6 hours


Who is online

Users browsing this forum: Bing [Bot] and 3 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  


BashScripts | Promote Your Page Too
Powered by phpBB © 2011 phpBB Group
© 2003 - 2011 USA LINUX USERS GROUP