Home » Other » General » how to run any sql script from the shell prompt without going to SQLPLUS.
how to run any sql script from the shell prompt without going to SQLPLUS. [message #156923] Mon, 30 January 2006 00:23 Go to next message
surbhigarg
Messages: 11
Registered: January 2006
Junior Member
hi
could any one please tell me
"how to run any sql script from the shell prompt without going to SQLPLUS."

thanks
Re: how to run any sql script from the shell prompt without going to SQLPLUS. [message #156943 is a reply to message #156923] Mon, 30 January 2006 04:29 Go to previous messageGo to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi,

For one such example, see the script get_stats.sh:
#!/usr/bin/ksh

ORACLE_BASE=/oracle export ORACLE_BASE
ORACLE_HOME=/oracle/product/8.1.7 export ORACLE_HOME
ORACLE_SID=DEVAPP1 export ORACLE_SID

/oracle/product/8.1.7/bin/sqlplus -s / << EOF
set head off
set pause off
set pages 0
set feedback off
set term off
set echo off
set verify off
spool /u01/app/analyze_schema.lst
select 'exec dbms_utility.analyze_schema('||''''||username||''''||','||'''COMPUTE'''||');'
FROM DBA_USERS
where username not in ('SYSTEM','SYS');
spool off
start /u01/app/analyze_schema.lst
exit
EOF

all u need to do is to run the script from shell prompt..basically sqlplus -s runs the sqlplus in silent mode..

Upd-Mod: Add code tags.

[Updated on: Mon, 30 January 2006 16:42] by Moderator

Report message to a moderator

Re: how to run any sql script from the shell prompt without going to SQLPLUS. [message #156968 is a reply to message #156943] Mon, 30 January 2006 07:46 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> "how to run any sql script from the shell prompt without going to SQLPLUS."
If i understand this correctly, the OP is trying to identify an environment/tool ( like sql*plus but NOT sql*plus) to query the database using a wrapper shell script.
Sql*plus is by itself almost like a shell ( Not exactly. Just a liberal example) to communicate with the database engine. If do not want to use sql*plus , you need some other tool to talk with database.
I am not aware of any such tool ( some like gqlplus have almost the same interface) with works in unix and that can be used in scripting ( unless you use perl DBI or jdbc or something like that).
Or May be i completely misunderstood your question.
Re: how to run any sql script from the shell prompt without going to SQLPLUS. [message #157033 is a reply to message #156968] Mon, 30 January 2006 16:43 Go to previous messageGo to next message
djmartin
Messages: 10181
Registered: March 2005
Location: Surges Bay TAS Australia
Senior Member
Account Moderator
Is this example more to your desire?
#!/bin/sh
# NAME            :  scr_test
# SYNTAX          :  scr_test <param 1> <param2> ...
# *******************************************************************

# comment 1
#
echo   "drop user myuser cascade;
  create user myuser identified by pswd default tablespace mytblspc;
  grant connect to myuser;
  grant dba to myuser;
  exit; "| sqlplus -s produser/pswd

# create tables in myuser user (if required)
#
sqlplus -s myuser/pswd @$CODEHOME/sql/create_my_tables

# create synonym in produser
echo   "drop synonym produser.user_tab_columns_me;
  create synonym produser.user_tab_columns_me for sys.user_tab_columns;
  exit; "| sqlplus -s produser/pswd

# create synonym in myuser
echo   "drop synonym myuser.user_tab_columns_me;
  create synonym myuser.user_tab_columns_me for sys.user_tab_columns;
  exit; "| sqlplus -s myuser/pswd

# get the latest batch number
#
echo  "set pagesize 0 feedback off echo off trims on termout off
  spool /tmp/bnr
  select 'export BATCHNR='
  ||to_char(nvl(max(transmission_batch_nr)+1,1)) from trnsmsn_dtl;
  exit;" | sqlplus -s produser/pswd

chmod 777 /tmp/bnr.lst 
. /tmp/bnr.lst
rm -f /tmp/bnr.lst

# export the data 
#
exp myuser/pswd owner=myuser \
  file=$CODEHOME/transdata/$BATCHNR.DAT \
  log=$CODEHOME/$BATCHNR.log

if [ $? -ne 0 ]
then
    echo "EXPORTING DATA - ERROR - CONTACT SUPPORT"
    exit 1
else
    # clear out the myuser user
    echo "EXPORTING DATA - Successful"
    echo "drop user myuser cascade;
      exit;"|sqlplus -s produser/pswd
    exit 0
fi

David
icon14.gif  Re: how to run any sql script from the shell prompt without going to SQLPLUS. [message #163791 is a reply to message #156923] Mon, 20 March 2006 00:38 Go to previous message
sagar007
Messages: 6
Registered: March 2006
Junior Member
hi.............
I am writing a small shell script...just for test...okay
all these has to be done in UNIX/LINUX

(1.)first create .sql file...let it is x_test.sql and has the contents ...
create table x_test(x1 varchar2(20),x2 number(4),x3 date)
/
exit;

(2.) write a shell script...like x_test.sh in vi editor
Here login is scott/itger@sid, or apps/apps@sid or whatever you know valid schema

echo enter the login
read login

sqlplus -s $login @x_test

(3.)run the script
$ sh x_test.sh

it will create the table in your particular schema.

[Updated on: Mon, 20 March 2006 00:39]

Report message to a moderator

Previous Topic: fresher
Next Topic: Dissertation Topic
Goto Forum:
  


Current Time: Fri Mar 29 08:18:34 CDT 2024