2 Replies Latest reply on Jun 6, 2014 6:57 PM by Mark D Powell

    Show number of rows affected on running sqlplus for a sql file

    ysri

      Hi,

      we have a sql file containing a bunch of select/update/insert statements, that's it.

       

      I have a unix script that invokes sqlplus on this sql file. However, in the output I don't see the number of rows affected.

      Without adding any code to the input sql file, how can I modify the shell script sqlplus code to output # of rows affected for each statement in the sql file?

       

      ${ORACLE_HOME}/bin/sqlplus -L >> ${LOG_FILE} 2>&1  << EOF
        ${ORAID}/${ORAPASS}@${ORAINS}
        whenever sqlerror exit sql.sqlcode;
        set echo on;
        set feedback off;
        set serveroutput on;
        @${SQL_FILE};
        exit;
      EOF
      RC=$?
      

       

      The input SQL_FILE contents are:

      select 12 "col1" from dual;
      select 'abcd' "col2" from dual;
      select;
      select 13 'col3' from dual;
      

       

       

      The output of the sqlplus run is (LOG_FILE):

      SQL*Plus: Release 11.2.0.2.0 Production on Fri Jun 6 14:28:00 2014
      
      Copyright (c) 1982, 2010, Oracle.  All rights reserved.
      
      Enter user-name: 
      Connected to:
      Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options
      
      SQL> SQL> SQL> SQL> SQL> SQL> select 12 "col1" from dual;
      
            col1
      ----------
              12
      SQL> select 'abcd' "col2" from dual;
      
      col2
      ----
      abcd
      SQL> select;
      select
           *
      ERROR at line 1:
      ORA-00936: missing expression
      
      
      Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
      With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
      Data Mining and Real Application Testing options
      

       

       

      Thanks,

      -srinivas y.