SQL*Plus does not execute SQL scripts that SQL Developer does

Question

I am facing a very annoying problem. I have written (in Notepad++) some SQL scripts. Now when I try to execute them by SQL*Plus (through the command line, on Windows 7), I am getting errors like ORA-00933: SQL command not properly ended.

Then I copy & paste the script into the SQL Developer worksheet window, hit the Run button, and the script executes without any problem/errors.

After a long investigation, I came to think that SQL*Plus has a problem with some whitespaces (including newline characters and tabs) that it does not understand.

Since I assume that SQL Developer knows how to get rid of the weird whitespaces, I have tried this: paste the script into the SQL Developer worksheet window, then copy it from there and paste it back in the SQL script. That solved the problem for some files, but not all the files. Some files keep showing errors in places for no apparent reason.

Have you ever had this problem? What should I do to be able to run these scripts by SQL*Plus through the command line?

UPDATE:

An example of a script that did not work with SQL*Plus but did work with SQL Developer:

SET ECHO ON;

INSERT INTO MYDB.BOOK_TYPE (
    BOOK_TYPE_ID, UNIQUE_NAME, DESCRIPTION, VERSION, IS_ACTIVE, DATE_CREATED, DATE_MODIFIED
)
SELECT MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, 'Book-Type-' || MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, 'Description-' || MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, A.VERSION, B.IS_ACTIVE, SYSDATE, SYSDATE FROM

    (SELECT (LEVEL-1)+0 VERSION FROM DUAL CONNECT BY LEVEL<=10) A,
    (SELECT (LEVEL-1)+0 IS_ACTIVE FROM DUAL CONNECT BY LEVEL<=2) B

;

The error I get:

SQL> SQL> SET ECHO ON;
SQL>
SQL> INSERT INTO MYDB.BOOK_TYPE (
  2      BOOK_TYPE_ID, UNIQUE_NAME, DESCRIPTION, VERSION, IS_ACTIVE, DATE_CREATED, DATE_MODIFIED
  3  )
  4  SELECT MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, 'Book-Type-' || MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, 'Description-' || MYDB.SEQ_BOOK_TYPE_ID.NEXTVAL, A.VERSION, B.IS_ACTIVE, SYSDATE, SYSDATE FROM
  5  
SQL>         (SELECT (LEVEL-1)+0 VERSION FROM DUAL CONNECT BY LEVEL<=10) A,
  2          (SELECT (LEVEL-1)+0 IS_ACTIVE FROM DUAL CONNECT BY LEVEL<=2) B
  3  
SQL> ;
  1     (SELECT (LEVEL-1)+0 VERSION FROM DUAL CONNECT BY LEVEL<=10) A,
  2*    (SELECT (LEVEL-1)+0 IS_ACTIVE FROM DUAL CONNECT BY LEVEL<=2) B

As you see the error is on (SELECT (LEVEL-1)+0 IS_ACTIVE FROM DUAL CONNECT BY LEVEL<=2) B (for some reason in all the files that get this error, the error appears on the last line before the concluding semicolon.)

Can you add a (preferably small) example of a script that doesn't work? — Mark J. Bobak
– Mark J. Bobak, Commented May 2, 2012 at 3:22
I struggled with that too, because I didn't need special features of PL SQL, just some set of queries and used the batchUpdate method on JdbcTemplate from spring framework. It seems to run as the DBeaver SQL client. — Lukasz
– Lukasz, Commented Oct 6, 2019 at 19:56

A.B.Cade · Accepted Answer · 2012-05-02 05:17:46Z

27

Remove the empty lines.
In sqlplus an empty line means stop previous statement and start a new one.

or you can set blank lines:

set sqlbl on

answered May 2, 2012 at 5:17

A.B.Cade

16.9k1 gold badge40 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user330315 Over a year ago

+1, I knew about the blank lines, but not about the configuration setting.

rapt Over a year ago

Hallelujah!!! You saved my day! You've hit the nail on the head. Thank you so much!

Gerard Reid · Accepted Answer · 2019-08-14 00:38:14Z

1

Alternatively you can use "Ctrl F7" in sqldeveloper to format your script. I had a similar issue and tried removing blanks etc. but I was obviously missing something. Found the Ctrl F7 function and the script worked.

answered Aug 14, 2019 at 0:38

Gerard Reid

111 bronze badge

Collectives™ on Stack Overflow

SQL*Plus does not execute SQL scripts that SQL Developer does

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related