How to produce a executable Oracle script from bash script? | Unix Linux Forums | Shell Programming and Scripting

  Go Back    


Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here.

How to produce a executable Oracle script from bash script?

Shell Programming and Scripting


Closed Thread    
 
Thread Tools Search this Thread Display Modes
    #1  
Old 02-05-2013
jediwannabe jediwannabe is offline
Registered User
 
Join Date: Nov 2012
Last Activity: 30 September 2014, 12:07 AM EDT
Posts: 50
Thanks: 6
Thanked 0 Times in 0 Posts
How to produce a executable Oracle script from bash script?

Hi here's my code


Code:
${ORACLE_HOME}/bin/sqlplus /nolog <<!EOF
--step 5 create db script start
set feedback off
set heading off
set echo off
conn / as sysdba
spool ${ORACLE_SID}_db_link.sql

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

spool off

set feedback on
set heading on
set echo on

!EOF

here's the output where i run the script


Code:
 [oracle@TU-RH5 manual_upgrade]$ export ORACLE_SID=SUPERB
[oracle@TU-RH5 manual_upgrade]$ /home/oracle/chunhung/NI/manual_upgrade/create_db_link.sh

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 5 17:59:16 2013

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> SQL> SQL> SQL> SQL> Connected.
SQL> SQL> SQL>   2    3    4    5    6    7
CREATE DATABASE LINK
SUPERB_ADMIN.UEM_CBS_DBLINK
CONNECT TO ABC_ADMIN IDENTIFIED BY "" USING
'/s01/oracle/product/10.2.0/db_1'
;

SQL> SQL> SQL> SQL> SQL> SQL> SQL> SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

my output file SUPERB_db_link.sql

looks like the following


Code:
SQL>
SQL> SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
  2  ||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
  3  ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
  4  '''||L.HOST||''''
  5  ||chr(10)||';' TEXT
  6  FROM SYS.LINK$ L, SYS.USER$ U
  7  WHERE L.OWNER# = U.USER#;

CREATE DATABASE LINK
SUPERB_ADMIN.UEM_CBS_DBLINK
CONNECT TO ABC_ADMIN IDENTIFIED BY "" USING
'/s01/oracle/product/10.2.0/db_1'
;

SQL>
SQL> spool off

unfortunately this is not what I want.

what I want is


Code:
CREATE DATABASE LINK
SUPERB_ADMIN.UEM_CBS_DBLINK
CONNECT TO ABC_ADMIN IDENTIFIED BY "" USING
'/s01/oracle/product/10.2.0/db_1'
;

would really appreciate if someone could help me on this!

thanks a lot!
Sponsored Links
    #2  
Old 02-05-2013
cero cero is offline
Registered User
 
Join Date: Aug 2006
Last Activity: 24 October 2014, 10:27 AM EDT
Posts: 378
Thanks: 3
Thanked 69 Times in 63 Posts
Hi,
Use SQL*Plus in silent mode (option -s):

Code:
${ORACLE_HOME}/bin/sqlplus -s /nolog <<!EOF
--step 5 create db script start
set feedback off
set heading off
set echo off
conn / as sysdba
spool ${ORACLE_SID}_db_link.sql

SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;

spool off

set feedback on
set heading on
set echo on

!EOF

The Following User Says Thank You to cero For This Useful Post:
jediwannabe (02-05-2013)
Sponsored Links
    #3  
Old 02-05-2013
jediwannabe jediwannabe is offline
Registered User
 
Join Date: Nov 2012
Last Activity: 30 September 2014, 12:07 AM EDT
Posts: 50
Thanks: 6
Thanked 0 Times in 0 Posts
Hi cero, thanks for the answer
Sponsored Links
Closed Thread

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

More UNIX and Linux Forum Topics You Might Find Helpful
Thread Thread Starter Forum Replies Last Post
Running Executable in Bash Script Duo11 UNIX for Dummies Questions & Answers 4 03-28-2011 02:47 AM
Script to produce report of High Utilization Processes thinakarmani Shell Programming and Scripting 3 03-24-2011 02:31 PM
Caller Script should produce and email from 4 different script within it rxg Shell Programming and Scripting 3 01-16-2010 02:56 AM
Perl Script to produce a file mingming88 Shell Programming and Scripting 3 05-16-2009 03:16 PM
How to make a script (Bash, KornShell, etc.) executable by mouse clicking? dariyoosh Shell Programming and Scripting 2 03-09-2009 01:44 PM



All times are GMT -4. The time now is 09:08 PM.