10
Migrating from Server Manager to SQL*Plus

Server Manager line mode will be obsoleted in a future release of Oracle. If you run SQL scripts using Server Manager line mode, 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

• Commands

• Syntax Differences

  See Also: SQL*Plus User's Guide and Reference for detailed information about using SQL*Plus release 8.1.

  Note: For brevity, Server Manager line mode is referred to as Server Manager in the rest of this chapter.

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, 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 10-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.

Table 10-1 New SQL*Plus Commands in Release 8.1

Commands Common to Server Manager and SQL*Plus

The commands listed in Table 10-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.

Table 10-2 Server Manager Commands Corresponding to Existing SQL*Plus Commands

SQL*Plus Equivalents for Server Manager Commands

Table 10-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, modify the scripts to use the SQL*Plus commands instead.

Table 10-3 SQL*Plus Equivalents for Server Manager Commands

SET CHARWIDTH 5

COLUMN ENAME FORMAT A5

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, 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:

• SET MAXDATA

• SET RETRIES

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 REMARK command (or REM)

• the SQL comment delimiters, /*...*/

• the ANSI/ISO 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, 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, 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, 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 allows blank lines within SQL statements, but SQL*Plus does not. SQL*Plus buffers SQL statements at the first blank line, which enables you to easily rerun a particular SQL statement.

If any of your SQL scripts contain blank lines within SQL statements, 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';

In SQL*Plus, replace this code with the following SQL statement by deleting the blank lines:

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

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, 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, 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, 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, you have two options:

• Use the SET ESCAPE command to place an escape character before each ampersand.

• Use the SET DEFINE OFF command to disable the recognition of substitution variables.

  Note: Do not use the SET DEFINE OFF command if you have other, valid substitution variables; if you do, the other variables will not be recognized.

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, 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, 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
/