Isqlplus command

Isqlplus command

• 1.
  Simple iSQL*PLUS An OracleiSQL*PLUS User Guide Created by Stephen Krebsbach Sept 10, 2003 Dakota State University Computer Science Department
• 2.
  Simple iSQL*PLUS version 1.0 The following document was created for the Database classes at DSU and is not a formal document of ORACLE corp. This document is a simple subset of the iSQL*PLUS interface commands that can be used to interact with an ORACLE database. iSQL*PLUS is a very powerful tool for query and report generation. More information can be found in the iSQL*PLUS user's guide. Connecting to iSQL*PLUS To connect to the iSQL*PLUS interface you should first open a web browser such as Internet Explorer or Netscape Navigator. Go to the URL: http://jaba.dsu.edu/isqlplus Your instructor will give you a username, password and connection identifier. The iSQL*PLUS Interface From this interface you can work with your database interactively or run scripts. You can also save your output to a file. To logout, use the logout button at the top right of the screen. Simple ORACLE iSQL*PLUS User Guide 2
• 3.
  Entering SQL Statements In the input window you can enter any valid ORACLE SQL statement. This can be done on iSQL*PLUS one line or several lines and is free-format. To go to a new line just press [ENTER]. Pressing offers the user [EXECUTE] will then execute the SQL statement with the resulting table being printed to the both SQL display. statements and Entering Non-SQL commands Non-SQL statements A very powerful feature of iSQL*PLUS is its formatting features along with other commands which will make processing of the SQL queries more user friendly. Many of the commands you will use in iSQL*PLUS are not SQL commands. It is important for you to understand the difference since these commands will not be available outside of the ORACLE SQL*PLUS environment. An example is the describe command that can be used to get the schema for a table. This is a command that only works within SQL*PLUS. Output Options When you execute a command, you have three options to where you would like the output to be presented. WORK SCREEN : in the current window you are working in WINDOW: in a new browser window FILE: the output can be redirected to file Loading Scripts Scripts are text files, (often called START files in ORACLE), that are a list of commands to be executed. You can load a script file into the input window by placing its location in the Script Location input box at the top right of the screen. You can type in the location or use the [BROWSE] button to help locate it. Once you have entered the location, the [LOAD SCRIPT] button will load it into the input command window. Saving a Script You can save the commands in the command input window to a script file by using the {Save Script] button at the lower right of the screen. Changing your Password You can use the [PASSWORD] button at the top of the screen to change your current password Simple ORACLE iSQL*PLUS User Guide 3
• 4.
  iSQL*PLUS Commands The following is a listing of 'some' of the iSQL*PLUS commands you may find useful. They are not in alphabetical order but instead are ordered in an attempt to present them in useful groupings etc.. Many of these commands are non-SQL. You can also use the Help located at the top right of the screen. Set {argument} SQL*PLUS allows you to set many parameters and options for your session. The set command will be used to set these parameters and options. Several of these will be introduced in this document. Many of these options are set to some default value. example: set numwidth 5 Show {argument} The show command will echo the value of the argument setting. example: show numwidth reply: numwidth 5 Remark , /* */ , -- iSQL*PLUS provides three SQL*PLUS allows you to embed comments into your SQL*PLUS statements in three (3) ways. ways to include comments. For a comment on a line by itself use remark or rem example: rem -- this is a comment and will be ignored For a one-line comment which is placed at the end of a line use -- example: select * from EMPLOYEE -- comment about this line here For comments which extend over several lines use /* */ example: /* Everything between these these delimiters is ignored as a comment */ Simple ORACLE iSQL*PLUS User Guide 4
• 5.
  Spool {filename}|{off} {NOTAVAIABLE in iSQL INTERFACE} Spool will redirect the output which is placed on the display to the filename given. It will create the file if it does not exist. If it does, it will be overwritten. Spool can be used to save the output of your query into a text file. Spool will continue to write everything displayed into the file until it is shut off. example: spool result.lst Select * from EMPLOYEE; spool off This example would save both the SQL query statement and the resulting table into the test file result.lst. It can then printed or reviewed at some later date. Save {filename}{repl}{NOT AVAIABLE in iSQL INTERFACE} Whenever you enter an SQL statement in SQL*PLUS, the statement is saved in a buffer. The Save and Start contents of this buffer contains only the SQL statement and no SQL*PLUS statements! The contents of the buffer can be saved to a text file using the save command. can be used together to example: SQL> save query1.sql create a formatted SQL The example will save the content of the buffer into a text file called query1.sql. This file can then Report which be reloaded and run later. includes both If the file being saved already exists then you must use the replace option to overwrite it. the SQL query and the example: SQL> save query1.sql repl SQL*PLUS format Start {filename} statements The start command allows you to load and execute a text file as if you had just typed it in using the keyboard. example: start query1.sql A common practice is to create the SQL statement interactively until it is getting the desired results. Saving the SQL statement into a file. Editing the file using a text editor to include any SQL*PLUS formatting statements you wish to use and then using start to execute this new file to generate a formatted report. Host {command}{NOT AVAIABLE in iSQL INTERFACE} The host command allows you to execute a host system statement without exiting SQL*PLUS. example: SQL> host ls This example shows how to get a listing from the current Unix directory from SQL*PLUS. To call the vi editor with some file you must first define the editor. example: SQL> define _editor = "vi" SQL> host vi test.sql This example allows you to call the vi editor from SQL*PLUS and have it load up the text file test.sql. On exit from the vi editor you are returned to SQL*PLUS. Simple ORACLE iSQL*PLUS User Guide 5
• 6.
  SQL*PLUS Editing Commands{NOTAVAIABLE in iSQL INTERFACE} SQL*PLUS has a simple line editor which can be used to edit the SQL command buffer. The command buffer holds the last SQL statement entered. Many time this will be quicker than saving the buffer, exiting SQL*PLUS, editing the file, reloading SQL*PLUS and then executing the command using start. It may also be quicker than using the host command with a defined editor. List [ln#] The list command will display a listing of the SQL command buffer. example: SQL> list The listing will have a line number for each line in the buffer. If you wish to list only a single line you can add the line number option. example: SQL> list 3 This example lists only line 3 in the buffer which is now considered the current buffer line. Change {/old string / new string} {NOT AVAIABLE in iSQL INTERFACE} The change command allows you to change and string found on the current buffer line with a new string. example: SQL> change / ugdate tabble / update table This example will change the substring 'ugdate tabble' with the substring 'update table' if the substring is found in the current buffer line. Slash / {NOT AVAIABLE in iSQL INTERFACE} The Slash can be used to run the contents of the current buffer. example: SQL> / This example will run what ever is in the current SQL buffer. Save {NOT AVAIABLE in iSQL INTERFACE} See Save in the SQL*PLUS Commands section. Load {filename} {NOT AVAIABLE in iSQL INTERFACE} The load command will load a saved SQL text file into the SQL command buffer. Remember that Non-SQL commands will not be put into the buffer. example: SQL> load mytest.sql The example will load the text file mytest.sql into the SQL command buffer. Simple ORACLE iSQL*PLUS User Guide 6
• 7.
  iSQL*PLUS Report FormatCommands iSQL*PLUS also has several options which can be used with the set command to format the output of an SQL query. There are also other SQL*PLUS commands which can be used for report generation. DOCUMENTATION Using the Help feature provided in the iSQL interface will bring up a screen with links help on all the commands in iSQL*PLUS and links to SQL and P/SQL commands Simple ORACLE iSQL*PLUS User Guide 7