Rückgabe des Cursors aus dem PL/SQL-Anonymen Block in C#

Question

Ich arbeite daran, eine bestehende Anwendung von SQL Server auf Oracle zu konvertieren, und ich bin auf ein Hindernis gestoßen. Ich versuche, einen anonymen Block als dynamisches SQL auszuführen und ein Ergebnisset zurückzugeben. Jedoch scheint nichts, was ich versucht habe, in der Lage zu sein, irgendwelche Werte zurückzugeben. Gespeicherte Prozeduren sind aufgrund von Designbeschränkungen ausgeschlossen.

Meine Abfrage ist definiert als:

DECLARE type id_array IS TABLE OF number;
t_Ids id_array;
BEGIN
UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE
WHERE OBJECT_UID IN 
(SELECT OBJECT_UID 
FROM CSM_RECORDS obj 
WHERE MIGRATION_STATE = 0
AND ROWNUM <= :BatchSize)
AND (:BatchName IS NULL OR obj.BATCH_NAME = :BatchName)
RETURNING OBJECT_UID BULK COLLECT INTO t_Ids;

OPEN rcursor FOR SELECT * FROM CSM_RECORDS;-- WHERE OBJECT_UID IN (t_Ids);
END;

Wie Sie sehen können, habe ich die WHERE-Klausel im Cursor auskommentiert, in einem Versuch, überhaupt etwas zurückzugeben.

Auf der C#-Seite habe ich folgendes:

OracleCommand getNextNodesC = new OracleCommand(SQL_AS_SHOWN_ABOVE, conn);
getNextNodesC.BindByName = true;

OracleParameter batchSizeP = new OracleParameter("BatchSize", OracleDbType.Int32);
batchSizeP.Value = batchSize;

getNextNodesC.Parameters.Add(batchSizeP);

OracleParameter batchNameP = new OracleParameter("BatchName", OracleDbType.Varchar2);
batchNameP.Value = batchName;

getNextNodesC.Parameters.Add(batchNameP);

OracleParameter returnCursor = new OracleParameter("rcursor", OracleDbType.RefCursor);
returnCursor.Direction = ParameterDirection.Output;
getNextNodesC.Parameters.Add(returnCursor);

getNextNodesC.ExecuteNonQuery();

return ((Oracle.ManagedDataAccess.Types.OracleRefCursor)returnCursor.Value).GetDataReader();

Das Endziel ist ein DbDataReader, den ich verwenden kann, aber im obigen Code scheint der returnCursor.Value weiterhin null zu bleiben. Ich habe verschiedene Kombinationen von Output vs. ReturnValue Parametern und ExecuteNonQuery() und ExecuteReader() ausprobiert, leider ohne Erfolg.

Jegliche Hinweise wären willkommen, aber ein Beispielcode, der tatsächlich das erreichen würde, was ich suche, wäre spektakulär.

Answer 1

TLDR: Sie vermissen den Doppelpunkt bei Ihrer Cursor-Bind-Variablen:

OPEN :rcursor FOR SELECT * FROM CSM_RECORDS;-- WHERE OBJECT_UID IN (t_Ids);

Vollständige Antwort: Wie Sie wahrscheinlich wissen, gibt es in Oracle den PL/SQL-Kontext (Ihr anonymer Block) und den SQLplus-Kontext. Hier ist ein Block mit SQLplus-Variablen, der Block mit den entsprechenden Bind-Variablen und ein SQLplus-Druck am Ende:

var rcursor refcursor
var fromDate varchar2(50)
var toDate varchar2(50)
exec :fromDate := '1-mar-2014';
exec :toDate := '1-apr-2014';

begin
  open :rcursor for
    SELECT
     trunc(to_date(:fromDate,'dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date
    FROM (
    select (level-1) n
    from dual
    connect by level-1 <= trunc(to_date(:toDate,'dd-mon-yyyy')) - trunc(to_date(:fromDate,'dd-mon-yyyy'))
   )
  ;
end;
/
print rcursor

Bei der Ausführung eines Blocks in .net kümmert sich ODP.net um die Vorbereitung auf der SQLplus-Ebene. Hier ist der gleiche Block, der von .net (als NUnit-Test) ausgeführt wird:

[Test]
public void RefCursorFromBatch()
{
  OracleCommand cmd = new OracleCommand();
  cmd.CommandText = @"
  begin
    open :rcursor for
      SELECT
       trunc(to_date(:fromDate,'dd-mon-yyyy')) + NUMTODSINTERVAL(n,'day') AS Full_Date
      FROM (
      select (level-1) n
      from dual
      connect by level-1 <= trunc(to_date(:toDate,'dd-mon-yyyy')) - trunc(to_date(:fromDate,'dd-mon-yyyy'))
     )
    ;
  end;";
  cmd.BindByName = true;
  cmd.Parameters.Add("fromDate", OracleDbType.Date).Value = DateTime.Today.AddDays(-30);
  cmd.Parameters.Add("toDate", OracleDbType.Date).Value = DateTime.Today;
  cmd.Parameters.Add("rcursor", OracleDbType.RefCursor).Direction = ParameterDirection.Output;

  using (cmd.Connection = new OracleConnection("..."))
  {
    cmd.Connection.Open();
    var reader = cmd.ExecuteReader();

    OracleDataAdapter da = new OracleDataAdapter(cmd);
    DataTable dt = new DataTable();
    da.Fill(dt);

    Assert.Greater(dt.Rows.Count, 0);

  }
}

Sie können hier mehr lesen: http://www.brothersincode.com/post/executing-SQL-Plus-Batches-from-Net.aspx

Answer 2

Wenn Sie noch keine Antwort haben, hier ist ein Beispielcode, von dem ich bestätigt habe, dass er funktioniert, den Sie als Ausgangspunkt verwenden können.

using System;
using System.Data;
using Oracle.ManagedDataAccess.Client;
using Oracle.ManagedDataAccess.Types;

namespace ConsoleApplication1
{
  class Class1
  {

    [STAThread]
    static void Main(string[] args)
    {
      try
      { 
        string conString = "User Id=scott;Password=tiger;Data Source=orcl;Pooling=false;";

        OracleConnection con = new OracleConnection();
        con.ConnectionString = conString;
        con.Open();

        string cmdtxt = "BEGIN " +
          "OPEN :1 for select ename, deptno from emp where deptno = 10; " +
          "OPEN :2 for select ename, deptno from emp where deptno = 20; " +
          "OPEN :3 for select ename, deptno from emp where deptno = 30; " +
          "END;";

        OracleCommand cmd = con.CreateCommand();
        cmd.CommandText = cmdtxt;

        OracleParameter p1 = cmd.Parameters.Add("refcursor1",
            OracleDbType.RefCursor);
        p1.Direction = ParameterDirection.Output;

        OracleParameter p2 = cmd.Parameters.Add("refcursor2",
            OracleDbType.RefCursor);
        p2.Direction = ParameterDirection.Output;

        OracleParameter p3 = cmd.Parameters.Add("refcursor3",
            OracleDbType.RefCursor);
        p3.Direction = ParameterDirection.Output;

        cmd.ExecuteNonQuery();

        OracleDataReader dr1 =
          ((OracleRefCursor)cmd.Parameters[2].Value).GetDataReader();
        OracleDataReader dr2 =
          ((OracleRefCursor)cmd.Parameters[1].Value).GetDataReader();

        while (dr1.Read() && dr2.Read())
        {
          Console.WriteLine("Employee Name: " + dr1.GetString(0) + ", " +
              "Employee Dept:" + dr1.GetDecimal(1));
          Console.WriteLine("Employee Name: " + dr2.GetString(0) + ", " +
              "Employee Dept:" + dr2.GetDecimal(1));
          Console.WriteLine();
        }

        Console.WriteLine("Drücken Sie 'Enter', um fortzufahren");
        Console.ReadLine();

      }
      catch (Exception ex)
      {
        Console.WriteLine(ex.Message);
        Console.WriteLine(ex.InnerException);
        Console.WriteLine(ex.Data);
      }
    }
  }
}

Answer 3

Versuchen Sie dies als Ihr dynamisches SQL...

DECLARE
  type id_array IS TABLE OF number;
  t_Ids id_array;
BEGIN
  UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE
  WHERE OBJECT_UID IN 
  (SELECT OBJECT_UID 
   FROM CSM_RECORDS obj 
   WHERE MIGRATION_STATE = 0
   AND ROWNUM <= :BatchSize)
  RETURNING OBJECT_UID BULK COLLECT INTO t_Ids;

  SELECT * FROM CSM_RECORDS;-- WHERE OBJECT_UID IN (t_Ids);
END;

Answer 4

Danke für das Feedback, jeder. Es stellte sich heraus, dass ich während der Fehlersuche dieses Problems irgendwann die Variable ausgetauscht habe, die meine Abfrage definierte, sodass die Änderungen, die ich an der Abfrage vornahm, tatsächlich nicht angewendet wurden, als die Anwendung ausgeführt wurde. Daher bin ich mir nicht sicher, welche Lösung letztendlich die Antwort war. Aber nur zur Vollständigkeit, hier ist, was ich verwendet habe und das funktioniert hat.

Zuerst führen Sie aus:

CREATE TYPE id_array AS TABLE OF NUMBER;

(Von der Antwort auf diese Frage)

Abfrage:

DECLARE t_Ids id_array;
BEGIN
UPDATE CSM_RECORDS SET MIGRATION_STATE = 1, LAST_UPDATE = CURRENT_DATE
    WHERE OBJECT_UID IN 
    (SELECT OBJECT_UID 
    FROM CSM_RECORDS obj 
    WHERE MIGRATION_STATE = 0
    AND ROWNUM <= :BatchSize
    AND (:BatchName IS NULL OR obj.BATCH_NAME = :BatchName)
RETURNING OBJECT_UID BULK COLLECT INTO t_Ids;

OPEN :rcursor FOR SELECT * FROM CSM_RECORDS WHERE OBJECT_UID IN (SELECT * FROM TABLE(cast(t_Ids AS id_array)));
END;

Und der C#-Code sieht so aus:

OracleCommand getNextNodesC = new OracleCommand(QUERY_DEFINED_ABOVE.Replace("\r\n", "\n"), conn);

getNextNodesC.BindByName = true;

OracleParameter batchSizeP = new OracleParameter("BatchSize", OracleDbType.Int32);
batchSizeP.Value = batchSize;
getNextNodesC.Parameters.Add(batchSizeP);

OracleParameter batchNameP = new OracleParameter("BatchName", OracleDbType.Varchar2);
batchNameP.Value = batchName;
getNextNodesC.Parameters.Add(batchNameP);

OracleParameter returnCursor = new OracleParameter("rcursor", OracleDbType.RefCursor);
returnCursor.Direction = ParameterDirection.ReturnValue;
getNextNodesC.Parameters.Add(returnCursor);

return getNextNodesC.ExecuteReader();

Die Vorschläge von b_levitt bezüglich des fehlenden Doppelpunkts bei der Cursor-Bindungsvariable waren jedoch genau richtig. Und ich musste auch alle "\r\n"s in meiner Abfrage durch "\n" ersetzen.

Ich bin immer noch nicht klar darüber, warum ich CREATE TYPE... ausführen muss, anstatt nur DECLARE TYPE... zu verwenden, da letzteres für den BULK COLLECT-Teil des Blocks zu funktionieren scheint, aber es funktioniert jetzt einwandfrei.

Nochmals vielen Dank für die Hilfe.