Introduction SQL*Plus (pronounced "sequel plus") is an interactive tool for the Oracle RDBMS environment. SQL*Plus can be used simply to process SQL statements one at a time, process SQL statements interactively with end users, utilize PL/SQL for procedural processing of SQL statements, list and print query results, format query results into reports, describe the contents of a given table, and copy data between databases. SQL*Plus originated from the beginning of the Oracle RDBMS days as a product called User Friendly Interface (UFI). UFI was used primarily to administer the Oracle environment. UFI was later renamed to SQL*Plus with the advent of Oracle Version 5. There have been some improvements to SQL*Plus from the UFI days; however, most of the commands and the ease of formatting results are as easy today as they were with the UFI product. There have been additions to several of the command capabilities, additional ways of starting SQL*Plus, and a changed role for SQL*Plus through the major releases of the Oracle RDBMS kernel. For example, before Oracle Version 6, using UFI or SQL*Plus was the only way to administrate the Oracle database. With Oracle Version 6 came a new tool called SQL*DBA that took over many of the database responsibilities such as backup and recovery and startup and shutdown. SQL*Plus also exists in the world of client/server and is available with all the major graphical interfaces. SQL*Plus is the main ad hoc, character-mode interface to the Oracle RDBMS. SQL*Plus can easily be used to produce a variety of types of character mode reports. SQL*Plus can also be used to create dynamic SQL*Plus scripts or even dynamic operating-system specific command language programs. SQL*Plus can be used for some Oracle administration functions, and it can be programmed to be interactive during a specific terminal session. SQL*Plus can process ANSI SQL as well as PL/SQL blocks. SQL*Plus Commands There are five types of SQL*Plus commands: Those that initiate the SQL*Plus environment SQL*Plus Execute commands SQL*Plus Editing commands SQL*Plus Formatting commands Miscellaneous commands SQL*Plus Execute Commands The execute commands are used to initiate the processing of SQL statements and PL/SQL blocks, measure the processing time of SQL or PL/SQL statements, execute non-Oracle programs, execute SQL*Forms programs, or attain additional help. / - Execute the SQL statement or PL/SQL block currently in the SQL buffer. (This is probably the most used of the SQL*Plus commands.) HELP - Provides online assistance with SQL, PL/SQL, or SQL*Plus commands. HOST - Execute non-Oracle commands (operating system-dependent) without leaving SQL*Plus. RUN - Displays and executes the contents of the SQL buffer. RUNFORM - Execute a SQL*Forms program without leaving SQL*Plus. TIMING - Displays the system CPU time with the SQL prompt. SQL*Plus Editing Commands The SQL buffer is a work area assigned to the SQL*Plus environment. This buffer contains only SQL or PL/SQL syntax. You can use the following commands to load, save, and manipulate the contents of this buffer: A new text or APPEND new text - Appends text to the end of the current line of the SQL buffer. C/target text/new text/ or CHANGE/target text/new text/ - Changes the target text to the new text on the current line in the SQL buffer. DEL - Deletes the current line in the SQL buffer. EDIT filename - Utilizes an operating system-dependent text editor. To edit the SQL buffer with an operating system-dependent text editor, simply leave off the filename. GET filename - Reads an operating system-dependent file into the SQL buffer. I text or INPUT text - Adds the text after the current line in the SQL buffer. L number or LIST number - Displays the contents of the SQL buffer. When the number syntax is used, LIST will display the line number and make that line the current line in the SQL buffer. SAVE filename Saves the contents of the SQL buffer to an operating system-dependent file. START filename param1 param2 ... - START will execute the contents of the SQL*Plus command file named in filename and pass any input parameters to the SQL*Plus command file. SQL*Plus Formatting Commands The SQL*Plus formatting commands are used to manipulate the result set from a SQL query. BREAK ON column_name and options This command controls the organization of rows returned by the query. BREAK can manipulate the appearance of the output by specifying under what conditions a BREAK should occur and what actions should be taken at the BREAK. The appearance of the output can be controlled by skipping a line or skipping to top of next page and providing totals when used in conjunction with COMPUTE. Any number of lines can be skipped at a BREAK point. BREAK points can be defined at the column level, for multiple columns, on a row, on a page, or on a report. Entering BREAK by itself at the SQL prompt will display the current BREAK settings. BTITLE print_options and/or text or variable options BTITLE is used to place text at the bottom of each page. There are various print options that position text at various locations. BTITLE will simply center the text if no print options are specified. print options include BOLD, CENTER, COL, FORMAT, LEFT, RIGHT, SKIP, and TAB. BTITLE spelled out by itself will display the current text setting. Other options that can be specified are ON and OFF. BTITLE is ON by default. CLEAR and options CLEAR resets any of the SQL*Plus formatting commands. You can also use it to clear the screen. The options include BREAKS, BUFFER, COLUMNS, COMPUTES, SCREEN, SQL, and TIMING. COLUMN column_name and options COLUMN is used to alter the default display attributes for a given column (column_name) of a SQL query. There are a variety of options, but the more common ones are FORMAT, HEADING, JUSTIFY, NEWLINE, NEW_VALUE, and NOPRINT. The FORMAT option is useful in applying editing to numeric fields, date masks to date fields, and specific lengths to variable-length character fields. The HEADING option overrides the SQL*Plus default heading for the particular column. The JUSTIFY option overrides the SQL*Plus column alignment to the heading default. The NEWLINE option will print the column on the beginning of the next line. NEW_VALUE assigns the contents of the column to a SQL*Plus variable . This value can then be used in conjunction with TTITLE or to store intermediate results for master/detail type reports, and is useful to store and pass information between two or more separate SQL statements. COMPUTE function OF options ON break options COMPUTE calculates and prints totals for groupings of rows defined by the BREAK command. A variety of standard functions can be utilized. The most common option is the name of the column in the query on which the total is to be calculated. The break option determines where the totals are to be printed and reset, as defined by the BREAK command. Miscellaneous Commands This section presents a variety of commands that enable you to interact with the user, comment on the code, and enhance coding options. ACCEPT variable number or char PROMPT text ACCEPT receives input from the terminal and places the contents in variable. This variable can already have been defined with the DEFINE command. If the PROMPT option is specified, then the text will be displayed after skipping a line. The variable attributes of number or char can be defined at this time. The variable will be a char if not otherwise defined. DEFINE variable DEFINE creates a user-defined variable and assigns it to be of char (character) format. This variable can be assigned a default value at this time. DESC or DESCRIBE database object DESCRIBE displays the columns associated with a table, view, or synonym. PAUSE text PAUSE prints the contents of text after skipping a line, and then waits for the Return or Enter key to be pressed. PROMPT text PROMPT simply skips a line and prints the contents of text. REM or REMARK SQL*Plus will ignore the contents of this line when it is used in SQL*Plus command files. REMARK enables documentation or other comments to be contained in these SQL*Plus command files. SET SQL*Plus System Variable The SET command controls the default settings for the SQL*Plus environment. You can automatically alter these settings for each SQL*Plus session by including them in the LOGIN.SQL file. The following are some common SET options utilized for reporting: SET LINESIZE 80 Controls the width of the output report line SET PAGESIZE 55 Controls the number of lines per page The following are some common SET options that suppress various SQL*Plus output: SET FEEDBACK OFF - Suppresses the number of query rows returned SET VERIFY OFF - Suppresses the substitution text when using &variables, including command line variables SET TERMOUT OFF - Suppresses all terminal output; this is particularly useful in conjunction with the SPOOL command SET ECHO OFF - Suppresses the display of SQL*Plus commands SPOOL filename or options The SPOOL command is used to open, close, or print an operating system-dependent file. Specifying SPOOL filename will create an operating system-dependent file; filename can contain the full pathname of the file and extension. If no file extension is given, the file suffix, LST, will be appended (filename.LST). Options include OFF or OUT. If OFF is specified, then the operating system-dependent file is simply closed. If OUT is specified, then the operating system-dependent file is closed and sent to the operating system-dependent printer assigned as the default printer to the user's operating system environment. UNDEFINE variable UNDEFINE removes the previously Defined variable from the SQL*Plus environment.
SQL*Plus, the primary interface to the Oracle Database server, provides a powerful yet easy-to-use environment for querying, defining, and controlling data. SQL*Plus delivers a full implementation of Oracle SQL and PL/SQL, along with a rich set of extensions. The exceptional scalability of the Oracle Database, coupled with the object-relational technology of SQL*Plus, allows you to develop your complex datatypes and objects using Oracle's integrated systems solution.
Some Tips with SQL PLus Many times we may open several SQL*PLUS windows meaning serveral database consoles. In order to identify the right console we can do, Code: SQL> host hostname Queen SQL> show parameter db_name NAME TYPE VALUE ------------------------------------ ---------------------------- db_name string kidas SQL> show user USER is "SYS" to be sure on which database as which user we are actually going to run commands. So after three commands we would run a sql. By setting SQL*plus environmental variable connection identifier we can get rid of it and be sure on which console we are connecting to. Like, Code: SQL> SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > " SYS@kidas > And now rename the session as of hostname. cheers
SQL*Plus file command allow you to execute commands (or programs) stored in an external file, input or output data from/to a file, and save SQL commands typed during current session. Some SQL*Plus file commands are: * SAVE filename. This allows you to save buffer contents into a file. * START filename. This allows you to execute a batch of SQL statements stored in a file. * SPOOL filename. This allows you save SQL statements together with their outputs to a file. * GET filename. This retrieve a file and places it into the buffer. * @ filename. This allows you to execute a PL/SQL procedure(s) stored in a file.
Oracle SQL * Plus is a command-line utility program that can run SQL and PL / SQL commands interactively or from script. SQL * Plus operation is relatively simple tool, as a basic command line interface. Programmers and database administrators often use it to provide the basic interface as the default, almost all of the Oracle software installation.
Hi All, SQL* Plus has been deprecated by Oracle since the release of Oracle 11g. Oracle has recommended the use of Command prompt for the execution of SQL scripts
