Kein Zugriff auf Oracle Stored Proc mit Resultset

[Hoshy]

Hi,

ich versuche gerade verzweifelt, mit D7 Enterprise eine Stored Proc in einer Oracle 9i DB aufzurufen...theoretisch ist alles klar, nur in der Praxis geht gar nix :?

Die Stored Proc in Oracle sieht so aus:

SQL-Code:

procedure P_MY_TESTPROC (v_id in number, v_value out number, v_refcursor in out gencurtype)
  is
  begin
    v_value := 4711;
    open v_refcursor for select * from T_TEST;
  end;
end;

Das Ganze in ein Package "MY_PACKAGE" eingebaut, wo auch v_refcursor als type ref cursor deklariert ist.

Zugriff auf die Proc mit folgenden Versuchsaufbauten, alle gescheitert:

(1) ZeosDBO - ZConnection.Protocol := 'oracle-9i'
Connect zur Datenbank geht, aber verfügbare Procs werden in der ZStoredProc nicht angezeigt. Params gesetzt, Versuch die Proc zu öffnen liefert immer "Unsupported operation".

(2) ZeosDBO - ZConnection.Protocol := 'ado' / OLEDB Provider für ODBC
Connect zur Datenbank geht, verfügbare Procs werden in der ZStoredProc angezeigt. Params gesetzt, Versuch die Proc zu öffnen liefert immer "Ein Objekt, das dem angeforderten Namen oder dem Ordinalverweis entspricht, kann nicht gefunden werden".

(3) ZeosDBO - ZConnection.Protocol := 'ado' / OLEDB Provider für Oracle
Connect zur Datenbank geht, verfügbare Procs werden in der ZStoredProc angezeigt. Params gesetzt, Versuch die Proc zu öffnen liefert immer "PLS-00306: wrong number or types of arguments in call to 'P_MY_TESTPROC".

(4) dbExpress mit SQLStoredProg / SQLDataSet
Connect zur Datenbank geht, aber verfügbare Procs werden nicht angezeigt. Params gesetzt, Versuch die Proc zu öffnen liefert immer "List Index out of bounds (0)".

(5) ado mit ADOStoredProc/ ADODataSet
Connect zur Datenbank geht, verfügbare Procs werden angezeigt. Beim Versuch, den Parameter V_REFCURSOR als Typ ftCursor zu setzen, krieg ich immer den Fehler "Die Argumente sind vom falschen Typ, liegen außerhalb des Gültigkeitsbereichs oder sind miteinander unvereinbar".Zum Öffnen des Dataset komme ch schon gar nicht mehr :-(

Ich habe nicht den geringsten Plan, wieso das nicht geht! Mit den bisher von mir verwendeten Komponenten der NCOCI8 Library, V 1.0.4 (www.torry.net), funktioniert der Aufruf der Proc einwandfrei.

Hat da irgendjemand eine Idee, was ich falsch mache?

Gruß
Andi

[Bernhard Geyer]

Zitat:

Zitat von Hoshy

(5) ado mit ADOStoredProc/ ADODataSet
Connect zur Datenbank geht, verfügbare Procs werden angezeigt. Beim Versuch, den Parameter V_REFCURSOR als Typ ftCursor zu setzen, krieg ich immer den Fehler "Die Argumente sind vom falschen Typ, liegen außerhalb des Gültigkeitsbereichs oder sind miteinander unvereinbar".Zum Öffnen des Dataset komme ch schon gar nicht mehr :-(

Verwendest Du den MS Oracle Provider? Falls Ja: Dieser ist mehr eine Machbarkeitsstudie denn das man ihne produktiv verwenden kann.

Ich empfehle dir direkte Zugriffskomponenten wie z.B. ODAC zu nehmen.

[Hoshy]

Zitat:

Ich empfehle dir direkte Zugriffskomponenten wie z.B. ODAC zu nehmen.

Genau davon wollte ich halt ein bißchen weg, um nicht so stark an eine Datenbank gebunden zu sein...Sonst hätte ich ja auch bei der NCOCI8 Library bleiben können, ebenfalls Komponenten, die direkt auf den Oracle Bibliotheken aufsetzen. Funktionieren gut, sind kostenlos und mit Sourcecode.

[Bernhard Geyer]

Zitat:

Zitat von Hoshy

Zitat:

Ich empfehle dir direkte Zugriffskomponenten wie z.B. ODAC zu nehmen.

Genau davon wollte ich halt ein bißchen weg, um nicht so stark an eine Datenbank gebunden zu sein...Sonst hätte ich ja auch bei der NCOCI8 Library bleiben können, ebenfalls Komponenten, die direkt auf den Oracle Bibliotheken aufsetzen. Funktionieren gut, sind kostenlos und mit Sourcecode.

Du bist auch mit ADO und ODBC Datenbankgebunden da es zwar eine SQL-Standard gibt aber jede Kleinigkeit die hier nicht bis ins Details definiert ist von 3 Datenbanken auf 4 verschiedene Arten realisiert wird. Mit dem Brückenmuster z.B. kann man sehr gut eine abstrakte Datenbankschnittstelle definieren um nicht an eine DB gebunden zu sein.

[Hoshy]

Zitat:

Mit dem Brückenmuster z.B. kann man sehr gut eine abstrakte Datenbankschnittstelle definieren um nicht an eine DB gebunden zu sein

Im Grunde genommen stellt ADO als Schnittstelle ja so etwas dar...Ich kann - für den Anwender vollkommen transparent - den Provider der ADOConnection ändern um somit ohne weitere Änderung der Implementation des Frontend direkt auf ein anderes RDBMS aufsetzen. Die durch unterschiedliche SQL Dialekte notwendigen Anpassungen kann ich - im Idealfall - ausschließlich im Backend machen.

Aber das geht schon zu sehr in den Bereich Softwaredesign und löst leider mein konkretes Problem nicht :wink:

[Bernhard Geyer]

Zitat:

Zitat von Hoshy

Zitat:

Mit dem Brückenmuster z.B. kann man sehr gut eine abstrakte Datenbankschnittstelle definieren um nicht an eine DB gebunden zu sein

Im Grunde genommen stellt ADO als Schnittstelle ja so etwas dar...Ich kann - für den Anwender vollkommen transparent - den Provider der ADOConnection ändern um somit ohne weitere Änderung der Implementation des Frontend direkt auf ein anderes RDBMS aufsetzen. Die durch unterschiedliche SQL Dialekte notwendigen Anpassungen kann ich - im Idealfall - ausschließlich im Backend machen.

Finde ich nicht. Sonst hätte MS ja nicht mit den Um- und Irrwegen ODBC/DAO/RDO und ADO jetzt bei ADO.NET einen Ansatz gewählt der für jede DB eine Implementierung erfordert. Mit ADO wird nur der Zugriffsweg vereinheitlicht aber nicht (wie z.B. mit dem o.g. Pattern) die Eigenheiten der Datenbank versteckt.

[Elvis]

Nehmen wir so ein Package:

SQL-Code:

create or replace package EinPackage is
  type TTestCursor is ref cursor return Test%RowType;
  subtype TId is Test.Id%Type;

  function TestProc(id in TId) return TTestCursor;
end;

create or replace package body EinPackage is

function TestProc(id in TId) return TTestCursor is
  Result TTestCursor;
begin
  open Result for
    SELECT *
    FROM   Test t
    WHERE  t.Id = id;

  return Result;
end;

end;

Du könntest es als normales AdoCommand absetzen:

SQL-Code:

begin :cursor := EinPackage.TestProc(:Id); end;

Oder als SProc mit "EinPackage.TestProc" als CommandText.

Ich habe keinerlei IDE hier und meine Erfahrungen mit dem alten, OLe-basierten ADO halten sich in Grenzen, aber so hätte ich es versucht und hätte auch erwartet dass es geht. ;)
Es ist aber gut möglich, dass ADO keine Entsprechung für eine Cursor referenz hat.

Dann müsstest du ein wenig umbauen:

SQL-Code:

create or replace package EinPackage is
  type TTestEntry is Test%RowType;
  type TTestEntryList is table of  TTestEntry;
  subtype TId is Test.Id%Type;

  function TestProc(id in TId) return TTestEntryList;
end;

create or replace package body EinPackage is

function TestProc(id in TId) return TTestEntryList
is
  Result TTestEntryList;
begin
  SELECT * BULK COLLECT
  INTO   Result
  FROM   Test t
  WHERE  t.Id = id;

  return Result;
end;

end;

Ausführen könntest du es so:

SQL-Code:

SELECT *
FROM   table(EinPackage.TestProc(1111))

Das alte ADO und Oracle ist aber wirklich eine ziemlich zickige und nervige Kombi (Du weißt, dass Ora XYZ kann, aber du weißt auch, dass ADO zu blöd dafür ist ;) )

[Bernhard Geyer]

MS wird ADO so definiert haben bezüglich der Schnittstellen und Methoden damit es perfekt zum eigenen Server passt und auch Access damit angesprochen werden kann. Das evtl. manche features anderer DB's nicht gehen oder über ADO sehr umständlich umzusetzen ist wird MS so ziemlich egal sein.

[iKilledKenny]

Das Thema ist zwar schon ein bischen älter, für mich aber gerade brandaktuell.

Delphi 5, Delphi ADO Komponenten und Rückgabe eines Cursors von einem Oracle Package.

Leider kann ich nicht auf andere Komponenten ausweichen und die geposteten Vorschläge funktionieren nicht (unterschiedlichste Meldungen).

Hat jemand eine Lösung für das Problem?

[iKilledKenny]

Nach weiterem Googlen & Nachdenken habe ich nun den Trick gefunden:

Delphi-Quellcode:

var   conn:    TADOConnection;
      data:    TADODataSet;
      command: TADOCommand;
      param:   TParameter;
      proc:    TADOStoredProc;
      row:     Integer;
      name:    String;

begin
  conn := TADOConnection.Create (nil);
  conn.LoginPrompt := False;
  conn.ConnectionString :=
      'Provider=OraOLEDB.Oracle.1;'
      + 'Persist Security Info=True;'
      + 'Data Source=xxx;'
      + 'PwdChgDlg=0;'
      + 'Extended Properties="plsqlrset=1"';
  conn.Open ('xxx', 'xxx');


  name := Trim (Edit1.Text) + '%';

  data := TADODataSet.Create (nil);

  command := TADOCommand.Create (nil);
  command.Connection := conn;
  command.CommandType := cmdStoredProc;
  command.CommandText := 'CURSOR_TEST.GetUserIDsAndNames';
  command.Parameters.CreateParameter
      ('Name', ftString, pdInput, Length (name), name);

  StringGrid1.RowCount := 0;

  data.Recordset := command.Execute;
  while not data.Eof do
    begin
      row := StringGrid1.RowCount;
      StringGrid1.RowCount := StringGrid1.RowCount + 1;
      StringGrid1.Cells [0,row] := data.Fields [0].AsString;
      StringGrid1.Cells [1,row] := data.Fields [1].AsString;
      StringGrid1.Cells [2,row] := data.Fields [2].AsString;
      data.Next;
    end;

  data.Free;
  command.Free;

  conn.Close;
  conn.Free;
end;