C
Migrating from Server Manager to SQL*Plus

This appendix guides you through the process of modifying your Server Manager line mode scripts to work with SQL*Plus. Server Manager is no longer supported in Oracle9i. If you run SQL scripts using Server Manager line mode, then you will need to change these scripts so that they are compatible with SQL*Plus, and then run them using SQL*Plus.

This appendix covers the following topics:

• Startup Differences
• Commands
• Syntax Differences

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

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

Startup Differences

The methods for starting Server Manager and SQL*Plus are different, and your SQL scripts must be modified to properly start SQL*Plus. 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 hr/hr

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 user hr with password hr, enter the following:

sqlplus hr/hr

SQL*Plus starts and connects as user hr.

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.

Commands Introduced in SQL*Plus Release 8.1

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

Table C-1  Commands Introduced in SQL*Plus Release 8.1

Commands Common to Server Manager and SQL*Plus

The commands listed in Table C-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 C-2  Server Manager Commands Corresponding to Existing SQL*Plus Commands

SQL*Plus Equivalents for Server Manager Commands

Table C-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 C-3  SQL*Plus Equivalents for Server Manager Commands

SET CHARWIDTH 5

COLUMN first_name 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, 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 and higher:

• 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 scripts.

Server Manager supports 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 semicolon (;), but in SQL*Plus, placing a SQL comment delimiter after a semicolon 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 semicolon , then either move the comment to its own line, or remove the semicolon 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 hr.employees
    WHERE job_id LIKE '%CLERK'; /* Includes only clerks. */

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

SELECT * FROM hr.employees
    WHERE job_id LIKE '%CLERK';
/* Includes only clerks. */

SELECT * FROM hr.employees
    WHERE job_id LIKE '%CLERK' /* Includes only clerks. */
    /

ANSI/ISO Comments, --

In Server Manager, the ANSI/ISO comments can be placed after a semicolon (;), but in SQL*Plus, placing an ANSI/ISO comment after a semicolon 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 semicolon, then either move the comment to its own line, or remove the semicolon 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 hr.employees
    WHERE job_id LIKE '%CLERK'; -- Includes only clerks.

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

SELECT * FROM hr.employees
    WHERE job_id LIKE '%CLERK';
-- Includes only clerks.

SELECT * FROM hr.employees
    WHERE job_id LIKE '%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 hr.employees
    WHERE job_id LIKE '%CLERK';

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

-- This statement returns only clerks.
SELECT * FROM hr.employees
    WHERE job_id LIKE '%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 employee_id, first_name, last_name, salary, commission_pct

    FROM hr.employees

    WHERE job_id LIKE '%MAN';

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

SELECT employee_id, first_name, last_name, salary, commission_pct
    FROM hr.employees
    WHERE job_id LIKE '%MAN';

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 employee_id, first_name, last_name FROM hr.employees -
WHERE job_id LIKE '%MAN';

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 employee_id, first_name, last_name, job_id
    FROM hr.employees;

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 hr..." - 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:

• 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, then 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 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 semicolon (;).

If you end any CREATE TYPE or CREATE LIBRARY command with a semicolon in your SQL scripts, then remove the semicolon 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 semicolon (;) 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
/