|
|||||||
| Forums | Search Forums | Register | Forum Rules | Man Pages | Albums | FAQ | Members | Calendar | Search | Today's Posts | Mark Forums Read |
| Shell Programming and Scripting Post questions about KSH, CSH, SH, BASH, PERL, PHP, SED, AWK and OTHER shell scripts and shell scripting languages here. |
|
|
|
Thread Tools | Search this Thread | Display Modes |
|
#1
02-05-2013
|
|||
|
|||
|
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
!EOFhere'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
02-05-2013
|
|||
|
|||
|
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
02-05-2013
|
|||
|
|||
|
Hi cero, thanks for the answer
|
| Sponsored Links | ||
|
|
| Thread Tools | Search this Thread |
| 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 |
|
|
Linear Mode
