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

12 comments:

  1. 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
  2. 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
  3. 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
  4. Excellent blog, I wish to share your post with my folks circle. It’s really helped me a lot, so keep sharing post like this
    Microsoft Azure online training
    Selenium online training
    Java online training
    Python online training
    uipath online training

    ReplyDelete
  5. best rice cooker. MiniTool Mac Data Recovery Boot Disk is a boot disk version of MiniTool Mac

    ReplyDelete
  6. sad shayari. We are also providing the best services click on below links

    ReplyDelete
  7. it's really nice and meanful. it's really cool blog. Linking is very useful thing.you have really helped lots of people who visit blog and provide them usefull information. stabilizer relay

    ReplyDelete
  8. Bfarf stock Real-Time Overview Of A Stock, Including Recent And Historical Price Charts, News, Events, Analyst Rating Changes And Other Key Stock Information.

    ReplyDelete
  9. I need to thank you for this very good read and i have bookmarked to check out new things from your post. Thank you very much for sharing such a useful article and will definitely save and revisit your site.

    artificial intelligence internship | best final year projects for cse | internship certificate online | internship for mba finance students | internship meaning in tamil

    ReplyDelete