This discussion is archived
5 Replies Latest reply: Oct 24, 2012 7:21 AM by Paul M. RSS

echoing comands in sql*plus

549039 Newbie
Currently Being Moderated
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. Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    Wow... what a timely response !! :-) just 16 months...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points