Mit C# eine Oracle Stored Procedure aufrufen und eine Record Table verarbeiten

[skluge]

Hallo zusammen,

ich versuche über C#.Net eine bestehende Oracle Package Procedure aufzurufen, die mir eine Record Table zurückliefert.

Hier die Definitionen von Oracle:

create or replace 
PACKAGE TEST_PAC AS 
... 
 TYPE TESTREFERENCE IS RECORD 
  ( DOCNUMBER VARCHAR2(20), 
    REFERENCE VARCHAR2(50), 
    TYPE NUMBER, 
    DESCRIPTION VARCHAR2(200)); 

 TYPE TESTREFERENCECOL IS TABLE OF TESTREFERENCE INDEX BY BINARY_INTEGER; 
... 

 FUNCTION GetDocumentReferences( pdocnumber IN VARCHAR2, preftyp IN NUMBER DEFAULT NULL ) 
          RETURN TESTREFERENCECOL ;

Wie kann ich nun aus meinem C#-Code aus diese Stored Procedure aufrufen und vor allem den Rückgabewert verarbeiten? Ich habe schon mehrere Lösungsansätze ausprobiert aber noch keiner hat was gebracht (anonyme PL/SQL Block, Out-Parameetr etc.). Ich denke, dass es hauptsächlich daran liegt, dass ich den Aufruf aus C# falsch aufbaue.

Könnt ihr mir hier helfen und mir ein Beispiel für das obenstehende Problem zeigen? Vielen Dank!

Gruß

[hustbaer]

skluge schrieb:

Ich denke, dass es hauptsächlich daran liegt, dass ich den Aufruf aus C# falsch aufbaue.

Dann wäre es vielleicht gut wenn du uns zeigst wie du es momentan machst, und beschreibst was dabei das Problem ist (Exception/du bekommst ein leeres Recordset zurück/...).

[skluge]

Wie schon beschrieben, hab ich mehrere Varianten ausprobiert. Ich kann gerne mal ein paar davon hier posten.

OracleConnection conn = new OracleConnection();
                conn.ConnectionString = "...";

                try
                {
                    conn.Open();
                    if (conn.State == ConnectionState.Open)
                    {
                        OracleCommand command = conn.CreateCommand();
                        command.CommandText = "TEST_PAC.GetDocumentReferences";
                        command.CommandType = CommandType.StoredProcedure;

command.CommandText = "declare\n" +
                                              "  vFooRes TEST_PAC.TESTREFERENCECOL;\n" +
                                              "begin\n" +
                                              "  vFooRes := TEST_PAC.GetDocumentReferences('555-0002', TEST_PAC.CREFID_DOC_APPROPRIATEDATA);\n" +
                                              "  open :vFooRes for select vFooRes.DOCNUMBER, vFooRes.REFERENCE, vFooRes.TYPE, vFooRes.DESCRIPTION from dual;\n" +
                                              "end;";

                        OracleParameter p = command.Parameters.Add(
                                              "vFooRes ",
                                               OracleDbType.RefCursor,
                                               DBNull.Value,
                                               ParameterDirection.Output
                                            );

                        command.ExecuteNonQuery();

Fehlermeldung/Exception:

ORA-06550: line 6, column 5:
PLS-00103: Encountered the symbol "(" 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
<an identifier> <a double-quoted delimited-identifier>
<a bind variable> << close current delete fetch lock insert
open rollback savepoint set sql execute commit forall merge
pipe
The symbol "mod" was substituted for "(" to continue.

Beispiel 2:

OracleParameter _RefParam = new OracleParameter();
                        _RefParam.ParameterName = "DocReferences";
                        _RefParam.OracleDbType = OracleDbType.RefCursor;
                        _RefParam.Direction = ParameterDirection.Output;
                        command.Parameters.Add(_RefParam);

                        OracleDataAdapter adapter = new OracleDataAdapter(command);
                        DataSet dataSet = new DataSet();
                        adapter.Fill(dataSet);

Die verschiedensten Varianten haben bis jetzt noch nichts gebracht.Habt ihr ein konkretes Beispiel, wie man den anonymen PS/SQL-Befehl setzen muss, damit man in C# diesen Returnwert in ein DataSet schreiben kann?

[Th69]

Du kannst von C# aus keinen PL/SQL Code direkt als Command ausführen (lassen).
Bei Angabe von "CommandType.StoredProcedure" wird einfach der CommandText als Name der auszuführenden StoredProcedure interpretiert, s. z.B. Lesson 07: Using Stored Procedures.
Die Ergebnisse erhältst du dann per

OracleDataReader reader = command.ExecuteReader();

[hustbaer]

Im Prinzip hat Th69 es ja schon geschrieben, aber...

command.CommandText = "TEST_PAC.GetDocumentReferences"; 
                         command.CommandType = CommandType.StoredProcedure; 

 command.CommandText = "declare\n" + 
                                               "  vFooRes TEST_PAC.TESTREFERENCECOL;\n" + 
                                               "begin\n" + 
                                               "  vFooRes := TEST_PAC.GetDocumentReferences('555-0002', TEST_PAC.CREFID_DOC_APPROPRIATEDATA);\n" + 
                                               "  open :vFooRes for select vFooRes.DOCNUMBER, vFooRes.REFERENCE, vFooRes.TYPE, vFooRes.DESCRIPTION from dual;\n" + 
                                               "end;";

Das ist Unsinn.
Du setzt hier 2x CommandText. Erstmal auf "TEST_PAC.GetDocumentReferences" und danach gleich nochmal auf den längeren String (was auch immer der darstellen soll -- ist aber auf jeden Fall mehr als bloss der Name einer Stored-Procedure).

Setz CommandType einfach auf Text und in CommandText schreibst du nen Text rein den du sonst auch als "Batch" von Oracle ausführen lassen könntest.
Und dann, wie Th69 ja auch schon geschrieben hat, mit ExecuteReader ausführen.