How to produce a executable Oracle script from bash script?


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to produce a executable Oracle script from bash script?
# 1  
Old 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

!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!
# 2  
Old 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

This User Gave Thanks to cero For This Post:
# 3  
Old 02-05-2013
Hi cero, thanks for the answer
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Bash script won't run because hardware won't produce display

Can anyone offer any advice on how to modify the script below to work on a new system we have, that has no graphics capability? We admin the system through a serial RAS device. I've tried running the below script through the RAS and through an ssh -X session. It failed with something like "GTK... (3 Replies)
Discussion started by: yelirt5
3 Replies

2. Shell Programming and Scripting

How to block first bash script until second bash script script launches web server/site?

I'm new to utilities like socat and netcat and I'm not clear if they will do what I need. I have a "compileDeployStartWebServer.sh" script and a "StartBrowser.sh" script that are started by emacs/elisp at the same time in two different processes. I'm using Cygwin bash on Windows 10. My... (3 Replies)
Discussion started by: siegfried
3 Replies

3. Shell Programming and Scripting

Bash script make itself executable

Is there a way to make this make itself executable? Thanks. :-) cat > somescript.sh << \EOF #!/bin/bash block_count=$(sudo tune2fs -l /dev/sda1 | awk '/^Block count:/ {print $NF}') reserved_block_count=$(sudo tune2fs -l /dev/sda1 | awk '/^Reserved block count:/ {print $NF}') perl -e... (4 Replies)
Discussion started by: drew77
4 Replies

4. Shell Programming and Scripting

Making bash script allways executable when transfer ?

Does it possible to make some bash script automatic to be a executable when transfered to another pc...? (5 Replies)
Discussion started by: tomislav91
5 Replies

5. Shell Programming and Scripting

How to create an executable bash script for these commands?

I wish to create an executable bash script that will run the following commands as root, that is, using sudo su iptables-save | awk '/^ / { print $1 } /^:+ / { print $1 " ACCEPT" ; } /COMMIT/ { print $0; }' | iptables-restoreMy first attempt at bash... (9 Replies)
Discussion started by: thixeqi
9 Replies

6. UNIX for Dummies Questions & Answers

Running Executable in Bash Script

Hey guys, so I've been trying to write a bash script called runSorter.sh that runs an executable that also takes in some parameters and outputs the results to a text file. The executable, sorter, takes in a number parameter. I want to make it so that you can input as many number parameters into... (4 Replies)
Discussion started by: Duo11
4 Replies

7. Shell Programming and Scripting

Script to produce report of High Utilization Processes

Hi, I have requirement to produce a report on high CPU utilization processes and the processes lying on the CPU for long time (Long running queries). The report should append into the files every 3 minutes. I use prstat to pull top 5 and found the following result. ... (3 Replies)
Discussion started by: thinakarmani
3 Replies

8. Shell Programming and Scripting

Caller Script should produce and email from 4 different script within it

Hi I wrote a shell script , which includes one output file which is emailed back to me , when the script runs . Now i want to slip the script into 4 different shell scripts each of which taking the parameter PROD or DEV, and include all the 4 different shell scripts in a caller script. ... (3 Replies)
Discussion started by: rxg
3 Replies

9. Shell Programming and Scripting

Perl Script to produce a file

hi i got a file called essay which contain few pages with many paragraphs. now i wanna with PERL to produce another file which called Essaylist that contain a sorted list of words that appear in the file essay. the format for Essaylist: $word found $times times on page a b c.... where $word... (3 Replies)
Discussion started by: mingming88
3 Replies

10. Shell Programming and Scripting

How to make a script (Bash, KornShell, etc.) executable by mouse clicking?

Hello everybody, Is there any way to make a script (Bash, KornShell, etc.) executable by mouse clicking? For example you have a file myscript.sh, you run: $ chmod u+x myscript.sh Therefore it becomes executable and all you need is to run from the terminal: $./myscript.sh... (2 Replies)
Discussion started by: dariyoosh
2 Replies
Login or Register to Ask a Question