SQLPLUS TIPS

1.SQLPLUS : a tool that execute sql ,sqlplus ,sqlplus command.

                     1.sqlplus command-line  cmd-sqlplus /nolog

                     2.sqlplus GUI   sqlplusw /nolog

                     3.sqlplus web   http://127.0.0.1:5560/isqlplus/

                                              http://ip:5560/isqlplus/dba

                     isqlplusctl start

  how to start sqlplus dba login

  [oracle@Toshiba ~]$ JAVA_HOME=$ORACLE_HOME/jdk
  [oracle@Toshiba ~]$ export JAVA_HOME
  [oracle@Toshiba ~]$ cd $ORACLE_HOME/oc4j/j2ee/isqlplus/application-deployments/isqlplus

  [oracle@Toshiba isqlplus]$ $ORACLE_HOME/jdk/bin/java -Djava.security.properties=$ORACLE_HOME/sqlplus/admin/iplus/provider -jar $ORACLE_HOME/oc4j/j2ee/home/jazn.jar -shell

  RealmLoginModule username: admin
  RealmLoginModule password:********  welcome

  JAZN:> adduser "iSQL*Plus DBA" oracle oracle
  JAZN:> grantrole webDba "iSQL*Plus DBA" oracle
  JAZN:> listusers
  iSQL*Plus DBA/admin
  iSQL*Plus DBA/oracle

  JAZN:> exit

2.sqlplus -h -v -s

 

3.sqlplus /log  ;login sqlplus

 

4.sqlplus  /  as sysdba  (oracle 10g)  sqlplus  "/ as sysdba" (oracle 9i)

   sqlplus  / 

   sqlplus hr

 

5.run  /  start  @

   start mysql.sql  p1 p2

   @mysql p1 p2

 

6.accep 

   

7.list  :display the contents of  SQL buffer ,the last line is the current line.

   l2:  the current line is number 2line.

   c:  changeor replace  the current line character.       c /t/emp

   a: appent the text to the end of the current line.     a where id='1'

   i:  insert a line after the current line.

       0 /*hhhhhhhhhhhhhhhhhh*/  inser  a line before the first line.

   del:  del n m

           del * last

           del *

           del *  n

           del  2

   list  *

   list n m

   list  m

 

   remark:  add comments  rem this is comments

   /* this is comment */

   --thjisssdsds

 

8.save/get filename

 

9.SQL buffer:store recent sql and pl/sql command.

 

10.?=$ORACLE_HOME

 

11.$ORACLE_HOME/sqlplus/admin/help/hlpbld.sql helpus.sql      create sqlplus help command

     $ORACLE_HOME/sqlplus/admin/help/helpdrop.sql

 

12.help save:  get help infor about sqlplus command

 

     help index : display all sqlplus command

 

13.quit,exit,ctrl+d,ctrl+z

 

14.desc :display defination about table,procedure,function

 

15.; / run sql command

 

16. . :terminate pl/sql  / : run pl/sql

 

17.show  display the content about sqlplus command

 

18.set serveroutput on/off

 

19.col id format $99.999 -

     heading "your ID"

 

20.ctrl+c: stop a command while it is running

 

21.! or $or host:use os command in the sqlplus

 

22. set linesize

 

      set pagesize

 

23.set pause on/off  :pause display

 

     set pau '////////////'  

25.set autocommit on :save change to database automatic

 

26.@ or @@ or start : run script

 

27.define _editor=vi      in sqlplus ,edit script

     ed lab1

 

28.set echo on/off   

     set verify on/off   suppress the display of each line before and after substitution:

 

29.run script as you start sqlplus

     1.sqlplus hr/hr @con.sql

     2.vi con.sql

        hr/hr

        select * from dept;

        sqlplus @con.sql

 

30.spool query.sql

     select  * from dept

     select 'drop table ' || object_name || ';' from user_objects where object_type='TABLE'

     spool off

 

31. substitution variables

      define d_loc=CHICAGO

      define d_dname=&dname

      define d_deptno=&1

      select * from dept where loc=&d_loc and dname=&d_name and deptno=&d_deptno; 

      select * from emp  where deptno=&d_deptno

 

32. Using Bind Variables

      var val number

      begin

         :val :=1;

      end;

      print val

 

      var emp_infor refcursor

      begin

        open  :emp_infor for select * from emp;

      end; 

 

      create or replace package myemppkg as

          type empinforcur is ref cursor;

          procedure  pmyempkg(res in out  empinforcur);

      end;

      create or replace package body myemppkg as

          procedure  pmyempkg(res in out  empinforcur) as

          begin

               open  res  for  select  *  from emp;

          end;

      end;

      var emps refcursor;

      exec myemppkg.pmyempkg(emps);

 

      create or replace function femp

      return restype  is reset  restype;

      begin

              open reset  for select * from emp;

              return(reset);

      end;

33.column

     column empno heading '--empno--'

     column empno format a30

     column empno format $999,999,999