SQL*Plus and Oracle

SQL*Plus is a command line SQL and PL/SQL language interface and reporting tool that ships with the Oracle Database Client and Server.

Connect to a Oracle (10g) Database using SQL*Plus

• From the Start menu find SQL*Plus in oracle home (If you are going to connect from a remote machine you need to have an oracle client installed on it).
  
• Give User Name, Password and Host string
• If you haven’t created specific users, you can log using System or Sys accounts. Password is what you given in the installation. Host String is also asking from you in the configuration just after installation.
  
• Anyhow if you forgot the Host String you can find it on the following location. “\oracle\product\10.2.0\client_1\NETWORK\ADMIN\tnsnames.ora” Here as I have noticed many times this oracle folder is not in the local drive which your Program Files folder exists. Just check other Local Drives as well. Inside tnsnames.ora file there should be a segment like follows.
  

  # tnsnames.ora Network Configuration File: D:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora
  # Generated by Oracle configuration tools.
  

  ORCL =
  (DESCRIPTION =
  (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.11)(PORT = 1521))
  )
  (CONNECT_DATA =
  (SERVICE_NAME = XE)
  )
  )
  

• Here ORCL is the Host String. You can give any name for it as you like. SERVICE_NAME is XE for oracle express edition and orcl for oracle enterprise edition in default.
  
• You can find exact value from Registry Editor of server machine in the following location.
  
• HKEY_LOCAL_MACHINE – SOFTWARE – ORACLE – SYSMAN – OracleDBConsoleorcl – ORACLE_SID
  

• After you connected to server you can get a list of tables using following command.


• SQL> select * from tab;
  

• 3 kinds of commands can be executed from SQL*Plus
  
  • SQL*Plus commands - used to set options for SQL*Plus, format reports, edit files, edit the command buffer, and so on
    
  • SHOW USER
    

• SQL commands
  
• SELECT * FROM TAB;
  

• PL/SQL blocks
  
• BEGIN
  DBMS_OUTPUT.PUT_LINE (‘Hello World!’);
  END;
  /