SQL*Plus 不执行 SQL Developer 执行的 SQL 脚本

2021-12-30 00:00:00 sql oracle11g oracle sqlplus oracle-sqldeveloper

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

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.

或者你可以设置空行:

set sqlbl on

相关文章