5 Replies Latest reply: Oct 24, 2012 9:21 AM by Paul M. RSS

    echoing comands in sql*plus

    549039
      Hi guys,
      Funny thing I cannot echo back the command that I'm issuing... I need this because when i issue @script i want to know what is running... but set echo on doesn't seam to work
      Connected to:
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
      With the Partitioning, OLAP, Data Mining and Real Application Testing options
      
      SQL> set echo on
      SQL> select 1 from dual;
      
              1
      ---------
              1
      
      SQL> set echo off;
      SQL> select 1 from dual;
      
              1
      ---------
              1
      
      SQL> set echo on;
      SQL> select 1 from dual;
      
              1
      ---------
              1
      any ideea how to output also the command?
        • 1. Re: echoing comands in sql*plus
          Paul M.
          when i issue @script i want to know what is running
          Did you try that way ?
          SQL> get sel1
            1* select 1 from dual;
          SQL> @sel1
          
                   1
          ----------
                   1
          
          SQL> set echo on
          SQL> @sel1
          SQL> select 1 from dual;
          
                   1
          ----------
                   1
          
          SQL>
          • 2. Re: echoing comands in sql*plus
            549039
            Actually no...
            what I need is to use something like
            --file test.sh
            #!/bin/bash
            sqlplus usr/pwd@sid @script.sql 
            and
            --file script.sql
            set timing on echo on
            update tableA set x=1;
            delete tableA where x=1;
            and the output to be:
            update tableA set x=1;
            54 rows updated
            Elapsed: 00:00:00:05
            
            delete tableA where x=1;
            54 rows deleted
            Elapsed: 00:00:00:05
            but also for selects ... i don't care about the header and stuff

            LE:
            test.sh like
            sqlplus  ora61/ora61 @script.sql
            [oracle@laptop shells]$ sh test.sh
            
            SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 9 22:04:04 2011
            
            Copyright (c) 1982, 2009, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            SQL> update emp2 set job_id = 'STCLERK' where job_id != 'STCLERK';
            
            0 rows updated.
            
            Elapsed: 00:00:00.73
            SQL> select count(1) from emp2 where job_id = 'STCLERK';
            
              COUNT(1)
            ----------
               2999486
            
            Elapsed: 00:00:00.55
            SQL> exit;
            Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            [oracle@laptop shells]$ 
            when
            sqlplus  ora61/ora61 <<EOS
            set echo on timing on
            update emp2 set job_id = 'STCLERK' where job_id != 'STCLERK';
            select count(1) from emp2 where job_id = 'STCLERK';
            exit;
            EOS
            [oracle@laptop shells]$ sh test.sh
            
            SQL*Plus: Release 11.2.0.1.0 Production on Thu Jun 9 22:07:50 2011
            
            Copyright (c) 1982, 2009, Oracle.  All rights reserved.
            
            
            Connected to:
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            
            SQL> SQL> 
            0 rows updated.
            
            Elapsed: 00:00:00.68
            SQL> 
              COUNT(1)
            ----------
               2999486
            
            Elapsed: 00:00:00.50
            SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            With the Partitioning, OLAP, Data Mining and Real Application Testing options
            [oracle@laptop shells]$ 
            i realy wold have liked to use only one file... but it seams that this way ecoing does not work :(

            Edited by: kquizak on Jun 9, 2011 9:49 PM

            Edited by: kquizak on Jun 9, 2011 10:04 PM
            • 3. Re: echoing comands in sql*plus
              Paul M.
              it seams that this way ecoing does not work :(
              SET ECHO ON only works with START (@) command. See http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/ch_twelve040.htm#i2698923
              • 4. Re: echoing comands in sql*plus
                827342
                Try something like this...

                cat << EOF > temp.sql

                set echo on

                select ...

                exit;

                EOF

                sqlplus jsmith/password@my_database @temp.sql
                • 5. Re: echoing comands in sql*plus
                  Paul M.
                  Wow... what a timely response !! :-) just 16 months...