时间戳

首页 > 解决方案 > 如何处理通过 shell 脚本执行的 Oracle SQL 选择语句中的保留字？

sql - 如何处理通过 shell 脚本执行的 Oracle SQL 选择语句中的保留字？

问题描述

在 Oracle 中有一个带有保留字的表。下面是我通过将 Double 引用到保留字来执行的查询。但得到错误

sqlplus -s user/'pwd'@schema @sql_file.sql 4 "nvl(to_char(\"sys_updated_on\",'YYYY-MM-DD HH24:MI:SS'),'2020-01-01 00:00:00')" SCHEMA.TABLE

ERROR:
ORA-01740: missing double quote in identifier

没有双引号低于错误。

ERROR at line 1:
ORA-00904: "SYS_UPDATED_ON": invalid identifier

我尝试了多种选择来转义保留字。任何帮助将不胜感激！

SQL 文件的内容

select /*+ parallel (m, &1) */ max(least(&2)) from &3 m;

标签： sqloracleoracle11g

解决方案

问题是您使用带引号的标识符创建对象。

从文档中：

带引号的标识符以双引号 (") 开头和结尾。如果您使用带引号的标识符命名架构对象，则在引用该对象时必须使用双引号。

如果您使用带引号的标识符创建对象，则它会区分大小写。

演示：

让我们使用带引号的标识符创建一个大写的列：

CREATE TABLE t_test (
    "SYS_UPDATED_ON" NUMBER
);

SELECT "sys_updated_on" FROM t_test;

ORA-00904: "sys_updated_on": invalid identifier

即使您DESCRIBE表格，您也不会看到双引号：

DESC t_test;

Name           Null? Type   
-------------- ----- ------ 
SYS_UPDATED_ON       NUMBER

它应该被称为：

SELECT "SYS_UPDATED_ON" FROM t_test;

现在，让我们使用带引号的标识符创建一个小写的列：

CREATE TABLE t_test_1 (
    "sys_updated_on" NUMBER
);

SELECT SYS_UPDATED_ON FROM t_test_1;

SQL Error: ORA-00904: "SYS_UPDATED_ON": invalid identifier

描述表格会让您知道对象名称是否区分大小写：

DESC t_test_1;

Name           Null? Type   
-------------- ----- ------ 
sys_updated_on       NUMBER

正确的方法是使用双引号创建的名称：

SELECT "sys_updated_on" FROM t_test_1;

关于SQL 和 PL/SQL 中的RESERVED字，您可以使用SQL*Plus 帮助实用程序列出所有这些字：

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> help reserved words;

 RESERVED WORDS (PL/SQL)
 -----------------------

 PL/SQL Reserved Words have special meaning in PL/SQL, and may not be used
 for identifier names (unless enclosed in "quotes").

 An asterisk (*) indicates words are also SQL Reserved Words.

 ALL*            DESC*           JAVA            PACKAGE         SUBTYPE
 ALTER*          DISTINCT*       LEVEL*          PARTITION       SUCCESSFUL*
 AND*            DO              LIKE*           PCTFREE*        SUM
 ANY*            DROP*           LIMITED         PLS_INTEGER     SYNONYM*
 ARRAY           ELSE*           LOCK*           POSITIVE        SYSDATE*
 AS*             ELSIF           LONG*           POSITIVEN       TABLE*
 ASC*            END             LOOP            PRAGMA          THEN*
 AT              EXCEPTION       MAX             PRIOR*          TIME
 AUTHID          EXCLUSIVE*      MIN             PRIVATE         TIMESTAMP
 AVG             EXECUTE         MINUS*          PROCEDURE       TIMEZONE_ABBR
 BEGIN           EXISTS*         MINUTE          PUBLIC*         TIMEZONE_HOUR
 BETWEEN*        EXIT            MLSLABEL*       RAISE           TIMEZONE_MINUTE
 BINARY_INTEGER  EXTENDS         MOD             RANGE           TIMEZONE_REGION
 BODY            EXTRACT         MODE*           RAW*            TO*
 BOOLEAN         FALSE           MONTH           REAL            TRIGGER*
 BULK            FETCH           NATURAL         RECORD          TRUE
 BY*             FLOAT*          NATURALN        REF             TYPE
 CHAR*           FOR*            NEW             RELEASE         UI
 CHAR_BASE       FORALL          NEXTVAL         RETURN          UNION*
 CHECK*          FROM*           NOCOPY          REVERSE         UNIQUE*
 CLOSE           FUNCTION        NOT*            ROLLBACK        UPDATE*
 CLUSTER*        GOTO            NOWAIT*         ROW*            USE
 COALESCE        GROUP*          NULL*           ROWID*          USER*
 COLLECT         HAVING*         NULLIF          ROWNUM*         VALIDATE*
 COMMENT*        HEAP            NUMBER*         ROWTYPE         VALUES*
 COMMIT          HOUR            NUMBER_BASE     SAVEPOINT       VARCHAR*
 COMPRESS*       IF              OCIROWID        SECOND          VARCHAR2*
 CONNECT*        IMMEDIATE*      OF*             SELECT*         VARIANCE
 CONSTANT        IN*             ON*             SEPERATE        VIEW*
 CREATE*         INDEX*          OPAQUE          SET*            WHEN
 CURRENT*        INDICATOR       OPEN            SHARE*          WHENEVER*
 CURRVAL         INSERT*         OPERATOR        SMALLINT*       WHERE*
 CURSOR          INTEGER*        OPTION*         SPACE           WHILE
 DATE*           INTERFACE       OR*             SQL             WITH*
 DAY             INTERSECT*      ORDER*          SQLCODE         WORK
 DECIMAL*        INTERVAL        ORGANIZATION    SQLERRM         WRITE
 DECLARE         INTO*           OTHERS          START*          YEAR
 DEFAULT*        IS*             OUT             STDDEV          ZONE
 DELETE*         ISOLATION


 RESERVED WORDS (SQL)
 --------------------

 SQL Reserved Words have special meaning in SQL, and may not be used for
 identifier names unless enclosed in "quotes".

 An asterisk (*) indicates words are also ANSI Reserved Words.

 Oracle prefixes implicitly generated schema object and subobject names
 with "SYS_". To avoid name resolution conflict, Oracle discourages you
 from prefixing your schema object and subobject names with "SYS_".

 ACCESS          DEFAULT*         INTEGER*        ONLINE          START
 ADD*            DELETE*          INTERSECT*      OPTION*         SUCCESSFUL
 ALL*            DESC*            INTO*           OR*             SYNONYM
 ALTER*          DISTINCT*        IS*             ORDER*          SYSDATE
 AND*            DROP*            LEVEL*          PCTFREE         TABLE*
 ANY*            ELSE*            LIKE*           PRIOR*          THEN*
 AS*             EXCLUSIVE        LOCK            PRIVILEGES*     TO*
 ASC*            EXISTS           LONG            PUBLIC*         TRIGGER
 AUDIT           FILE             MAXEXTENTS      RAW             UID
 BETWEEN*        FLOAT*           MINUS           RENAME          UNION*
 BY*             FOR*             MLSLABEL        RESOURCE        UNIQUE*
 CHAR*           FROM*            MODE            REVOKE*         UPDATE*
 CHECK*          GRANT*           MODIFY          ROW             USER*
 CLUSTER         GROUP*           NOAUDIT         ROWID           VALIDATE
 COLUMN          HAVING*          NOCOMPRESS      ROWNUM          VALUES*
 COMMENT         IDENTIFIED       NOT*            ROWS*           VARCHAR*
 COMPRESS        IMMEDIATE*       NOWAIT          SELECT*         VARCHAR2
 CONNECT*        IN*              NULL*           SESSION*        VIEW*
 CREATE*         INCREMENT        NUMBER          SET*            WHENEVER*
 CURRENT*        INDEX            OF*             SHARE           WHERE
 DATE*           INITIAL          OFFLINE         SIZE*           WITH*
 DECIMAL*        INSERT*          ON*             SMALLINT*

推荐阅读