{"id":174,"date":"2009-06-24T10:48:21","date_gmt":"2009-06-24T05:18:21","guid":{"rendered":"http:\/\/www.oratraining.com\/blog\/?p=174"},"modified":"2009-06-24T10:48:21","modified_gmt":"2009-06-24T05:18:21","slug":"sqlplus-basic-commands","status":"publish","type":"post","link":"https:\/\/www.oratraining.com\/blog\/2009\/06\/sqlplus-basic-commands\/","title":{"rendered":"SQL*Plus Basic commands"},"content":{"rendered":"<h2>Usi<a href='http:\/\/atlantic-drugs.net\/products\/tretinoin-cream-0-05-.htm'>n<\/a>g SQL*Plus<\/h2>\n<p>SQL*Plus is a command-line tool that provides access to the Oracle RDBMS.<\/p>\n<p>SQL*Plus enables you to:<\/p>\n<ul type=\"disc\">\n<li>Enter SQL*Plus commands to configure the SQL*Plus environment<\/li>\n<li>Startup and shutdown an Oracle database<\/li>\n<li>Connect to an Oracle database<\/li>\n<li>Enter and execute SQL commands and PL\/SQL blocks<\/li>\n<li>Format and print query results<\/li>\n<\/ul>\n<p>SQL*Plus is available on several platforms. In addition, it has a web-based user<\/p>\n<p>interface, <em>i<\/em>SQL*Plus.<\/p>\n<p>SQL*Plus is a client terminal software allowing users to interact with Oracle server to manipulate data and data structures. Users type in SQL statements in SQL*Plus that send statements to Oracle server. Oracle server then validates and executes the statements on its databases. The query results are returned to SQL*Plus and displayed to the user.\u00a0 Besides sending SQL statements to the server, SQL*Plus also saves them into a local buffer and allow users to view and change the statements. The following figure illustrates the process.<!--more--><\/p>\n<p>After you login into SQL*Plus, at the SQL prompt, you can begin typing any SQL command. Upon hitting return (i.e., enter key) the SQL prompt will change to line number prompts. When you are finished typing a command, type \/ or RUN to execute the SQL command. Also, a semicolon at the end of the SQL command will execute the command immediately after hitting return. In addition to SQL commands, \/, and RUN, you can also executes SQL*Plus file commands.<\/p>\n<p><strong>SQL*PLUS Commands Quick Reference<\/strong><\/p>\n<p>Below table shows, SQL*Plus commands available in the command-line interface. Not all commands or command parameters are shown.<\/p>\n<table border=\"1\" cellspacing=\"0\" cellpadding=\"0\">\n<tbody>\n<tr>\n<td width=\"175\" valign=\"top\"><strong>How to &#8230;<\/strong><\/td>\n<td width=\"415\" valign=\"top\"><strong>SQL*Plus Command<\/strong><\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Log in to SQL*Plus<\/td>\n<td width=\"415\" valign=\"top\">SQLPLUS [ { username[\/passward][@connect_identifier] | \/ }\u00a0 [ AS { SYSDBA | SYSOPER } ] | \/NOLOG ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">List help topics available in SQL*Plus<\/td>\n<td width=\"415\" valign=\"top\">HELP [ INDEX | topic ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Execute host commands<\/td>\n<td width=\"415\" valign=\"top\">HOST [ command ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Show SQL*Plus system variables or environment settings<\/td>\n<td width=\"415\" valign=\"top\">SHOW { ALL | ERRORS | USER | system_variable | &#8230; }<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Alter SQL*Plus system variables or environment settings<\/td>\n<td width=\"415\" valign=\"top\">SET system_variable value<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Start up a database<\/td>\n<td width=\"415\" valign=\"top\">STARTUP PFILE = filename\u00a0 [ MOUNT [ dbname ] | NOMOUNT | &#8230; ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Connect to a database<\/td>\n<td width=\"415\" valign=\"top\">CONNECT [ [ username [ \/password ] [ @connect_identifier ]\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 [ \/ AS { SYSOPER | SYSDBA } ]<\/p>\n<p>]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">List column definitions for a table, view, or synonym, or specifications for afunction or procedure<\/td>\n<td width=\"415\" valign=\"top\">DESCRIBE [ schema. ] object<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Edit contents of the SQL buffer or a file<\/td>\n<td width=\"415\" valign=\"top\">EDIT [ filename [ .ext ] ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Get a file and load its contents into the SQLBuffer<\/td>\n<td width=\"415\" valign=\"top\">GET filename [ .ext ] [ LIST | NOLLIST ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Save contents of the SQL buffer to a file<\/td>\n<td width=\"415\" valign=\"top\">SAVE filename [ .ext ] [ CREATE | REPLACE | APPEND ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">List contents of the SQL Buffer<\/td>\n<td width=\"415\" valign=\"top\">LIST [ n | nm | n LAST | &#8230; ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Delete contents of the SQL Buffer<\/td>\n<td width=\"415\" valign=\"top\">DEL [ n | nm | n LAST | &#8230; ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Add new lines following current line in the SQL buffer<\/td>\n<td width=\"415\" valign=\"top\">INPUT [ text ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Append text to end ofcurrent line in the SQL<\/p>\n<p>buffer<\/td>\n<td width=\"415\" valign=\"top\">APPEND text<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Find and replace first occurrence of a text string in current line of the SQL buffer<\/td>\n<td width=\"415\" valign=\"top\">CHANGE sepchar old [ sepchar [ new [ sepchar ] ] ]sepchar can be any non-alphanumeric character such as &#8220;\/&#8221; or &#8220;!&#8221;<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Capture query results in a file and, optionally, send contents of file to default printer<\/td>\n<td width=\"415\" valign=\"top\">SPOOL [ filename [ .ext ]\u00a0 [ CREATE | REPLACE | APPEND | OFF | OUT ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Run SQL*Plus statements stored in a file<\/td>\n<td width=\"415\" valign=\"top\">@ { url | filename [ .ext ] } [ arg&#8230; ]START filename [ .ext ] [ arg&#8230; ]<\/p>\n<p>.ext can be omitted if the filename extension is .sql<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Execute commands stored in the SQL buffer<\/td>\n<td width=\"415\" valign=\"top\">\/<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">List and execute commands stored in the SQL buffer<\/td>\n<td width=\"415\" valign=\"top\">RUN<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Execute a single PL\/SQL statement or run a stored procedure<\/td>\n<td width=\"415\" valign=\"top\">EXECUTE statement<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Disconnect from a database<\/td>\n<td width=\"415\" valign=\"top\">DISCONNECT<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Shut down a database<\/td>\n<td width=\"415\" valign=\"top\">SHUTDOWN [ ABORT | IMMEDIATE | NORMAL | &#8230; ]<\/td>\n<\/tr>\n<tr>\n<td width=\"175\" valign=\"top\">Log out of SQL*Plus<\/td>\n<td width=\"415\" valign=\"top\">{ EXIT | QUIT }\u00a0 [ SUCCESS | FAILURE | WARNING | &#8230; ]<\/p>\n<p>[ COMMIT | ROLLBACK ]<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<p><a name=\"SQL*Plus_file_commands\"><\/a><\/p>\n<p>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.<\/p>\n<p>Some SQL*Plus file commands are:<\/p>\n<ul type=\"disc\">\n<li>SAVE filename. This allows you to save buffer contents into a file.<\/li>\n<li>START filename. This allows you to execute a batch of SQL statements stored in a file.<\/li>\n<li>SPOOL filename. This allows you save SQL statements together with their outputs to a file.<\/li>\n<li> GET filename. This retrieve a file and places it into the buffer.<\/li>\n<li>@ filename. This allows you to execute a PL\/SQL procedure(s) stored in a file.<\/li>\n<\/ul>\n<h2><a name=\"SQL*Plus_edit_commands\"><\/a><\/h2>\n<p>Recall that the previously executed commands (in current SQL*Plus session) are stored in the local buffer. One way to change an SQL statement in the buffer is by using the<em> <strong>line editor<\/strong><\/em>. The following are a list of line edit commands.<\/p>\n<ul type=\"disc\">\n<li>LIST or L&#8211;Lists the contents of the buffer<\/li>\n<li>LIST n or L n&#8211;Lists the contents of line number n in the buffer and makes the line current<\/li>\n<li>LIST * or L *&#8211;Lists the current line<\/li>\n<li>LIST m n&#8211;Lists the range from m to n line<\/li>\n<li>Append text or A text&#8211;Adds to the end of the current line (e.g., &#8220;A ,&#8221; adds a comma to the end of line<\/li>\n<li>INPUT or I&#8211;Adds one or more lines after the current line so you can begin adding the text.<\/li>\n<li>CHANGE \/text&#8211;Deletes text from the current line<\/li>\n<li>CHANGE \/oldtext\/newtext&#8211;Replaces oldtext with newtext in the current line<\/li>\n<li>DEL &#8212; Deletes the current line<\/li>\n<\/ul>\n<p>Besides line editor,\u00a0you can also use the <em>vi editor<\/em> if you are a fan of Unix editor!.<\/p>\n<p>To invoke the vi editor, type Edit at the SQL Prompt.\u00a0\u00a0 Multiple SQL commands can be typed in vi editor. End each SQL command (except the last one) with a semicolon. After exiting notepad, type Start to run all of the commands.<\/p>\n<h2><a name=\"run_batch_sql\"><\/a>Run SQL statements in a batch<\/h2>\n<p>To run SQL commands in a batch, you can put all your SQL commands into a text file and execute these commands in this file in SQL*PLUS.<\/p>\n<ul type=\"disc\">\n<li>Use your favorite editor to type in your SQL queries into a text file.<\/li>\n<\/ul>\n<p>For Example,<\/p>\n<p>$ more table.sql<br \/>\nDROP TABLE employee<br \/>\n\/<\/p>\n<p>commit<br \/>\n\/<\/p>\n<p>CREATE TABLE employee (<br \/>\nempno INTEGER NOT NULL,<br \/>\nname VARCHAR2(50) NOT NULL,<br \/>\nsal REAL NOT NULL,<br \/>\nprimary key (empno));<br \/>\n\/<\/p>\n<p>INSERT INTO employee VALUES (1, &#8216;Jack&#8217;, 6000);<br \/>\nINSERT INTO employee VALUES (2, &#8216;Tom&#8217;,\u00a0 6000);<br \/>\nINSERT INTO employee VALUES (3, &#8216;John&#8217;, 6000);<br \/>\nINSERT INTO employee VALUES (4, &#8216;Jane&#8217;, 6000);<br \/>\n\/<\/p>\n<p>UPDATE employee SET sal=500 WHERE name=&#8217;Jack&#8217;<br \/>\n\/<\/p>\n<p>CREATE INDEX test_index on employee(sal)<br \/>\n\/<br \/>\n$<\/p>\n<ul type=\"disc\">\n<li>Connect into SQL*Plus, and run the batch of commands. For example, assume that you name the SQL file as <em>table.sql<\/em>.<\/li>\n<\/ul>\n<p><tt>SQL&gt; START table.sql<\/tt>;<\/p>\n<h2><a name=\"output_to_file\"><\/a>Output results <a name=\"Output\"><\/a><\/h2>\n<ul type=\"disc\">\n<li>You can record your SQL command outputs to a file for output or editing purpose.<\/li>\n<\/ul>\n<p>SQL&gt; <tt>SPOOL <\/tt>&lt;your file name&gt;<\/p>\n<p>For example,<\/p>\n<p><tt>SQL&gt; SPOOL myoutput.out<\/tt><\/p>\n<p>All SQL commands and their outputs after this command are written into the file <tt>myoutput.out <\/tt>that by default is stored in the current working directory where you invoked SQL*Plus.<\/p>\n<ul type=\"disc\">\n<li>To end recording, use the following command: <tt> <\/tt><\/li>\n<\/ul>\n<p><tt>SQL&gt; SPOOL OFF<\/tt><\/p>\n<h2><a name=\"dual\"><\/a>DUAL and select the current time<\/h2>\n<p>DUAL is the dummy table, mostly used to view the results from functions and calculations. The built-in function <tt>SYSDATE<\/tt> returns a <tt>DATE<\/tt> value containing the current date and time on your system. (Note Oracle is a client-server architecture and SQL*Plus is the client. SYSDATE gives you the time of the Unix system which you telnet in. It may NOT be the time of Oracle server unless you telnet into the machine running Oracle server.)<\/p>\n<p>For example,<\/p>\n<pre>SQL&gt; SELECT TO_CHAR(<tt>SYSDATE<\/tt> , 'Dy DD-Mon-YYYY HH24:MI:SS') as \"Current Date\/Time\" FROM DUAL;;<\/pre>\n<p>Result:<\/p>\n<pre>Current Time<\/pre>\n<pre>------------------------------------------------------------------------<\/pre>\n<pre>Mon 15-July-2002 10:01:29<\/pre>\n<ul type=\"disc\">\n<li>TO_CHAR is a function to format a value.<\/li>\n<li><tt>DUAL<\/tt> is built-in relation in Oracle which serves as a dummy relation to put in the <tt>FROM<\/tt> clause when nothing else is appropriate. For example, try &#8220;SELECT 2+2 FROM DUAL;&#8221;<\/li>\n<li>To format a number attribute to a dollar format, use the <em>column &lt;attribute&gt; format &lt;format&gt;<\/em>:<\/li>\n<\/ul>\n<pre>\u00a0 \u00a0\u00a0\u00a0\u00a0 SQL&gt; COLUMN salary FORMAT $999,999<\/pre>\n<ul type=\"disc\">\n<li>To indicate the displayed width of a character string attribute, use the <em>column &lt;attribute&gt; format &lt;A&#8217;format&gt;<\/em>. For example, set the width of the name attribute to 8 characters.<\/li>\n<\/ul>\n<pre>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 SQL&gt; COLUMN name FORMAT A8<\/pre>\n<p>If a name is longer than 8 characters, the remaining is displayed at the second line (or several lines)<\/p>\n<ul type=\"disc\">\n<li>The <em>set<\/em> command can be used to change the default number of lines per page (14) and the number of characters per line (80).<\/li>\n<\/ul>\n<p>For example, to set the number of lines per page to 60, use the following command:<\/p>\n<pre>\u00a0\u00a0 SQL&gt; SET PAGESIZE 60<\/pre>\n<ul type=\"disc\">\n<li>All formatting remain active until they are cleared or reset or after you exit from SQL*Plus.<\/li>\n<\/ul>\n<pre>\u00a0\u00a0 SQL&gt; CLEAR COLUMN<\/pre>\n<ul type=\"disc\">\n<li>If you forget a specific SQL command you could enter<\/li>\n<\/ul>\n<p><tt> SQL&gt; HELP &lt;the SQL command&gt;;<\/tt><\/p>\n<p>You could also find out all commands by entering:<\/p>\n<p><tt> SQL&gt; HELP menu;<\/tt><\/p>\n<ul type=\"disc\">\n<li>Sometimes when you get something fuzzy, you\u00a0 can try the following<\/li>\n<\/ul>\n<p>SQL&gt; SET SERVEROUTPUT ON<br \/>\nSQL&gt; SET ARRAYSIZE 1<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Using SQL*Plus SQL*Plus is a command-line tool that provides access to the Oracle RDBMS. SQL*Plus enables you to: Enter SQL*Plus commands to configure the SQL*Plus environment Startup and shutdown an Oracle database Connect to an Oracle database Enter and execute SQL commands and PL\/SQL blocks Format and print query results SQL*Plus is available on several [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[31,4,8,10,11,13],"tags":[54,72,196,107,111,119,128,132,135,140],"class_list":["post-174","post","type-post","status-publish","format-standard","hentry","category-database-programming","category-oracle","category-database","category-oracle-dba","category-oracle-developers","category-oracle-plsql","tag-commands","tag-guide","tag-oracle","tag-plsql","tag-quick-reference","tag-reference","tag-sql","tag-sqlplus","tag-syntax","tag-tutorial"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/174","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/comments?post=174"}],"version-history":[{"count":0,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/posts\/174\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/media?parent=174"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/categories?post=174"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.oratraining.com\/blog\/wp-json\/wp\/v2\/tags?post=174"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}