SP2-0734: unknown command beginning rest of line ignored

Problem Description
Running any script or SQL or PL/SQL fails with error SP2-0734 unknown command beginning "........" - rest of line ignored..
Following is some example generated while working with script, SQL or Pl/Sql.

Case 01:
Running an oracle script fails as,
SQL> @csminst.sql
SP2-0734: unknown command beginning "csminst.sq..." - rest of line ignored.

Case 02:
Running a procedure fails as,
SQL> my_proc();
SP2-0042: unknown command "my_proc()" - rest of line ignored.

Case 03:
Login to database using sql*plus even login to sql*plus fails with SP2-0042 as below.
$ sqlplus / nolog
SP2-0734: unknown command beginning "yyy..." rest of line ignored.

Case 04:
When trying to purge a table from the recyclebin that is issuing SQL fails with,
SQL> purge table t;
SQL> purge recyclebin;
"SP2-0734: unknown command beginning "purge tabl..." - rest of line ignored."


Solution of the Problem

Case 01 Solution
This is due to the display terminal keyboard configuration of the kill character. Because script is ok and unknown command shown the script name.

The problem is common with unix environment with the display terminal keyboard settings. The sqlplus session had trouble interpreting the "@" sign, because it was assigned in the terminal to the "kill" setting. The csminst.sql script was supposed to be run as "@ csminst.sql" and since the "@" sign had a completely different meaning for this OS session, sqlplus only saw "csminst.sql" and hence it throws error.

There is two solution exists for this type of scenario. One is to change the display terminal keyboard setting of the kill character to something else. For example:

# stty kill ^u

After making this change the script is interpreted correctly and runs as it should.

Another is, run the script using "start" keyword instead of "@" sign.

SQL> start csminst.sql

Case 02 Solution
This is happened because of incorrect way to execute a stored procedure. When calling a PL/SQL stored procedure from SQL*plus you must call it using the EXECUTE (or EXEC) command or via a PL/SQL BEGIN-END block.

Following is the correct examples.

1) EXEC my_proc ();

or

2) BEGIN
my_proc();
END;
/

Case 03 Solution
In this case any invalid entries in glogin.sql file causes this issue. The glogin.sql script gets executed when users invoke sqlplus, even with nolog option.

Check if there is any invalid commands defined in glogin.sql (which usually resides in $ORACLE_HOME/sqlplus/admin).

If not, then check if there is any issue with this file itself. Also rename the glogin.sql file to glogin_bak.sql and try invoking sqlplus again.

Case 04 Solution
This problem happened whenever you connect to the database 10g using SQL*Plus version 9i or lower. The purge command is new in rel 10g, so older versions of SQL*Plus do not recognize it.
To implement the solution, execute the following steps:

1. Connect to the database using SQL*Plus rel 10g, either by logging in directly to the server, or by running SQL*Plus from a 10g client installation.

2. Rerun the purge command. As long as you connect from a 10g version of SQL*Plus, the command will work.

Special Case
Note that, a common SP2-0734 problem happened during controlfile creation. After you have issued ALTER DATABASE BACKUP CONTROLFILE TO TRACE you have generated controlfile script. Whenever you run the script it fails as below.

SQL> CREATE CONTROLFILE REUSE DATABASE "A" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ARJU\ORADATA\A\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\APP\ARJU\ORADATA\A\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\APP\ARJU\ORADATA\A\REDO03.LOG'  SIZE 50M
-- STANDBY LOGFILE

DATAFILE
  'D:\APP\ARJU\ORADATA\A\SYSTEM01.DBF',
  'D:\APP\ARJU\ORADATA\A\SYSAUX01.DBF',
  'D:\APP\ARJU\ORADATA\A\UNDOTBS01.DBF',
  'D:\APP\ARJU\ORADATA\A\USERS01.DBF',
  'D:\APP\ARJU\PRODUCT\11.1.0\DB_1\DATABASE\DATA01.DBF',
  'F:\MIGRATE_TO_ASM.DBF'
CHARACTER SET WE8MSWIN1252
;

SP2-0042: unknown command "DATAFILE" - rest of line ignored.
SP2-0734: unknown command beginning "'D:\APP..." - rest of line ignored.

This happened due to blank line before the DATAFILE clause and after -- STANDBY LOGFILE , remove that space line as well as remove line -- STANDBY LOGFILE and re run the script, it should be fixed. Fixed one will look like,

SQL> CREATE CONTROLFILE REUSE DATABASE "A" NORESETLOGS  NOARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'D:\APP\ARJU\ORADATA\A\REDO01.LOG'  SIZE 50M,
  GROUP 2 'D:\APP\ARJU\ORADATA\A\REDO02.LOG'  SIZE 50M,
  GROUP 3 'D:\APP\ARJU\ORADATA\A\REDO03.LOG'  SIZE 50M
DATAFILE
  'D:\APP\ARJU\ORADATA\A\SYSTEM01.DBF',
  'D:\APP\ARJU\ORADATA\A\SYSAUX01.DBF',
  'D:\APP\ARJU\ORADATA\A\UNDOTBS01.DBF',
  'D:\APP\ARJU\ORADATA\A\USERS01.DBF',
  'D:\APP\ARJU\PRODUCT\11.1.0\DB_1\DATABASE\DATA01.DBF',
  'F:\MIGRATE_TO_ASM.DBF'
CHARACTER SET WE8MSWIN1252
;

Related Documents
http://arjudba.blogspot.com/2008/12/understanding-execution-plan-statistics.html
http://arjudba.blogspot.com/2008/11/formatting-sqlplus-reports.html
http://arjudba.blogspot.com/2008/12/controlling-autotrace-report-in-sqlplus.html
http://arjudba.blogspot.com/2008/12/formatting-sqlplus-reports-part-2.html
http://arjudba.blogspot.com/2008/11/use-of-bind-variables-in-sqlplus.html
http://arjudba.blogspot.com/2008/11/communicate-with-user-through-accept.html
http://arjudba.blogspot.com/2008/11/working-with-sqlplus-scripts.html
http://arjudba.blogspot.com/2008/11/sqlplus-basics-and-tips.html
http://arjudba.blogspot.com/2008/08/error-45-initializing-sqlplus-internal.html
http://arjudba.blogspot.com/2008/05/how-to-see-explain-plan-from-sqlplus.html
http://arjudba.blogspot.com/2008/05/change-prompt-in-sqlplus.html
http://arjudba.blogspot.com/2008/05/how-to-set-environmental-variable-to.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-host.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and.html
http://arjudba.blogspot.com/2008/05/what-is-difference-between-and-in.html
http://arjudba.blogspot.com/2008/05/how-can-one-pass-operating-system.html
http://arjudba.blogspot.com/2008/05/where-is-my-column-data-sqlplus-does.html
http://arjudba.blogspot.com/2008/05/automatic-recovery-during-applying-logs.html
http://arjudba.blogspot.com/2008/05/purpose-and-restriction-of-recover.html
http://arjudba.blogspot.com/2008/04/shutdown-modes-in-oracle.html