B Migrating from Server Manager to 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.

sqlplus /nolog

sqlplus hr/hr

sqlplus

Commands Introduced in SQL*Plus Release 8.1

Table B-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.

Commands Common to Server Manager and SQL*Plus

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

SQL*Plus Equivalents for Server Manager Commands

Table B-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.

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.

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.

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

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

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

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

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

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

# This statement returns only clerks.
SELECT * FROM hr.employees
    WHERE job_id LIKE '%CLERK';

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

Rem ------------------------------------------------------------------------- 
SELECT employee_id, first_name, last_name, job_id
    FROM hr.employees;

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

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

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

SELECT a + b -  
    c FROM xx;  

SELECT a + b c FROM xx;

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

SELECT a + b - c FROM xx; 

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

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:

SET ESCAPE \

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

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
/