CODEWITHSUNDEEP
sqloracle-databasesqlplus
Vicky
Vicky

Reputation: 5540

Printing Error with sqlplus

Below you could see template of SQL script that i'm trying to execute on sqlplus:

WHENEVER SQLERROR EXIT 1 ROLLBACK
WHENEVER OSERROR EXIT 1 ROLLBACK

BEGIN
 -- Doing some Insert
END;
/

INSERT INTO TMP_TABLE VALUES('A', 'B')

COMMIT;
QUIT;
/

This script is available in a file (Test1.sql) on my local system and I am running this query by connecting to Oracle through Windows command line console. Typing below line on cmd.

C:\Users\myself> sqlplus UNAME/PWD@//DB IP:PORT/SID @C:\Users\Test1.sql

In SQL script I purposeful didn't terminated one statement (INSERT INTO...) with a semi colon (see above). Now when i run this script by following above instructions, program run successfully and prints a message:

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 6 16:50:19 2012

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options


PL/SQL procedure successfully completed.


Commit complete.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64
bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

When I run the same set of scripts on my SQL Navigator tool, I get below error and that is correct:

[10]: (Error): ORA-00933: SQL command not properly ended

I am not understanding why the script is not reporting this error and instead displaying message like 'Commit complete'.

Can you tell me how can i make my script throw this error on console when i'm trying to run the same through sdlplus using Windows command console?

Upvotes: 1

Views: 1331

Answers (1)

Glenn
Glenn

Reputation: 9150

My understanding is that in a sqlplus script, a blank line terminates a sql statement. I don't have an Oracle environment to test, but try removing the blank line between your insert and the commit statement and see if you then get an error. I would imagine that you would see it then (it would act as a concatenated insert..commit statement).

Take a look at executing commands section in the SQL Plus reference

Specifically:

Ending a SQL Command

You can end a SQL command in one of three ways:

  • with a semicolon (;)
  • with a slash (/) on a line by itself
  • with a blank line

Note: You can change the way blank lines appear and behave in SQL statements using the SET SQLBLANKLINES command (not in iSQL*Plus). For more information about changing blank line behavior, see the SET command.

As for SQLNavigator, different client tools will behave differently. The SQLNavigator interpreter does not behave the same as the sqlplus interpreter. If you call Oracle support with an issue with a script, the first thing they will ask is "do you see this error when running it in sqlplus?"

Upvotes: 3

Related Questions