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