1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle SQL*plus tips

Discussion in 'General' started by Arju, Oct 10, 2008.

  1. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    You can use TOAD, tora or any other client tools in order to interact with database. But you should remember SQL*Plus is also a good tool. Some tips of SQL*Plus tools are given below.

    You can end up your sql command by using

    1.semicolon ;
    2.or /
    3.or simply a blank line (Depend on SET SQLBLANKLINES Settings)

    Now let's see a query,
    Code (Text):
    SQL> select * from test;

    NAME                B
    ---------- ----------
    sadik               1
    arju                1
    momin               3
    Abdul               4
    sadik               3
     
    As you know up arrow is not available in sql*plus. And now I can run the same query by slash (/) keyword. But I wish to see all columns of test table where name is momin. You can simply append with a and then write your words. And then run with slash.

    Code (Text):
    SQL> a  where name='momin';
      1* select * from test where name='momin'
    SQL> /

    NAME                B
    ---------- ----------
    momin               3
    Now you can change any word written in previous command. Suppose you want info of sadik instead of momin. Then change with c and write as c/old_value/new_value like below.

    Code (Text):
    SQL> c/momin/sadik
      1* select * from test where name='sadik'
    SQL> /

    NAME                B
    ---------- ----------
    sadik               1
    sadik               3
    Now I like to add one line by input with i and then write words.
    Code (Text):
    SQL> i and b=3
    SQL> /

    NAME                B
    ---------- ----------
    sadik               3
    Here we see with / the command is not displayed as we did not gave semicolon. In order to display the command then you ran immediately to run again with displaying use run. It will display command along with line number.

    Code (Text):
    SQL> run
      1  select * from test where name='sadik'
      2* and b=3

    NAME                B
    ---------- ----------
    sadik               3
    You can delete any line number by simply delete with del and then line number as below.
    Code (Text):
    SQL> del 2
    SQL> /
    NAME B
    ---------- ----------
    sadik 1
    sadik 3

    In order to print any line just enter list along with line number..
    Code (Text):
    SQL>  select * from test where name='sadik'
      2  and
      3  b=1;

    NAME                B
    ---------- ----------
    sadik               1
     
    We see three line. l 2 will display line number 2 and so.
    Code (Text):
    SQL> l 2
      2* and
    SQL> l 3
      3* b=1
    To display 2 t 3 lines use,
    Code (Text):
    SQL> l 2 3
      2  and
      3* b=1
    * above indicates current line.
    To print line 2 to current line use,
    Code (Text):
    SQL> l 2 *
      2  and
      3* b=1
    To print current line,
    Code (Text):
    SQL> l *
      3* b=1
     
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Excellent post Arju... i personally use TOAD because because in my line of work direct coding is seldom needed, but very good tips. Will try out in SQL Plus!
     
  3. Arju

    Arju Forum Expert

    Messages:
    107
    Likes Received:
    5
    Trophy Points:
    240
    Location:
    Bangladesh
    Several more which is not available on other clients.
    Many times we open several SQL*PLUS windows means serveral databases console. Then what we would do to enter a SQL command. In order to identify the right console we would do,
    Code (Text):
    SQL> host hostname
    Queen

    SQL> show parameter db_name

    NAME                                 TYPE        VALUE
    ------------------------------------    ----------------------------
    db_name                              string      arju

    SQL> show user
    USER is "SYS"
     
    to be sure on which database as which user we are actually going to run commands.

    So after three commands we would run a sql. By setting SQL*plus environmental variable connection identifier we can get rid of it and be sure on which console we are connecting to. Like,
    Code (Text):
    SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
    SYS@arju >
    And now rename the session as of hostname.