static void Main(string[] args)
{
using (var cn = new OracleConnection(cs))
{
//**重要** 先開啟連線,確保後續執行在同一個Session
cn.Open();
//**重要** 記得要啟用dbms_output
cn.Execute("dbms_output.enable",
commandType: CommandType.StoredProcedure);
//方法1,使用PLSQLAssociativeArray接回陣列
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
var cmd = cn.CreateCommand();
cmd.CommandText = "dbms_output.get_lines";
cmd.CommandType = CommandType.StoredProcedure;
var pLines = cmd.Parameters.Add("lines", OracleDbType.Varchar2,
ParameterDirection.Output);
pLines.Size = 2000; //可容納的訊息字串筆數
pLines.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
//指定每筆字串最大長度(Oracle 12可到32767)
pLines.ArrayBindSize = Enumerable.Repeat(4000, pLines.Size).ToArray();
//numlines為雙向參數,執行前傳入lines可容納筆數,執行後傳回實際讀得筆數
var pNumLines = cmd.Parameters.Add("numlines", OracleDbType.Int32,
ParameterDirection.InputOutput);
pNumLines.Value = pLines.Size;
cmd.ExecuteNonQuery();
var rawLines = (OracleString[]) pLines.Value;
string[] lines =
//依numlines判斷資料筆數
rawLines.Take(((OracleDecimal) pNumLines.Value).ToInt32())
.Select(o => o.ToString()).ToArray();
Console.WriteLine(string.Join("\n", lines));
//缺點: 程序較複雜,得先預估空間,若一次取不完還是得跑迴圈(但應很罕見)
//方法二,用PL/SQL指令組成字串一次傳回
//呼叫Stored Procedure
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
var p = new DynamicParameters();
p.Add("result", dbType: DbType.AnsiString, size: 32767,
direction: ParameterDirection.Output);
cn.Execute(@"
DECLARE
lines dbmsoutput_linesarray;
numlines INTEGER;
i INTEGER;
msg VARCHAR2(32767);
BEGIN
numlines := 32767;
dbms_output.get_lines(lines, numlines);
i := 1;
WHILE i <= numlines
LOOP
IF i = 1 THEN
msg := lines(i);
ELSE
msg := msg || CHR(10) || lines(i);
END IF;
i := i + 1;
END LOOP;
:result := msg;
END;
", p);
Console.WriteLine(p.Get<string>("result"));
//缺點: 取回字串有長度限制(12c 32767,更早版本只有4000)
//方法3,使用自訂函數轉成Table
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
lines = cn.Query<string>(
"SELECT column_value from table(get_dbms_output)").ToArray();
foreach (var line in lines)
Console.WriteLine(line);
//缺點: 需在資料庫部署自訂函數
//方法4,用RefCursor
cn.Execute("JeffDbJobTest1",
commandType: CommandType.StoredProcedure);
//使用 Dapper 接收 Oracle Ref Cursor
//http://blog2.darkthread.net/post-2017-04-17-dapper-ref-cursor.aspx
var op = new OracleDynamicParameters();
op.Add("res", dbType: OracleDbType.RefCursor,
direction: ParameterDirection.Output);
var m = cn.QueryMultiple(@"
DECLARE
lines dbmsoutput_linesarray;
numlines INTEGER;
BEGIN
dbms_output.get_lines(lines, numlines);
IF lines.COUNT > numlines THEN
lines.TRIM;
END IF;
OPEN :res FOR SELECT column_value FROM TABLE(lines);
END;
", op);
var data = m.Read();
lines = data.Select(o => (string) o.COLUMN_VALUE).ToArray();
foreach (var line in lines)
Console.WriteLine(line);
Console.Read();
}
}