Oracle8i Migration
Release 3 (8.1.7)

Part Number A86632-01

Library

Product

Contents

Index

Go to previous page Go to next page

11
Migrating from Server Manager to SQL*Plus

Oracle Corporation recommends scripting in SQL*Plus instead of Server Manager line mode. If you run SQL scripts using Server Manager line mode, then you should change these scripts so that they are compatible with SQL*Plus, and then run them using SQL*Plus. This chapter guides you through changing your Server Manager line mode scripts to work with SQL*Plus.

This chapter covers the following topics:

Startup Differences

The methods for starting Server Manager and SQL*Plus are different, and your SQL scripts must be modified to start SQL*Plus properly. The following sections explain the startup differences and provide options for starting SQL*Plus.

Starting Server Manager

To start Server Manager, enter the name of the Server Manager program at a system prompt; the name of this program is operating system-specific. After you start up Server Manager, connect using the CONNECT command, as in the following example:

CONNECT scott/tiger

Starting SQL*Plus

The following sections describe various ways to start SQL*Plus.

Starting SQL*Plus with the NOLOG Option

If you want SQL*Plus to behave in the same way as Server Manager, then use the NOLOG option when you start SQL*Plus, as in the following example:

sqlplus /nolog

SQL*Plus starts and you can use the CONNECT command to connect as a user.

Starting SQL*Plus with Connect Information

Another option for starting SQL*Plus is to enter the connect information when you start the program. For example, to start SQL*Plus and connect as SCOTT/TIGER, enter the following:

sqlplus scott/tiger

SQL*Plus starts and connects as user SCOTT.

Starting SQL*Plus without Options or Connect Information

To start SQL*Plus without options or connect information, enter the following:

sqlplus

SQL*Plus prompts you for a user name and password. When you enter a valid user name and password, SQL*Plus starts and connects as the user you specified at the prompts. In your SQL scripts, however, you may not want to prompt the user to enter a user name and password.

Commands

Server Manager and SQL*Plus share certain commands that behave the same in both programs. Other commands, however, behave differently in SQL*Plus than they do in Server Manager. To successfully migrate from Server Manager to SQL*Plus, you need to understand these differences and similarities. The following sections include information about modifying your SQL scripts to use commands that are interpreted correctly by SQL*Plus.

New SQL*Plus Release 8.1 Commands

Table 11-1 lists Server Manager commands that are now available in SQL*Plus. You can use these commands in SQL scripts that you run with SQL*Plus release 8.1.


Note:

On databases prior to release 8.1 (such as Oracle7 and release 8.0), use Server Manager to run scripts containing these commands. Versions of SQL*Plus before SQL*Plus release 8.1 will not run scripts containing these new commands. 


Table 11-1 New SQL*Plus Commands in Release 8.1
Command  Description 

ARCHIVE LOG 

Starts or stops automatic archiving of online redo log files, manually (explicitly) archives specified redo log files, or displays information about archives. 

RECOVER 

Performs media recovery on one or more tablespaces, one or more datafiles, or the entire database. 

SET AUTORECOVERY 

ON causes the RECOVER command to automatically apply the default filenames of archived redo log files needed during recovery. No interaction is needed when AUTORECOVERY is set to ON, provided the necessary files are in the expected locations with the expected names. 

SET INSTANCE 

Changes the default instance for your session to the specified instance path. Does not connect to a database. The default instance is used for commands when no instance is specified. 

SET LOGSOURCE 

Specifies the location from which archive logs are retrieved during recovery. The default value is set by the LOG_ARCHIVE_DEST initialization parameter. Issuing the SET LOGSOURCE command without a pathname restores the default location. 

SHOW AUTORECOVERY  

Shows whether autorecovery is enabled.  

SHOW INSTANCE 

Shows the connect string for the default instance. SHOW INSTANCE returns the value LOCAL if you have not used SET INSTANCE or if you have used the LOCAL option of the SET INSTANCE command. 

SHOW LOGSOURCE 

Shows the current setting for the archive log location. Displays DEFAULT if the default setting is in effect, as specified by the LOG_ARCHIVE_DEST initialization parameter. 

SHOW PARAMETERS 

Displays the current values for one or more initialization parameters. The SHOW PARAMETERS command, without any string following the command, displays all initialization parameters. 

SHOW SGA  

Displays information about the current instance's System Global Area.  

SHUTDOWN 

Shuts down a currently running Oracle instance, optionally closing and dismounting a database.

Note: The STARTUP and SHUTDOWN commands in SQL*Plus release 8.1 are not supported against an Oracle7 server.  

STARTUP 

Starts an Oracle instance with several options, including mounting and opening a database.

Note: The STARTUP and SHUTDOWN commands in SQL*Plus release 8.1 are not supported against an Oracle7 server.  

Commands Common to Server Manager and SQL*Plus

The commands listed in Table 11-2 are available in both Server Manager and SQL*Plus, and have been available in both programs in past releases of Oracle. You do not need to alter these commands in your SQL scripts to use SQL*Plus.


Note:

There may be minor formatting differences in the output for these commands in the two programs. 


Table 11-2 Server Manager Commands Corresponding to Existing SQL*Plus Commands
Command  Description 

CONNECT 

Connects to a database using the specified user name. 

DESCRIBE 

Describes a function, package, package body, procedure, table, or view. For example, for a table, displays the definitions of each column in the table.  

REMARK 

Enters a comment, typically in SQL script files. 

SET COMPATIBILITY 

Sets compatibility mode to V7, V8, or NATIVE. The compatibility mode setting affects the specification of character columns, integrity constraints, and rollback segment storage parameters. NATIVE matches the version of the database. 

SET ECHO 

Controls whether the START command lists each command in a command file as the command is executed. ON lists the commands; OFF suppresses the listing. 

SET NUMWIDTH 

Sets the default width for displaying numbers. 

SET SERVEROUTPUT 

Controls whether to display the output (that is, DBMS_OUTPUT.PUT_LINE) of stored procedures or PL/SQL blocks in SQL*Plus. OFF suppresses the output of DBMS_OUTPUT.PUT_LINE; ON displays the output. 

SET TERMOUT 

Controls the display of output generated by commands executed from a command file. OFF suppresses the display so that you can spool output from a command file without seeing the output on the screen. ON displays the output. 

SHOW ALL 

Lists all of the system variables set by the SET command in alphabetical order, except ERRORS, PARAMETERS, and SGA. 

SHOW ERRORS 

Shows the errors generated from the last compilation of a procedure, package, or function, if any. 

SPOOL 

Stores query results in an operating system file and, optionally in SQL*Plus, sends the file to a printer.

Note: The extension of spool files may differ between SQL*Plus and Server Manager. To ensure an extension, specify it when you issue the SPOOL command. Also, SQL*Plus may format white space in terminal output using tab characters in place of repeated blanks. Use SET TAB OFF in SQL*Plus to prevent this replacement. Tabs are never output by Server Manager.  

SQL*Plus Equivalents for Server Manager Commands

Table 11-3 lists the SQL*Plus commands that correspond to Server Manager commands with different names. If you are using any of these Server Manager commands in SQL scripts, then modify the scripts to use the SQL*Plus commands instead.

Table 11-3 SQL*Plus Equivalents for Server Manager Commands
Server Manager Commands  SQL*Plus Commands  Description 

SET CHARWIDTH

SET DATEWIDTH

SET LONGWIDTH 

COLUMN FORMAT 

You can use the COLUMN FORMAT command in SQL*Plus to set the column width of character columns, date columns, and number columns. In your SQL scripts, replace the SET CHARWIDTH, SET DATEWIDTH, and SET LONGWIDTH Server Manager commands with the SQL*Plus command COLUMN FORMAT.

Use COLUMN FORMAT for all character columns to be changed. There is no equivalent command to change all character columns with one command.

For example, suppose you have the following entry in a SQL script:

SET CHARWIDTH 5

This command sets the width for all character columns to 5 in Server Manager.

To specify that a particular column, such as ENAME, display with a width of 5 characters, enter the following SQL*Plus command:

COLUMN ENAME FORMAT A5

Use COLUMN FORMAT for all character columns to be changed. There is no equivalent command to change all character columns with one command.

Use COLUMN FORMAT for all date columns to be changed. There is no equivalent command to change all date columns with one command.

Use SET LONG to specify how much of the LONG column to fetch and display. 

SET STOPONERROR 

WHENEVER SQLERROR

WHENEVER OSERROR 

Use the WHENEVER SQLERROR and WHENEVER OSERROR commands to direct SQL*Plus to either exit or continue whenever a SQL error or operating system error occurs. Use these commands in your SQL scripts instead of the Server Manager command SET STOPONERROR.

For both WHENEVER SQLERROR and WHENEVER OSERROR, the EXIT clause directs SQL*Plus to exit, while the CONTINUE clause directs SQL*Plus to continue. Other terms and clauses are also available for these commands. 

Possible Differences in the SET TIMING Command

The SET TIMING command is available in both Server Manager and SQL*Plus, but this command may function differently in the two programs on some operating systems. Check your operating system-specific Oracle documentation for more information. If the SET TIMING command functions differently in these two programs on your operating system, then modify your SQL scripts so that this command functions properly with SQL*Plus.

Server Manager Commands Unavailable in SQL*Plus

The following Server Manager commands are unavailable in SQL*Plus release 8.1:

Remove these commands from your SQL scripts.

Syntax Differences

The following sections explain the syntax differences between Server Manager and SQL*Plus. Modify your SQL scripts to conform with SQL*Plus syntax conventions before you attempt to run your scripts using SQL*Plus.

Comments

SQL*Plus recognizes the following types of comments:

The SQL*Plus User's Guide and Reference provides detailed information about using these types of comments in SQL*Plus code.

Server Manager supports the these types of comments, but the behavior is different for some of them. Also, certain types of comments are available in Server Manager, but not in SQL*Plus. The sections below discuss each type of comment and the syntax differences between Server Manager and SQL*Plus.

REMARK Command (or REM)

In general, the REMARK command works the same in Server Manager and SQL*Plus, and you do not need to change the occurrences of the REMARK command in your SQL scripts. There is, however, one difference: SQL*Plus interprets a hyphen that terminates a REMARK command differently than Server Manager. See "Hyphens Used as Dividing Lines" for information about this difference.

SQL Comment Delimiters, /*...*/

In Server Manager, the SQL comment delimiters can be placed after a semi-colon, but in SQL*Plus, placing a SQL comment delimiter after a semi-colon is not allowed. Except for this one difference, SQL comment delimiters work the same in Server Manager and SQL*Plus.

If your SQL scripts contain any SQL comment delimiters placed after a semi-colon, then either move the comment to its own line, or remove the semi-colon and place a slash (/) on the next line to end the SQL statement.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

SELECT * FROM scott.emp
    WHERE job = 'CLERK'; /* Includes only clerks. */

In SQL*Plus, replace this code with either of the following entries:

SELECT * FROM scott.emp
    WHERE job = 'CLERK';
/* Includes only clerks. */

SELECT * FROM scott.emp
    WHERE job = 'CLERK' /* Includes only clerks. */
    /

ANSI/ISO Comments, --

In Server Manager, the ANSI/ISO comments can be placed after a semi-colon, but in SQL*Plus, placing an ANSI/ISO comment after a semi-colon is not allowed. Except for this one difference, ANSI/ISO comments work the same in Server Manager and SQL*Plus.

If your SQL scripts contain any ANSI/ISO comments that are placed after a semi-colon, then either move the comment to its own line, or remove the semi-colon and place a slash (/) on the next line to end the SQL statement.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

SELECT * FROM scott.emp
    WHERE job = 'CLERK'; -- Includes only clerks.

In SQL*Plus, replace this code with either of the following entries:

SELECT * FROM scott.emp
    WHERE job = 'CLERK';
-- Includes only clerks.

SELECT * FROM scott.emp
    WHERE job = 'CLERK' -- Includes only clerks. 
    /

Server Manager Pound (#) Comments

Server Manager supports the use of the pound sign (#) to indicate a comment line. If your scripts contain these comments, then change the ' # ' to ' - - ' to run the scripts using SQL*Plus.

For example, suppose you have the following Server Manager code in one of your SQL scripts:

# This statement returns only clerks.
SELECT * FROM scott.emp
    WHERE job = 'CLERK';

In SQL*Plus, replace this code with the following entry:

-- This statement returns only clerks.
SELECT * FROM scott.emp
    WHERE job = 'CLERK';

Blank Lines

Server Manager ignores blank lines within SQL statements, but when SQL*Plus encounters a blank line the default behavior is to stop recording the statement and return to the prompt.

Both products allow blank lines between distinct SQL statements. This section only applies to blank lines between clauses of SQL statements.

In SQL*Plus, the SET SQLBLANKLINES command alters the way blank lines are handled. When SQLBLANKLINES is set to OFF, the default setting, and there is a SQL statement containing a blank line, SQL*Plus buffers the statement at the blank line, returning to the prompt without executing the statement. This behavior allows interactive users to abort and buffer an unwanted SQL command, or to perform other SQL*Plus commands before executing or editing this buffered SQL command.

If any of your SQL scripts contain blank lines within SQL statements, then either set SQLBLANKLINES to ON, or remove the blank lines before you run these scripts using SQL*Plus.

For example, suppose you have the following SQL statement in one of your SQL scripts:

SELECT empno, ename, sal, comm

    FROM scott.emp

    WHERE job = 'MANAGER';

Either set SQLBLANKLINES to ON, or delete the blank lines:

SELECT empno, ename, sal, comm
    FROM scott.emp
    WHERE job = 'MANAGER';

If you do not remove the blank lines or set SQLBLANKLINES to ON, then SQL*Plus will treat each blank line of code as a command terminator.

The value of SQLBLANKLINES does not affect blank lines in PL/SQL blocks. These are always treated as part of the block and do not return to the SQL*Plus prompt.

Interactive users can terminate SQL or PL/SQL statements by entering a period on a line by itself, regardless of the value of SQLBLANKLINES.

The Hyphen Continuation Character

SQL*Plus supports the use of a hyphen as a continuation character for long SQL statements or SQL*Plus commands. For example, you can use the continuation character in the following way:

SELECT empno, ename, sal, comm FROM scott.emp -
WHERE job = 'MANAGER';

Server Manager does not support the use of a hyphen as a continuation character, but you may use hyphens for other purposes in your SQL scripts. If you do, then SQL*Plus may interpret a hyphen as a continuation character, which can cause unexpected output.

The following sections provide scenarios in which SQL*Plus interprets the use of hyphens in SQL scripts as continuation characters, when the hyphens were meant for another purpose. Check your SQL scripts for the use of hyphens and modify them to avoid scenarios similar to those described below.

Hyphens Used as Dividing Lines

Your SQL scripts may use a long row of hyphens following a REMARK command as a dividing line in the code. Consider the following sample lines from a SQL script:

Rem ------------------------------------------------------------------------- 
SELECT empno, ename, job
    FROM scott.emp;

In this statement, SQL*Plus interprets the first line of the SELECT statement as a continuation of the previous line, which is a REMARK comment. Therefore, the FROM line is interpreted as the first line of a SQL statement, and SQL*Plus returns the following error:

unknown command beginning "FROM scott..." - rest of line ignored. 

If you use hyphens as dividing lines in your SQL scripts, then remove the REM command preceding the hyphens before you run the scripts using SQL*Plus.

Hyphens Used as Minus Signs

Because the hyphen is the same keyboard character as the minus sign, you may have a hyphen at the end of a line. Consider the following sample lines from a SQL script:

CREATE TABLE xx (  
    a int,  
    b int,  
    c int);  

INSERT INTO xx VALUES (10, 20, 30);

SELECT a + b -  
    c FROM xx;  

SQL*Plus interprets the 'c' as an alias because the minus symbol is interpreted as a continuation character:

SELECT a + b c FROM xx;  

Therefore, SQL*Plus returns the following unexpected output:

         C
----------
        30

Server Manager, however, interprets this code as the following:

SELECT a + b - c FROM xx; 

Therefore, Server Manager returns the following expected output:

A+B-C     
----------
         0

Make sure you do not have a minus sign at the end of a line in your SQL scripts.

Ampersands

SQL*Plus interprets an ampersand (&) as a substitution variable, whereas Server Manager interprets an ampersand as a normal string. If the text following the ampersand does not have a defined value, then SQL*Plus interprets it as an undefined value and prompts the user for input, even if the ampersand is enclosed in a comment. Therefore, ampersands can cause unexpected output in SQL*Plus.

If you have SQL scripts that use ampersands as normal text strings, then you have two options:

For example, the following SQL statement prompts the user for input in SQL*Plus:

CREATE TABLE "Employees & Managers" (
    Employees varchar(16), 
    Managers varchar(16));

Enter value for managers:

Using the SET ESCAPE Command

To avoid the user prompt, you can use the SET ESCAPE command to set an escape character. Then, place the escape character before the ampersand. A backslash (\) is often used as an escape character.

To avoid the prompt in the example preceding example by using the SET ESCAPE command, change the entry to the following:

SET ESCAPE \

CREATE TABLE "Employees \& Managers" (
    Employees varchar(16), 
    Managers varchar(16));

Using the SET DEFINE OFF Command

To avoid the prompt in the preceding example by using the SET DEFINE OFF command, change the entry to the following:

SET DEFINE OFF

CREATE TABLE "Employees & Managers" (
    Employees varchar(16), 
    Managers varchar(16));

CREATE TYPE and CREATE LIBRARY Commands

SQL*Plus treats the CREATE TYPE and CREATE LIBRARY commands as PL/SQL blocks. Therefore, in SQL*Plus, you must use a slash (/) on a separate line to end these commands, while Server Manager allows you to end these commands with a semi-colon.

If you end any CREATE TYPE or CREATE LIBRARY command with a semi-colon in your SQL scripts, then remove the semi-colon and place a slash on the next line. For example, the following SQL statements are not recognized by SQL*Plus:

CREATE OR REPLACE TYPE sys.dummy AS OBJECT (data CHAR(1));
CREATE OR REPLACE LIBRARY DBMS_SPACE_ADMIN_LIB TRUSTED AS STATIC;

Edit these statements in the following way before you run them with SQL*Plus:

CREATE OR REPLACE TYPE sys.aq$_dummy_t AS OBJECT (data CHAR(1))
/
CREATE OR REPLACE LIBRARY DBMS_SPACE_ADMIN_LIB TRUSTED AS STATIC
/

COMMIT Command

SQL*Plus requires that the COMMIT command be terminated either with a semi-colon (;) or a slash (/), but Server Manager allows the COMMIT command with no terminator. Therefore, if you use the COMMIT command in your SQL scripts without a terminator, then edit these scripts to include a terminator.

For example, suppose you have the following COMMIT command in a SQL script:

commit

Include a terminator for the command, as shown in either of the following examples:

commit;

commit
/ 


Go to previous page Go to next page
Oracle
Copyright © 1996-2000, Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index