SQL * Plus不执行SQL Developer所执行的SQL脚本 [英] SQL*Plus does not execute SQL scripts that SQL Developer does

问题描述

我正面临一个非常烦人的问题.我已经在Notepad ++中编写了一些SQL脚本.现在，当我尝试通过SQL * Plus(在Windows 7上通过命令行)执行它们时，出现了ORA-00933: SQL command not properly ended之类的错误.

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.

然后我复制&将脚本粘贴到"SQL Developer工作表"窗口中，单击运行"按钮，脚本执行成功，没有任何问题/错误.

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

经过长时间的调查，我开始认为SQL * Plus存在一些无法理解的空格(包括换行符和制表符)问题.

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.

由于我假设SQL Developer知道如何摆脱怪异的空格，所以我尝试了以下操作:将脚本粘贴到SQL Developer工作表窗口中，然后从那里复制并将其粘贴回SQL脚本中.这就解决了某些文件的问题，但不是所有文件的问题.某些文件没有明显原因一直在地方显示错误.

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.

您遇到过这个问题吗?我应该怎么做才能通过命令行通过SQL * Plus运行这些脚本?

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

更新:

无法与SQL * Plus一起使用但与SQL Developer一起使用的脚本示例:

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

;

我得到的错误:

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

如您所见，错误出现在(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.)

推荐答案

删除空白行.
在sqlplus中，空行表示停止上一条语句并开始一条新语句.

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

这篇关于SQL * Plus不执行SQL Developer所执行的SQL脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！