Home » Infrastructure » Unix » how to run .sql file from shell script
how to run .sql file from shell script [message #611596] Fri, 04 April 2014 03:44 Go to next message
ashwanth77
Messages: 95
Registered: April 2013
Location: India
Member
how to run .sql file from shell script

$ @partition_dict_queries.sql
sh: @partition_dict_queries.sql: not found.
Re: how to run .sql file from shell script [message #611612 is a reply to message #611596] Fri, 04 April 2014 07:51 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
use sqlplus or other SQL client
Re: how to run .sql file from shell script [message #611613 is a reply to message #611596] Fri, 04 April 2014 07:52 Go to previous messageGo to next message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Run it with sqlplus:

sqlplus user/passwort@database @partition_dict_queries.sql
Re: how to run .sql file from shell script [message #611922 is a reply to message #611613] Thu, 10 April 2014 03:54 Go to previous message
rc3d
Messages: 213
Registered: September 2013
Location: Baden-Württemberg
Senior Member
Here is a KSH (Korn Shell) function. Output of SQL query is written to file.

generate_batch ()
{
export DBO=
export DBOPW=
export ORACLE_SID=

sqlplus -S $DBO/$DBOPW@$ORACLE_SID <<EOF > /.../batchrun.$(/bin/date '+%d%m%Y.%Hh')

set echo Off
set term On
set pages 0
set head off
set ver off
set feed off
set trims on
set linesize 20000

WITH data
        AS (SELECT user_id,
                   jc_name,
                   upd_time,
                   RANK () OVER (PARTITION BY user_id ORDER BY upd_time ASC)
                      rk
              FROM user_jc
             WHERE user_id IN (  SELECT user_id
                                   FROM user_jc
                                  WHERE JC_NAME LIKE 'TTMR\_S\_%' ESCAPE '\'
                               GROUP BY user_id
                                 HAVING COUNT (user_id) > 1)
                   AND JC_NAME LIKE 'TTMR\_S\_%' ESCAPE '\')
SELECT    'DISCONNECT ent_user  FROM job_code WITH user_id = "'
       || user_id
       || '", jc_name = "'
       || jc_name
       || '";'
  FROM data
 WHERE rk = 1;

exit
EOF
}
Previous Topic: Problem Passing Variables Into SQL*Plus
Next Topic: -bash: Not a directory
Goto Forum:
  


Current Time: Thu Mar 28 14:08:09 CDT 2024