神奇的“雙引號”——從一個誤建立物件錯誤談起

 

在Oracle中，無論是SQL還是PL/SQL，都是使用單引號進行字串的標識。雙引號是我們經常忽視的一個方面，本篇介紹一個由於雙引號引起的故障解決。

 

 

一個同事找到筆者，說建立的一個資料庫表不能drop掉。筆者連入到資料庫伺服器上，根據同事提供的名稱找到了對應資料表。

 

 

看到資料表，筆者發現了該表的一點不同，該資料表名為“NBS.REF_APPVALMODIFY”。請注意，這是資料表的名稱，前面的NBS.不是對應Schema的字首，而是資料表名稱的一部分。

 

 

這樣的問題還是頭一次遇到，經過詢問同事，才知道這個資料表是透過PL/SQL Developer圖形介面建立出來的。建立的時候因為疏忽，將schema引導的全名（schema=’NBS’）填寫到資料表名的上面，結果沒想到還真建立成功。但是之後無論是修改重新命名，還是刪除都不能成功。

 

 

嘗試

 

在NBS schema對應下的資料表物件中，的確可以看到該物件名。驗證一下資料字典情況。

 

 

SQL> select * from user_objects where object_name='NBS.REF_APPVALMODIFY';

 

OBJECT_NAME               OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE   

------------------------  ---------- -------------- --------------

NBS.REF_APPVALMODIFY          117293         117293 TABLE         

 

 

SQL> select * from dba_tables where table_name='NBS.REF_APPVALMODIFY';

 

OWNER    TABLE_NAME                     TABLESPACE_NAME   

--------------------------------------- -------------------

NBS      NBS.REF_APPVALMODIFY           NBSTBL            

 

（上述列資訊存在省略，處於篇幅考慮…）

 

從資料字典的情況看，Oracle承認這個名稱為“NBS.REF_APPVALMODIFY”的物件是合法Oracle物件，承認這個物件是一張資料表。

 

 

當我們進行刪除的時候，就會遇到問題。

 

 

SQL> desc nbs.NBS.REF_APPVALMODIFY;

Object nbs.NBS.REF_APPVALMODIFY does not exist.

 

SQL> drop table NBS.REF_APPVALMODIFY;

drop table NBS.REF_APPVALMODIFY

 

ORA-00942: 表或檢視不存在

 

SQL> drop table NBS.NBS.REF_APPVALMODIFY;

drop table NBS.NBS.REF_APPVALMODIFY

 

ORA-00933: SQL 命令未正確結束

 

 

根據我們一般的方法，我們無法進行描述、刪除資料表操作。

 

 

猜測和分析

 

仔細分析原因，筆者認為是由於nbs.在其中影響的緣故。Oracle一般認為xx.xx的關係是一種附屬關係，不會直接認為說是物件名稱。

 

 

突然想到一個問題，既然Oracle承認這個物件的資料表身份。那麼逆向的DDL語句是如何進行描述的呢？使用手段將DDL抽出之後，如下所示片段。

 

 

CREATE TABLE "NBS"."NBS.REF_APPVALMODIFY"

   (    "SEQ_NUMBER" NUMBER(13,0) NOT NULL ENABLE,

    "APPVAL_SEQ" NUMBER(13,0) NOT NULL ENABLE,

    "READ_FIELD" VARCHAR2(20) NOT NULL ENABLE,

    "DB_FIELD" VARCHAR2(20) NOT NULL ENABLE,

    "OLD_VALUE" VARCHAR2(20),

    "NEW_VALUE" VARCHAR2(20) DEFAULT 'I' NOT NULL ENABLE,

    "APPROVAL_TYPE" VARCHAR2(20) NOT NULL ENABLE,

    "CREATE_USER" VARCHAR2(20) NOT NULL ENABLE,

    "CREATE_DATE" DATE NOT NULL ENABLE,

 

 

注意，這裡出現的雙引號標記。那麼是不是如果使用雙引號將資料表名進行封裝包裹起來，Oracle就無條件的將其識別為資料表名呢？結合同事是使用開發工具圖形介面生成的資料表，這種情況不無可能。

 

 

SQL> drop table "NBS.REF_APPVALMODIFY";

SQL> select * from dba_tables where table_name='NBS.REF_APPVALMODIFY';

 

OWNER                          TABLE_NAME                     

------------------------------ ------------------------------

 

 

刪除成功。看來分析是正確的。

 

 

對於雙引號的實驗

 

下面針對雙引號，我們進行一系列的實驗。

 

 

SQL> create table nbs.nbs.test (id number);

 

create table nbs.nbs.test (id number)

 

ORA-00922: 選項缺失或無效

 

SQL> create table nbs."nbs.test" (id number);

SQL> drop table "nbs.test";

 

 

在沒有雙引號的幫助下，nbs.test這類比較奇怪的識別符號是不能被識別為合法物件名稱的。xx.xx結構已經違反了標準Oracle內部規範。

 

接下來的實驗能說明另一方面。

 

 

SQL> create table "lsd.test" (id number);

 

SQL> select table_name from user_tables;

 

TABLE_NAME

------------------------------

T2

lsd.test

 

SQL> select * from lsd.test;

 

select * from lsd.test

 

ORA-00942: 表或檢視不存在

 

SQL> select * from "lsd.test";

 

        ID

----------

 

 

 

一般，我們使用create物件的時候，Oracle會將物件自動轉化為大寫字母，儲存在資料字典中。使用了雙引號之後，這種機制被禁止掉。雙引號中有什麼，就自動把裡面的內容直接作為物件資訊。

 

 

SQL> create table "kl.t" ("date" number);

 

Table created

 

SQL> create table "kl.t" (date number);

 

create table "kl.t" (date number)

 

 

 

上面的例子中，解釋了雙引號可以幫我們迴避關鍵字禁忌。一般情況下,date作為關鍵字是不被允許作為列名或者變數使用的。

 

 

我們日常中，直接書寫的帶雙引號SQL的情況是很少。但是一些會生成SQL語句的工具軟體或者框架軟體，會廣泛使用雙引號來遮蔽關鍵字引起衝突的情況。

 

 

一次偶然的故障問題，學習到了一直忽視的知識點，算是有所得吧。