Saturday, 17 September 2011

Oracle SQL*Plus Essentials

The fundamental interface to an Oracle database is an Oracle command-line utility program called SQL*Plus. This utility is available at every Oracle site and it is a critical tool for virtually every Oracle user. Despite its wide use, few developers and DBAs know how powerful this tool can be. Below are the most popular SQL*Plus commands that one should dare to learn.

Starting/Exiting
sqlplus <username>[/password]@<net_service_name> [as sysdba | as sysoper]  
Start an SQL*Plus session with SYSDBA or SYSOPER privileges connected to the Oracle Net database alias (@net_service_name). 

The password is also optional in the first command and if not written here it will not be echoed back to the user in clear text. 
exit or quit
Disconnect from the database and terminate SQL*Plus.


Executing/Loading
exec<procedure_name>
Execute a stored procedure
@<path_and_script_name>   
Runs the SQL*Plus statements in the specified script.
@@<path_and_script_name>
Similar to the previous statement but it is useful for running nested scripts because it looks for the specified script in the same path as the script which is was called.
get<path_and_script_name>
This retrieves the contents of a file and places it in the SQL buffer.
save<file_name>     
Save the commands in the SQL buffer to a file.
spo[ol][<path_and_file_name>] [CREATE | REPLACE | APPEND ]] |OFF|OUT]    
Save SQL statements together with their output to a file or optionally sends the file to a printer.

E.g.: spool c:\test.txt
    select * from dual;
    spo off

    spo c:\test.txt app
    select object_id
      from user_objects
    spo off


Others
run (short: /)
List and run the command stored in the SQL buffer
ed[it]
Edit the last command
host
Issue an operating system command

6 comments:

  1. Your new valuable key points imply much a person like me and extremely more to my office workers. With thanks from every one of us.

    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    AWS Training in Bangalore | Amazon Web Services Training in Bangalore

    ReplyDelete
  2. The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.

    angularjs Training in chennai
    angularjs Training in chennai

    angularjs-Training in tambaram

    angularjs-Training in sholinganallur

    angularjs-Training in velachery

    ReplyDelete
  3. Thanks Admin for sharing such a useful post, I hope it’s useful to many individuals for developing their skill to get good career.
    online Python certification course | python training in OMR | python training course in chennai

    ReplyDelete
  4. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    Devops Training courses
    Devops Training in Bangalore
    Best Devops Training in pune
    Devops interview questions and answers

    ReplyDelete
  5. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.
    Java training in Chennai | Java training in Bangalore

    Java online training | Java training in Pune

    ReplyDelete
  6. Nice post. By reading your blog, i get inspired and this provides some useful information. Thank you for posting this exclusive post for our vision. 
    Data Science course in kalyan nagar | Data Science course in OMR

    Data Science course in chennai | Data science course in velachery

    Data science course in jaya nagar | Data science training in tambaram

    ReplyDelete