How to run SQL in shell script

Question

How do you run a SQL command in a shell script while setting a variable   I had tried this method and it isn t executing the command  it is thinking the command is just a string      bin ksh variable1  sqlplus -s username pw oracle instance  lt  lt EOF SET PAGESIZE 0 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF select count    from table  EXIT  EOF  if   -z   variable1     then   echo  No rows returned from database    exit 0 else   echo  variable1 fi

User · Accepted Answer

bin ksh variable1     echo  set feed off set pages 0 select count    from table  exit      sqlplus -s username password oracle instance   echo  found count    variable1

User · Answer

sqlplus -s  nolog  lt  lt EOF whenever sqlerror exit sql sqlcode  set echo on  set serveroutput on   connect  lt SCHEMA gt   lt PASS gt   lt HOST gt   lt PORT gt   lt SID gt    truncate table tmp   exit  EOF

User · Answer

You can use a heredoc  e g  from a prompt     sqlplus -s username password oracle instance  lt  lt EOF set feed off set pages 0 select count    from table  exit EOF   so sqlplus will consume everything up to the EOF marker as stdin

User · Answer

Code  PL CONNECT STRING   DB USERNAME  DB PASSWORD  DB SERVICE   OUTPUT   sqlplus -s  PL CONNECT STRING  lt  lt -END-OF-SQL            select count    from table  exit  END-OF-SQL  echo  COMPLETED GATHER STATS  OUTPUT     Explanation   PL CONNECT STRING carry database username  password and it service name  sqlplus is used to connect the Database with PL CONNECT STRING details  END-OF-SQL tag contain the query which you want to execute  echo is used to print the output of the query  NOTE  You can give multiple Query inside the END-OF-SQL tag  so its useful for batch execution as well

User · Answer

Maybe it s too late for answering but  there s a working code   sqlplus -s    as sysdba   lt  lt  EOF     SET HEADING OFF     SET FEEDBACK OFF     SET LINESIZE 3800     SET TRIMSPOOL ON     SET TERMOUT OFF     SET SPACE 0     SET PAGESIZE 0     select  select instance name from v  instance  as DB NAME             file name       from dba data files      order by 2

[sql] How to run SQL in shell script

Examples related to sql

Examples related to oracle

Examples related to shell

Examples related to unix