How to produce a executable Oracle script from bash script?

Shell Programming and Scripting
1

Hi here's my 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

 [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

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

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!

2

Hi,
Use SQL*Plus in silent mode (option -s):

${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
1 Like
3

Hi cero, thanks for the answer