Oracle & Dyn Cursor & Bulk SQL

[Robert_G]

Moin

Gegeben sei folgendes Problem: (bitte nicht über den tieferen Sinn von Code oder Bennenungen grübeln -> sind nur Beispiele )

In mehreren Usern gibt es mehrere Tabellen, die die Spalten a, b & c enthalten.
Diese 3 Spalten möchte ich so schnell wie möglich in PL/SQL-Tabellen einlessen -> bulk collect.
AABEER...
Die Tabellen/User heißen natürlich nicht gleich.


Ich habe eben die 3 möglichen Varianten ausprobiert, die mir eingefallen sind (bisher konnte ich die Kombi Bulk SQL & dyn. SQL gut umschiffen )

• Versuch1: Weak Typed Dyn. Cursor
  
  SQL-Code:

  create or replace procedure TestDynCur1
  (
    pOwner in varchar2
   ,pTable in varchar2
  ) is
    -- constants
    Lf char(1) := Chr(10);
  
    -- cursors
    type WeakTypedDynCur is ref cursor;
    DynCur WeakTypedDynCur;
  
    -- Bulk tables
    type ShortChrTab is table of varchar2(20);
    a ShortChrTab;
    b ShortChrTab;
    c ShortChrTab;
  begin
  
    open DynCur for
     'SELECT a' || Lf ||
     '      ,b' || Lf ||
     '      ,c' || Lf ||
     'FROM  ' || pOwner || '.' || pTable;
  
    fetch DynCur bulk collect
      INTO a, b, c;
  
    close DynCur;
  end;

  Der Kompiler meckert nix, aabeer...
  
  

  Zitat von Oracle:

  ORA-01001: invalid cursor, line 26

• Versuch2: Strong Typed Dyn. Cursor
  
  SQL-Code:

  create or replace procedure TestDynCur2
  (
    pOwner in varchar2
   ,pTable in varchar2
  ) is
    -- constants
    Lf char(1) := Chr(10);
  
    --"Alibi" cursor ;)
    cursor AlibiCur is
      SELECT a
            ,b
            ,c
      FROM   a;
  
    -- cursors
    type StrongTypedDynCur is ref cursor return AlibiCur%rowtype;
    DynCur StrongTypedDynCur;
  
    -- Bulk tables
    type ShortChrTab is table of varchar2(20);
    a ShortChrTab;
    b ShortChrTab;
    c ShortChrTab;
  begin
  
    open DynCur for 
     'SELECT a' || Lf || 
     '      ,b' || Lf || 
     '      ,c' || Lf || 
     'FROM  ' || pOwner || '.' || pTable;
  
    fetch DynCur bulk collect
      INTO a, b, c;
  
    close DynCur;
  end;

  
  

  Zitat von Oracle:

  Compilation errors for PROCEDURE ROBERTG.TESTDYNCUR2
  
  Error: PLS-00455: cursor 'DYNCUR' cannot be used in dynamic SQL OPEN statement
  Line: 27
  Text: open DynCur for

• Versuch 3: komplett dynamischer Block
  
  SQL-Code:

  create or replace procedure TestDynCur3
  (
    pOwner in varchar2
   ,pTable in varchar2
  ) is
    -- constants
    Lf char(1) := Chr(10);
  
    -- cursors
    -- keine lokalen cursor mehr nötig
  
    -- Bulk tables
    /*musste durch einen SQL Type ersetzt werden
      type ShortChrTab is table of varchar2(20); */
    a ShortChrTab;
    b ShortChrTab;
    c ShortChrTab;
  begin
    execute immediate
     'declare' || Lf ||
     '  cursor getVals is' || Lf ||
     '    SELECT a' || Lf ||
     '          ,b' || Lf ||
     '          ,c' || Lf ||
     '    FROM   ' || pOwner || '.' || pTable||';' || Lf ||
     'begin' || Lf ||
     '  open getVals;' || Lf ||
     '' || Lf ||
     '  fetch getVals bulk collect' || Lf ||
     '    INTO :a, :b, :c;' || Lf ||
     '  close getVals;' || Lf ||
     'end;'
      Using out a, out b, out c;
  
  end;

  Das funktioniert jetzt, aber es sieht irgendwie aus, wie der Code von einem totalen Oracle-Newbie

Wozu der Thread?
Ich hoffe jemand von euch wird mir gleich sagen "Mensch, warum machst du das denn so! Das geht doch ... viel einfacher."

Achso: Das ganze muss leider kompatibel ab Ora 8.174 sein

[Leuselator]

Hi Robert,

nicht wirklich kürzer und auch schlecht optimierbar für den Server (gilt aber für dynamic SQL allgemein), aber in MSSQL sähe es so aus - weis nicht, ob Du das in Ora nachbauen kannst:

SQL-Code:

--Beispieltabellen:
CREATE TABLE DeineSysTabelle ( FeldName varchar(255)
                             , TabellenName varchar(255))
INSERT INTO DeineSysTabelle (FeldName, TabellenName) VALUES ('Vorname', 'Personen')
INSERT INTO DeineSysTabelle (FeldName, TabellenName) VALUES ('Name'   , 'Personen')

CREATE TABLE DeineDatenTabelle( Vorname varchar(255)
                              , Name varchar(255))
INSERT INTO DeineSysTabelle (Vorname, Name) VALUES ('Hans' , 'Wurst')
INSERT INTO DeineSysTabelle (Vorname, Name) VALUES ('Peter', 'Pan'  )

  DECLARE @FELD varchar(255)
        , @TABELLE varchar(255)
        , @SQL nvarchar(4000) -- nimmt Statement für Cursordeklaration auf

  DECLARE @MeinDynCur Cursor -- dynamischer Cursor
  
      SET @SQL = 'SET @MeinDynCur = CURSOR FOR SELECT '

  DECLARE MeinStatCur
   CURSOR FOR
   SELECT FeldName
     FROM DeineSysTabelle

     OPEN MeinStatCur
    FETCH NEXT 
     FROM MeinStatCur 
     INTO @FELD    
        , @TABELLE
    WHILE @@Fetch_Status = 0 BEGIN -- durch Felder iterieren

        SET @SQL = @SQL+@FELD+', ' -- Feldliste ergänzen
      FETCH NEXT 
       FROM MeinStatCur 
       INTO @FELD    
          , @TABELLE
    END -- WHILE

      SET @SQL = @SQL+' 0' -- PseudoFeld für letztes Komma
      SET @SQL = @SQL+' FROM '+@TABELLE
      SET @SQL = @SQL+' WHERE Bedingung'
   -- CursorStatement fertig

   EXEC sp_execustesql(@SQL) -- legt Cursor an
   OPEN @MeinDynCur          -- öffnet ihn 
   ...

Gruß aus Berlin

[Bitworm]

Hi !

Wenn ich Deine Probleme richtig gedeute habe kann Dir sowas vielleicht helfen :

create table neue_table as
((select a,b,c from table_a)
union
(select a,b,c from table_b)
union
(select a,b,c from table_c))

[Robert_G]

Sorry ihr 2, ich habe mich wohl nicht genau genug ausgedrückt.

PL/SQL Tabelle <> DB Tabelle -> es ist eher so etwas wie eine Collection oder ein Array
Mein Problem ist die Kombi Bulk SELECT und dyn. Cursor.
Würde ich auf das Bulk SELECT verzichten könnte ich es so machen:

SQL-Code:

create or replace procedure TestDynCurNoBulk
(
  pOwner in varchar2
 ,pTable in varchar2
) is
  -- constants
  Lf char(1) := Chr(10);

  -- cursors
  type WeakTypedDynCur is ref cursor;
  DynCur WeakTypedDynCur;

  -- Bulk tables
  type ShortChrTab is table of varchar2(20) index by binry_integer;
  a ShortChrTab;
  b ShortChrTab;
  c ShortChrTab;
begin

  open DynCur for
   'SELECT a' || Lf ||
   '      ,b' || Lf ||
   '      ,c' || Lf ||
   'FROM  ' || pOwner || '.' || pTable;

  a(0) := null; 
  b(0) := null; 
  c(0) := null; 

  loop
    fetch DynCur
      INTO a(a.Last + 1)
          ,b(b.Last + 1)
          ,c(c.Last + 1);
    EXIT When DynCur%NotFound;
  end loop;
  close DynCur;
end;

Aber die ständigen Context switches zwischen PL/SQL und SQL würden wie eine angezogene Handbremse wirken.
Per Bulk DML bekomme ich mehrere 100.000 Datensätze in t < 70 ms. Wenn ich durch den Cursor iteriere würde es mehr als 1 Sekunde dauern!

Das Problem ist bei den ersten 2 Varianten im ersten Post, dass Oracle im voraus wissen muss, wie der Cursor "aussieht" um das Bulk SELECT vorbereiten zu können. Er schiebt dabei schließlich in einem Schritt die gesamte Abfrage in die 3 Collections.

Ich fürchte ich muss mit der 3. Variante leben, auch wenn dabei ein kompletter Block kompiliert werden muss -> das kostet wieder min. 10ms

[Leuselator]

SpeedJunky!