Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2004
    Posts
    2

    Exclamation Unanswered: SQL*PLUS Question

    When I was learning the SQL*PLUS, I found that there were few lines of commands that I did not understand. Can somebody please explain for me:

    1. SET ECHO OFF & SET ECHO ON
    2. SET VERIFY OFF & SET VERIFY ON
    3. NVL
    4. COLUMN name FORMAT A20 & COLUMN name CLEAR

    Can somebody please provide me some examples and explain these??

    Thanks a lot


  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Did you look in the manual at all?

    1) SET ECHO ON means: show the commands being executed when I run a SQL Plus script:
    Code:
    SQL> @junk
    
    ENAME
    --------------------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    
    SQL> set echo on
    SQL> @junk
    SQL> select ename from emp;
    
    ENAME
    --------------------
    SMITH
    ALLEN
    WARD
    JONES
    MARTIN
    BLAKE
    CLARK
    SCOTT
    KING
    TURNER
    ADAMS
    JAMES
    FORD
    MILLER
    
    14 rows selected.
    2) SET VERIFY ON means: show me the values actually used for substitution variables

    Code:
    SQL> set verify on
    SQL> select &v from dual;
    Enter value for v: 123
    old   1: select &v from dual
    new   1: select 123 from dual
    
           123
    ----------
           123
    
    SQL> set verify off
    SQL> select &v from dual;
    Enter value for v: 123
    
           123
    ----------
           123
    3) NVL is part of Oracle SQL, not SQL Plus. It returns its first argument if it is not null, else it returns the second argument:

    Code:
    SQL> select nvl(1,2) from dual;
    
      NVL(1,2)
    ----------
             1
    
    SQL> select nvl(null,2) from dual;
    
    NVL(NULL,2)
    -----------
              2
    4) The COLUMN command formats output:

    Code:
    SQL> select ename, deptno from emp;
    
    ENAME          DEPTNO
    ---------- ----------
    SMITH              20
    ALLEN              30
    WARD               30
    JONES              20
    MARTIN             30
    BLAKE              30
    CLARK              10
    SCOTT              20
    KING               10
    TURNER             30
    ADAMS              20
    JAMES              30
    FORD               20
    MILLER             10
    
    14 rows selected.
    
    SQL> column ename format a20
    SQL> /
    
    ENAME                    DEPTNO
    -------------------- ----------
    SMITH                        20
    ALLEN                        30
    WARD                         30
    JONES                        20
    MARTIN                       30
    BLAKE                        30
    CLARK                        10
    SCOTT                        20
    KING                         10
    TURNER                       30
    ADAMS                        20
    JAMES                        30
    FORD                         20
    MILLER                       10
    
    14 rows selected.
    The CLEAR option undoes the formatting previously applied (reverts to default).

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    docs docs docs:
    Code:
    http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a82950/ch8.htm
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •