ODP.NET CommandText 跑多行指令

對 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 一次跑完，卻以命令結束有問題出錯收場：

先釐清一點，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.