2.6 Changes on the Command Line
| The CHANGE command is very simple and often simpler and less time-consuming than using a mouse cut-and-paste. The actual command is CHANGE but can be abbreviated with the character "C." Any text editor change command requires a field terminator and, in this case, the terminating character is a forward slash ”by default it is a forward slash but any character can be used, as we will illustrate . The CHANGE command syntax is: C[HANGE]/old_text/new_text[/] Use the following to just remove text: C[HANGE]/old_text In this and subsequent sections, a STUDENTS table is used to demonstrate SQL*Plus. A STUDENTS table can be created using the SQL provided in Chapter 1, Section 1.6, "Behind Tables." A helpful SQL*Plus command is the DESCRIBE command (DESC), which describes a table. The DESCRIBE command, first of all, is a quick way to see if a table exists. You get a harmless error if you attempt to describe a nonexistent table. If the table does exist, the command displays each column name , column type, plus a "Null" indicator to tell you if the column is a mandatory column ”mandatory column constraints are discussed in Chapter 3. The following session text starts with a DESCRIBE command and follows with some INSERT and SQL*Plus CHANGE commands. Editorial comments are in italics and underlined. Each underlined comment precedes what that comment is describing. SQL> desc students Name Null? Type --------------------- -------- ------------ STUDENT_ID VARCHAR2(10) STUDENT_NAME VARCHAR2(30) COLLEGE_MAJOR VARCHAR2(20) STATUS VARCHAR2(20) Select table row count . SQL> SELECT COUNT (*) FROM students; COUNT(*) ---------- Insert a row. SQL> INSERT INTO students VALUES 2 ('A101', 'John', 'Biology', 'Degree'); 1 row created. We just inserted one row for the student John. We can type another INSERT statement, but for this exercise, we'll CHANGE the SQL statement in the SQL*Plus buffer. When we complete our CHANGE, we will LIST the contents of the SQL*Plus buffer and execute (using the forward slash) the SQL statement. Change STUDENT_ID. SQL> c/101/102/ 2* ('A102', 'John', 'Biology', 'Degree') Change "John" to "Mary". SQL> c/John/Mary 2* ('A102', 'Mary', 'Biology', 'Degree') For field separator use "." Change major. SQL> c.Biology.Math/Science. 2* ('A102', 'Mary', 'Math/Science', 'Degree') LIST SQL*Plus buffer (lower case L). SQL> l 1 insert into students values 2* ('A102', 'Mary', 'Math/Science', 'Degree') Execute statement, then select all rows. SQL> / 1 row created. SQL> SELECT * FROM students; STUDENT_ID STUDENT_NA COLLEGE_MAJOR STATUS ---------- ---------- -------------------- ------ A101 John Biology Degree A102 Mary Math/Science Degree SQL> This SQL*Plus session introduces a few topics in addition to the basic CHANGE command.
A common typing error is to transpose the "R" and "F" (they are adjacent on the keyboard) when typing the "FROM" keyword of a SELECT statement. For example, SQL> SELECT * FORM students; SELECT * FORM students * ERROR at line 1: ORA-00923: FROM keyword not found where expected Make correction. We do not need a forward slash at the end. SQL> c/FORM/FROM 1* SELECT * FROM students SQL> / STUDENT_ID STUDENT_NA COLLEGE_MAJOR STATUS ---------- ---------- -------------------- ------ A101 John Biology Degree A102 Mary Math/Science Degree The following is a summary of common SQL*Plus editing commands discussed so far.
Two forthcoming points on the CHANGE command:
You remove text by not typing a NEW_TEXT clause. First, the CHANGE command definition with this consideration (notice NEW_TEXT is wholly in brackets) makes it optional. C[HANGE]/old_text [ /new_text[/] ] Because the entire NEW_TEXT clause is optional, the OLD_TEXT is removed when there is no NEW_TEXT. For example: SQL> SELECT student_id, student_name FROM students; STUDENT_ID STUDENT_NAME ---------- ------------ A101 John A102 Mary Use change command to drop "student_id," then execute. SQL> c/student_id, 1* select student_name from students SQL> / STUDENT_NAME ------------ John Mary CHANGE can be used to remove additional characters when we mistype. The following removes an extra comma. We have an extra comma after "student_name." SQL> SELECT student_name, FROM students; SELECT student_name, FROM students * ERROR at line 1: ORA-00936: missing expression Make the correction and resubmit the query. SQL> c/, 1* select student_name from students SQL> / STUDENT_NAME -------------------- John Mary Multiline editing is accomplished by first identifying the line number you intend to edit. The CREATE TABLE command, in the beginning of Section 1.8, is a CREATE TABLE statement that was typed using five lines. Entering carriage returns as we type is done to make it easier to read the statement we are typing. Lengthy, multiline SQL statements can easily be saved in a SQL script file for later use. If you're comfortable with SQL and you type well, you may often find the need to type a lengthy SQL statement to quickly get to some information that is of immediate need; in other words, you're in a big hurry. The following example is a scenario of correcting a multiline SQL statement against a data dictionary view. "column_type" should be "data_type," we expect an error. SQL> SELECT column_name, column_type 2 FROM user_tab_columns 3 WHERE table_name='STUDENTS'; SELECT column_name, column_type * ERROR at line 1: ORA-00904: invalid column name Chapter 1, Section 1.8, introduced the Oracle data dictionary that stores information about the tables we create. It also stores a great deal of information besides tables, such as privileges that users have. We have created a table called STUDENTS. We can query the data dictionary view USER_TAB_COLUMNS, as in Section 1.8, to see what columns are defined in the data dictionary. Typing a multiline SQL statement in SQL*Plus always displays a next -line number prompt. Earlier, SQL*Plus displayed the "2" before "FROM," and displayed "3" before "WHERE." The aforementioned SQL statement needs a correction. We made a mistake with COLUMN_TYPE. We should have checked the definition of the view USER_TAB_COLUMNS with a DESCRIBE command. If we do this now we see that the column is not COLUMN_TYPE but DATA_TYPE. SQL> desc user_tab_columns Name Null? Type -------------- -------------- -------- ------------- TABLE_NAME NOT NULL VARCHAR2(30) COLUMN_NAME NOT NULL VARCHAR2(30) DATA_TYPE VARCHAR2(106) etc . . . . . . . . USER_STATS VARCHAR2(3) AVG_COL_LEN NUMBER The SQL statement is still in the SQL*Plus buffer. Go to the first line ”that is where the error is ”and CHANGE the text. At the SQL*Plus prompt, we type the character for the numeral "1." SQL*Plus responds with the text from the first line. The text typed is the numeral one, not letter "L." SQL> 1 1* SELECT column_name, column_type Change command to make correction. SQL> c/column_type/data_type 1* select column_name, data_type List SQL*Plus buffer, then execute the SQL statement. SQL> l 1 SELECT column_name, data_type 2 FROM user_tab_columns 3* WHERE table_name='STUDENTS' SQL> / COLUMN_NAME DATA_TYPE ------------------------------ --------- STUDENT_ID VARCHAR2 STUDENT_NAME VARCHAR2 COLLEGE_MAJOR VARCHAR2 STATUS VARCHAR2 What about inserting additional lines of text? For example, we see earlier, from the DESCRIBE on USER_TAB_COLUMNS, there is a column called AVG_COL_LEN. This looks interesting. Modify the query to include this column as well. Use the SQL*Plus INSERT (just use the first character "I") command. We want to insert our additional text after line 1. We can accomplish this by typing a one ("1"), then "I" followed by the new text ”the change is complete. Type LIST (L) command. SQL> l 1 SELECT column_name, data_type 2 FROM user_tab_columns 3* WHERE table_name='STUDENTS' Type numeral one. SQL> 1 1* SELECT column_name, data_type INSERT ("I"), new text. SQL> i ,avg_col_len Type LIST (L) command then execute with "/". SQL> l 1 SELECT column_name, data_type 2 ,avg_col_len 3 FROM user_tab_columns 4* WHERE table_name='STUDENTS' SQL> / COLUMN_NAME DATA_TYPE AVG_COL_LEN ------------------------------ ---------- ----------- STUDENT_ID VARCHAR2 4 STUDENT_NAME VARCHAR2 4 COLLEGE_MAJOR VARCHAR2 10 STATUS VARCHAR2 6 SQL> Does your query show no numbers under the AVG_COL_LEN? This column shows the average column length and is a vital statistic that is used by the Oracle Cost Based Optimizer. If you have no statistics, you can generate the statistics for your STUDENTS table with the following command and rerun your query ”this time you'll see the statistics. One method for gathering statistics on the STUDENTS table is with the analyze command (the PL/SQL package, DBMS_STATS, is a more robust form of gathering statistics but ANALYZE, in this case, is a straightforward solution.) SQL> ANALYZE TABLE students COMPUTE STATISTICS; Table analyzed. SQL> Now run the aforementioned query and see values in the AVG_COL_LEN column. We added one line of text and inserted the text between lines 1 and 2. Suppose you want to add several lines. For this just the INSERT ("I") command and then Enter/Return ”there is no additional text. You are then in a form of "input mode." You can type as many additional lines as you like. Typing Enter/Return twice takes you out of "input mode." In general, when you have multiple lines to change or add, you save time when you save the SQL statement to a script file, edit that file, and run it from SQL*Plus. Following are two final topics about editing in SQL*Plus.
DEL is the command to delete a line. The previous SQL*Plus scenario inserted, after line 1, the text: ", AVG_COL_LEN." This became Line 2. Suppose we now want to delete this line. Set the current line at 2, then type DEL. Type LIST (L) command. SQL> l 1 SELECT column_name, data_type 2 ,avg_col_len 3 FROM user_tab_columns 4* WHERE table_name='STUDENTS' Go to line 2. SQL> 2 2* ,avg_col_len Delete this line. SQL> del Type LIST (L) showing deletion. SQL> l 1 SELECT column_name, data_type 2 FROM user_tab_columns 3* WHERE table_name='STUDENTS' SQL> When you set a "current line" by typing a numeral followed by enter/return, the text of that line is refreshed by SQL*Plus. You can then edit that line with a CHANGE or APPEND command. When you LIST the SQL*Plus buffer, the "current line" is always reset to the last line. So a LIST followed by a CHANGE command will always attempt to apply your change to the last line of the SQL*Plus buffer. The APPEND command is very useful. Sometimes you type a SELECT statement; then you want to qualify it by appending a WHERE clause. The following query, as first written, selects column names from the USER_TAB_COLUMNS. SQL> SELECT table_name, column_name 2 FROM user_tab_columns; TABLE_NAME COLUMN_NAME ------------------------ - ----------- STUDENTS STUDENT_ID STUDENTS STUDENT_NAME STUDENTS COLLEGE_MAJOR STUDENTS STATUS Let's modify this to see just those columns that are of type VARCHAR2. We simply use the APPEND command (abbreviate with "AP") to append a WHERE clause. We are appending text to the clause: "FROM USER_TAB_COLUMNS." This requires a space plus an appended WHERE clause. The append command, which is "AP," is separated from the appended text with a space. You need two spaces ”the second space is the first character of the text you are appending. The append command is: Two spaces after "ap", then submit SQL query. SQL> ap where data_type = 'VARCHAR2' 2* FROM user_tab_columns where data_type='VARCHAR2' SQL> / TABLE_NAME COLUMN_NAME ------------------------------ ------------- STUDENTS STUDENT_ID STUDENTS STUDENT_NAME STUDENTS COLLEGE_MAJOR STUDENTS STATUS
The aforementioned example appended a WHERE clause and we had to begin with a space to detach the text with a space. You can sometimes just leave off a character when typing ”in which case you simply append characters to the end of a line. The following statement, as first entered, is missing the "S" from the STUDENTS table ”sometimes we just cannot remember if a table name is singular or plural. Left off the "s". SQL> SELECT * FROM student; SELECT * FROM student * ERROR at line 1: ORA-00942: table or view does not exist "ap"<space>"s" SQL> ap s 1* SELECT * FROM STUDENTS SQL> / STUDENT_ID STUDENT COLLEGE_MAJOR STATUS ---------- ------- -------------------- ------ A101 John Biology Degree A102 Mary Math/Science Degree SQL> You can enter a SQL statement into the SQL*Plus buffer without executing it, then list it, then execute it. This is not the normal sequence of events, but is sometimes used as a technique within SQL*Plus scripts where it is desirable to have the script listed, in a spool file, and then force the execution of that statement; that is, list first, then execute. You do this by not entering a semicolon and typing ENTER twice ”this effectively ends the "input" mode. Notice the following has no semicolon at the end of line one. Type statement, ENTER, LIST (L), then submit query (/). SQL> SELECT * FROM students 2 SQL> l 1* SELECT * FROM STUDENTS SQL> / STUDENT_ID STUDENT COLLEGE_MAJOR STATUS ---------- ------- -------------------- ------ A101 John Biology Degree A102 Mary Math/Science Degree The preceding SQL*Plus session enters a SQL statement without a final semicolon. When you type the first enter/return, SQL*Plus responds with a prompt for a second line of SQL text. SQL*Plus interprets the second enter/return as "we're done" and returns the SQL*Plus prompt. The SQL statement is entered into the SQL*Plus buffer but not executed. We can list the buffer and then execute it. This scenario can be implemented in a SQL*Plus script by having a SQL statement without a semicolon followed by a blank line followed by a LIST, then a forward slash execute command. To summarize SQL*Plus editing, the two most helpful editing commands are:
We have also looked at
Finally, to execute the SQL statement that is currently in your SQL*Plus buffer, you type a forward slash ("/"). One variation on the forward slash is the RUN command. The RUN command first lists the contents of the SQL*Plus buffer and then executes the statement. It is identical to the sequence: LIST followed by a forward slash.  |
EAN: 2147483647
Pages: 111