對 ODP.NET 如何跑 Oracle 多行指令,我始終一知半解。何時可以加分號「;」?何時該用 begin end?為何冒出 Encountered the symbol "xxx" when expecting one of the following: ... 錯誤?我通常是亂試一通,能跑就當沒事,沒想過認真把它弄清楚。前陣子查資料順便做了功課,終於有點心得。

問題一,CommandText 能不能有多行指令?需不需要加 ;?

CommandText 可一次跑多行指令的印象主要來自 MSSQL,而 SQL Server 行末的分號可加可不加;Oracle GUI 工具如 Oracle SQL Developer,允許寫兩行 INSERT 以分號結尾(跟 SQL 不同是分號不可省略),全選執行可以一次插入兩筆,於是我有了「使用分號分隔,Oracle 可一次跑多行指令」的錯覺:

但是把同樣的程式搬到 ODP.NET,滿心期望可用 OracleCommand 一次跑完,卻以命令結束有問題出錯收場 - ORA-00933: SQL 命令的結束有問題 / SQL command not properly ended

先釐清一點,SQLPlus、Oracle SQL Developer 這些工具有一種命令檔(Script)模式,當輸入多行指令(Statement)並以分號結尾,要以命令檔模式才會全部執行。以 Oracle SQL Developer 為例,UI 的執行有兩顆鈕,左邊 1 是 Execute Statement (執行述描句)、2 所指的是 Run Script (執行命令檔)。前者只會執行游標所在位置的指令,如果有兩行 INSERT 而游標停在第一行,只會執行第一行;按 Run Script 或全選再按 Execute Statement 才會全部執行,而分號就是觸發執行該 Statement 的標記。

CommandText 只能執行一個 Statement,無法容納多個 Statement,不管有沒有加分號。要突破這個限制有一個做法是用 begin 與 end; 將多行指令夾起來,這段指令將被包成一個 PL/SQL Block,其中可容納多個 Statement,但每個 Statement 要以分號結尾才符合規範,不像 MSSQL 可加可不加:

問題二,為什麼有時會冒出 Encountered the symbol "xxx" when expecting one of the following: ( begin case declare end exception ... 錯誤?

學會了 bein end; 大絕,我想把 Oracle SQL Developer 建資料表動作搬到 ODP.NET 裡跑,例如:先建 Table 再加欄位註釋:

結果得到 ORA-06550 PLS-00103 Encountered the symbol "CREATE" when expecting one of the following: ( begin case declare end exception exit for goto if loop mod null pragma raise return select update while with ... :

bein end; 包含的區塊是所謂 PL/SQL Anonymous Block,而 PL/SQL Block 的一項重要原則是不可以包含 DDL (Data Definition Language) 指令,舉凡 CREATE、ALTER、DROP、GRANT、REVOKE、COMMENT... 都在禁止之列。如果一定要用,須改以 execute immediate 間接執行,但因涉及組裝 SQL 指令,請務必謹慎不要留下 SQL Injection 漏洞。

begin
execute immediate '
CREATE TABLE TBL (
    PK INT,
    N VARCHAR(16)
)';
execute immediate 'COMMENT ON COLUMN TBL.PK IS ''Primary Key''';
execute immediate 'COMMENT ON COLUMN TBL.N IS ''Name''';
end;

問題三,換行符號 "\r" 到底要不要移掉?

Windows 平台的換行符號是"\r\n",而 UNIX 世界是"\n",在過去 CommandText 的換行符號常會導致 PLS-00103: Encountered the symbol "" when expecting one of the following: begin case declare exit for function goto if loop ... 錯誤,有很多次經驗需用 Replace("\r", string.Empty) 消毒,但隨著 Oracle 版本更新,似乎已能自動修正,例如用 Managed ODP.NET 連 XE 18c,故意送出 \r 也不會有問題:

小結

整理一下本篇提到的幾個要點:

  1. CommandText 裡只能有一個執行動作(SELECT、INSERT、UPDATE、DELETE...),若要包含多個一次執行,可用 begin end; 包成 PL/SQL Block
  2. PL/SQL Block 不能混入 DDL 資料庫異動指令(CREATE、ALTER...),除非以 EXECUTE IMMEDIATE '...' 方式間接觸發
  3. 過去 CommandText 中不能摻雜 \r 換行符號,但當代 Oracle Client 似已無此限制

Tips of how to run multiple statements in one CommandText in ODP.NET.


Comments

# by 偉倫

您好, 請教一下,是否有嘗試過轉換過以下情境為oracle語法? 1. 原mssql語法中使用declare後再執行select的情境 2. 原mssql語法中使用建立暫存表(select into)後,後續執行select時會再使用該暫存表

# by Jeffrey

to 偉倫,好問題,沒試過。關於這個議題,改天我再專門寫一篇文跟各位講解... (老高上身) 記得訂閱分享按下小鈴噹哦。

Post a comment