1. Willkommen im Forum für alle Datenbanken! Registriere Dich kostenlos und diskutiere über DBs wie Mysql, MariaDB, Oracle, Sql-Server, Postgres, Access uvm
    Information ausblenden

PL SQL: hat eine Tabelle eine Spalte mit bestimmten Kriterien?

Dieses Thema im Forum "Oracle" wurde erstellt von Babsi, 4 Juli 2019.

  1. Babsi

    Babsi Fleissiger Benutzer

    Hallo,
    ich bin neu bei PL SQL. ich versuche mit folgender Funktion arauszubekommen, ob eine tabell eine Spalte mit entsprechenden Kriterien hat, wenn ja soll 1 zurückgegeben werden wenn nicht 0, das Return des 0-wertes ist aber noch nicht eingebaut! Leider habe ich nur Fehlermeldungen, das Select arbeitet wie gewünscht nur wenn ich die Funktion aufrufe bekomme ich die Fehlermeldung:
    Fehler beim Start in Zeile: 21 in Befehl -
    f_CheckTables(EXTEND)
    Fehlerbericht -
    Unbekannter Befehl

    Und hier die Funktion,

    create or replace Function f_CheckTables(slotName in varchar2)
    return PLS_INTEGER
    as
    cursor myColumnName is
    SELECT column_name
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = 'slotName'
    AND column_name LIKE 'WID%' ;

    mayCol USER_TAB_COLS.column_name%TYPE;
    begin
    open myColumnName;

    FETCH myColumnName INTO mayCol;
    RETURN 1;
    dbms_output.put_line(mayCol);

    end;
    /
    Kann mir da jemand helfen?
     
  2. Babsi

    Babsi Fleissiger Benutzer

    O.K. exec fehlt ad beim Aufruf, aber dann

    Fehler beim Start in Zeile: 21 in Befehl -
    BEGIN f_CheckTables(EXTEND); END;
    Fehlerbericht -
    ORA-06550: Zeile 1, Spalte 21:
    PLS-00357: Table-, View- oder Sequence-Referenz 'EXTEND' in diesem Kontext nicht gültig
    ORA-06550: Zeile 1, Spalte 7:
    PL/SQL: Statement ignored
    06550. 00000 - "line %s, column %s:\n%s"
    *Cause: Usually a PL/SQL compilation error.
    *Action:
     
  3. castorp

    castorp Datenbank-Guru

    Die Verwendung des Parameters ist falsch. Die Bedingung TABLE_NAME = 'slotName' prüft auf eine Tabelle mit dem Namen "slotName", nicht auf eine Tabelle deren Name im Parameter steht. Das muss also TABLE_NAME = slotname sein (ohne die Anführungszeichen). Da Oracle Tabellenname GROSS geschrieben speichert, ist es auch besser den Parameter auch mittels UPPER() komplett groß zu schreiben.

    Um zu Testen ob die Spalte existiert, kannst Du die Variable auf NULL prüfen und dann 0 oder 1 zurückgeben:

    Code:
    create or replace Function f_CheckTables(slotName in varchar2)
      return PLS_INTEGER
    as
      cursor myColumnName is
      SELECT column_name
      FROM USER_TAB_COLS
      WHERE TABLE_NAME = upper(slotName)
      AND column_name LIKE 'WID%' ;
    
      mayCol USER_TAB_COLS.column_name%TYPE;
    begin
      open myColumnName;
      FETCH myColumnName
        INTO mayCol;
     
      if maycol is null then
        dbms_output.put_line('Nicht gefunden');
        return 0;
      else
        dbms_output.put_line(mayCol);
        RETURN 1;
      end if;
    end;
    /
    
    Die Fehlermeldung deutet darauf hin, dass Du die Funktion falsch verwendest. Funktionen kann man nur als Teil eines anderen SQL Statements - typischerweise eines SELECTs - verwenden. exec (oder execute) ist zum Ausführen von Prozeduren, nicht für Funktionen.

    Um einen String als Parameter zu übergeben muss der in Anführungszeichen stehen:

    Code:
    SELECT f_checktables('EXTEND')
    FROM dual;
    Die Tabelle DUAL is eine "Dummy Tabelle" die immer eine Zeile zurückliefert und in den Fällen verwendet werden kann, wenn man eigentlich das FROM weglassen möchte - was aber in Oracle nicht geht.
     
  4. castorp

    castorp Datenbank-Guru

    Ich würde die Funktion übrigens anders gestalten. Die Verwendung eines CURSORs bedeutet auch immer, dass man den CURSOR sauber schließen muss (was Deine Funktion nicht macht - und was ich auch vergessen habe in meiner Antwort).
    Besser ist ein einzelnes Statement ohne Cursor:
    Code:
    create or replace Function f_CheckTables(slotName in varchar2)
       return PLS_INTEGER
    as
      l_count integer;
    begin
    
      select count(*)
         into l_count
      FROM user_tab_cols
      WHERE table_name = upper(slotname)
      AND column_name LIKE 'WID%' ;
    
      if l_count > 0 then
         return 1;
      else
         return 0;
      end if;
    end;
    /
    dbms_output in einer Funktion macht normalerweise keinen Sinn, weil in dem Kontext in dem Funktionen verwendet werden, die Ausgaben typischerweise gar nicht interessiert.
     
  5. Babsi

    Babsi Fleissiger Benutzer

    Hallo Castorp,

    Super!!!

    Du hast mir sehr geholfen, vielen, vielen Dank:):).
    Die Erläuterungen sind Super!!!

    Jetzt funktioniert es :D

    P.S. Ich melde mich später noch mal?war erst der Anfang meiner Aufgabe:rolleyes:
     
  6. Babsi

    Babsi Fleissiger Benutzer

    Ah, das hat sich jetzt überschnitten,
    ja ich bau das dann wohl um, schau mir das aber erst mal an,
    um zu verstehen
    Echt, tausend Dank!!!
     
  7. Babsi

    Babsi Fleissiger Benutzer

    Hallo,

    ich habe das nun doch wieder umgebaut, ich muss irgendiwe an den Spaltennamen der Tabelle ran.
    Wenn ich den dann habe, muss ich prüfen, ob der Spaltenname mit WID endet, wenn ja ist gut, wenn nein, soll dieser Spalte erstellt werden.
    Und dann soll auch nocvh geprüft werden, ob es eine entsprechende Seuenz zu dieser Spalte gibt, wenn ja, alles gut wenn nein, ebefalls erstellen.

    Tja, ich muss mal sehen wie ich das hinbekomme:

    mein jetziger Code sieht so aus:
    create or replace Function f_CheckTables(slotName in varchar2)
    return PLS_INTEGER
    as

    v_myColumn varchar2(30);
    v_mySeqName varchar2(30);
    v_newColumn varchar2(30);
    v_newSeq Varchar2(39);

    cursor c_myColumnName is
    SELECT column_name
    INTO v_myColumn
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = upper(slotName)
    AND column_name LIKE 'WID%' ;

    mayCol USER_TAB_COLS.column_name%TYPE;

    cursor c_mySeqName is
    select SEQUENCE_NAME
    from user_sequences
    WHERE SEQUENCE_NAME LIKE 'v_myColumn';

    mySecName user_sequences.SEQUENCE_NAME%Type;


    begin
    open c_myColumnName;
    FETCH c_myColumnName
    INTO mayCol;

    open c_mySeqName;
    FETCH c_mySeqName
    into mySecName;
    if maycol is null and mySecName is null then

    if maycol is null then
    SELECT column_name
    INTO v_newColumn
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = upper(slotName);
    end if;
    --jetzt neue splate erstellen

    if mySecName is null then
    execute IMMEDIATE 'Create Sequence column_name||wasdanochdransoll START WITH 1000 INCREMENT BY 1';
    end if;
    else

    RETURN 1;
    end if;

    close c_myColumnName;
    Close c_mySeqName;

    end;
    /
    Jetzt bekomme ich immer die Fehlermeldung:
    ORA-01422: Exakter Abruf gibt mehr als die angeforderte Zeilenzahl zurück
    ORA-06512: in "BI_ADMIN.F_CHECKTABLES", Zeile 39
    01422. 00000 - "exact fetch returns more than requested number of rows"
    *Cause: The number specified in exact fetch is less than the rows returned.
    *Action: Rewrite the query or change number of rows requested

    Was heisst denn das nun, dass er mehr als eine Zeile liefert, der Cursor?
     
  8. castorp

    castorp Datenbank-Guru

    LIKE 'WID%' testet aber ob der Spaltenname mit WID anfängt.
    Der Test ob ein Name mit WID endet muss: LIKE '%WID' sein.

    Du prüfst aber nur auf einen Teil des Namens. Was ist wenn die Tabelle mehrere Spalten hat die mit WID enden?
    Z.B.: BLA_WID, BLUB_WID, BLUBBER_WID? Soll dann eine neue Spalte BLABLUB_WID angelegt werden?


    Im Grunde verstehe ich den Test auf "endet mit WID" nicht. Da Du beim Hinzufügen der neuen Spalte einen Namen vergeben musst, musst Du ja wissen wie die Spalte heißen soll die da hinzugefügt werden soll. Warum prüfst Du dann nicht gleich auf diesen Namen?


    Das klappt in Oracle aber nur mit Namenskonventionen da es keine "feste" Verbindung zwischen einer Sequence und einer Spalte gibt.

    Auch hier wieder der Fehler: Variablen dürfen nicht in Anführungszeichen stehen. Der Cursor sucht nach Sequences die v_myColumn heißen. Richtig ist WHERE SEQUENCE_NAME LIKE v_mycolumn (ohne die Anführungszeichen)


    Schuld daran ist dieser Teil:
    Code:
    SELECT column_name
      INTO v_newColumn
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = upper(slotName);
    Die Abfrage liefert alle Spaltennamen der Tabelle zurück - Du versucht aber das Ergebnis in einer Variable zu speichern. Das kann nicht funktionieren. Was bezweckst Du denn mit der Abfrage überhaupt?

    Ich würde das so machen:
    Code:
    create or replace Function f_CheckTables(slotName in varchar2)
      return PLS_INTEGER
    as
      l_count integer;
     
      -- Der Name der Spalte der hinzugefügt werden soll.
      l_column := 'BLUBBER_WID';
    
    begin
      -- teste ob es wenigstens eine Spalte mit WID endet
      SELECT count(*)  
         INTO l_count
      FROM USER_TAB_COLS
      WHERE TABLE_NAME = upper(slotName)
        AND column_name = l_column;
     
      -- Die Spalte wurde nicht gefunden - also hinzufügen
      IF l_count = 0 THEN
        EXECUTE IMMEDIATE 'ALTER TABLE '||slotname||' ADD COLUMN '||l_column||' VARCHAR2(100)';
      END IF;
    
      SELECT count(*)
        INTO l_count
      FROM user_sequences
      WHERE sequence_name = l_column;
     
      IF l_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE SEQUENCE '||l_column;
      END IF;
      RETURN 1;
    end;
    /
    
     
  9. Babsi

    Babsi Fleissiger Benutzer

    Hallo Castorp,

    O.K. wieder den fehler mit den 'variable'. Danke
    Also '%_WID'
    Ich so dann so: AND column_name LIKE Column_Name||'%_WID' ;

    Wenn ich aber nun folgendes ausprobiere:
    WHERE SEQUENCE_NAME LIKE BI_ADMIN_||v_myColumn||WID_SEQ;

    Dann moniert er "WID_SEQ": ungültiger Bezeichner

    Ich will folgendes erreichen, ich möchte schauen, ob es irgendwo in einer Tabelle eine Spalte gibt, die nicht die Endung _WID hat, wernn das der Fall ist, soll sie angelegt werden. das versuche ich jetzt mit Column_Name||'%_WID'
    Obwohl es ja auch heißen müsste not LIKE Column_Name||'%_WID'
    Genauso mit der Sequenz, wenn es eine gibt, mit BI_ADMIN_Splatenname_WID alles gut, sonst erstellen.

    endet mit WID" nicht. Da Du beim Hinzufügen der neuen Spalte einen Namen vergeben musst, musst Du ja wissen wie die Spalte heißen soll die da hinzugefügt werden soll. Warum prüfst Du dann nicht gleich auf diesen Namen?
    Ja, das ist ja richtig...
     
  10. Babsi

    Babsi Fleissiger Benutzer

    Hallo noch mal,

    vielen Dank für das Statement.
    l_column := 'BLUBBER_WID';
    Das weiss ich ja nicht wei die heißen soll , der Name setzt sich aus den Spaltennamen und diesem WID zusammen. Darum veruche ich ja mit dem Cursor den Spaltennamen(Erste Spalte einer jeden Tabelle!) in die Variable zu schreiben und deswegen prüfe ich auch auf _WID, wenn er nichts findet, soll er ja den Spaltennamen nehmen, WID hinten anfügen und dies als neuen Spaltennamen nehmen und in die Tabelle einfügen.
     
  11. castorp

    castorp Datenbank-Guru

    Ich kapier's nicht. Wenn keine Spalte mit _WID vorhanden ist, dann willst Du einfach "den ersten" Spaltenname verwenden, _WID anhängen und eine neue Spalte erzeugen? Warum die "erste" Spalte, warum nicht die sagen wir "fünfte"? Die Reihenfolge von Spalten in einer Tabelle spielt eigentlich keine Rolle. Was ist an der "ersten" so besonders - und wie kannst Du Dir sicher sein, dass immer die "erste" die Spalte ist die verwendet werden soll?
     
  12. Babsi

    Babsi Fleissiger Benutzer

    So wie folg habe ich es geändert:

    create or replace Function f_CheckTables(slotName in varchar2)
    return PLS_INTEGER

    as

    v_ColumnName Varchar2(30);
    v_Seqname integer;

    cursor myColumnName is
    SELECT column_name
    Into v_ColumnName
    FROM USER_TAB_COLS
    WHERE TABLE_NAME = 'slotName'
    AND column_name LIKE 'WID%' ;

    mayCol USER_TAB_COLS.column_name%TYPE;

    Cursor c_Sequence is
    SELECT SEQUENCE_NAME
    Into v_Seqname
    FROM DBA_SEQUENCES
    WHERE SEQUENCE_NAME LIKE mayCol;

    my_seq DBA_SEQUENCES.SEQUENCE_NAME%Type ;


    begin
    open myColumnName;
    FETCH myColumnName
    INTO mayCol;

    open c_Sequence;
    FETCH c_Sequence
    into my_seq;

    if maycol is null and my_seq is null then

    if maycol is null then
    -- Spalte in Tabelle anlegen
    return 2;
    end if;

    if my_seq is null then
    Return 3;
    EXECUTE IMMEDIATE 'CREATE sequence maycol||my_seq start with 1 increment by 1';

    end if;

    -- Hier jetzt die Spalte erstellen und die sequence


    else
    RETURN 1;
    end if;
    close myColumnName;
    close c_Sequence;
    end;
    /


    Und das läuft jetzt auch durch, zumindest bekomme ich keine Fehlermeldung mehr
     
  13. Babsi

    Babsi Fleissiger Benutzer

    Hallo,
    Ich kapier's nicht. Wenn keine Spalte mit _WID vorhanden ist, dann willst Du einfach "den ersten" Spaltenname verwenden, _WID anhängen und eine neue Spalte erzeugen?
    Ja, genau
    ...Warum die "erste" Spalte, warum nicht die sagen wir "fünfte"?

    Das ist einfach ne Übungsaufgabe, die ich umsetzen soll. Obwohl das dann auch später benutzt werden soll.
    Ist eben der Standart hier bei uns und diese _WID
    ...kannst Du Dir sicher sein, dass immer die "erste" die Spalte ist die verwendet werden soll

    Das soll wohl auch so sein.
     
  14. castorp

    castorp Datenbank-Guru

    Komische Standards habt ihr.
    Nach Deiner Beschreibung würde ich das so machen:
    Code:
    create or replace Function f_checktables(slotName in varchar2)
      return PLS_INTEGER
    as
      l_count integer;
      l_first_column  varchar(128);
      l_column_name   varchar(128);
      l_sequence_name varchar(128);
     
    begin
      -- teste ob es wenigstens eine Spalte mit WID endet
      SELECT count(*) 
         INTO l_count
      FROM USER_TAB_COLS
      WHERE TABLE_NAME = upper(slotname)
        AND column_name = l_column;
     
      -- Keine Spalte mit _WID am Ende vorhanden - hole den Namen der ersten Spalte der Tabelle.
      IF l_count = 0 THEN
        SELECT column_name
           INTO l_column
        WHERE TABLE_NAME = upper(slotname)
        ORDER BY column_id  --<< nach der Reihenfolge sortieren
        FETCH FIRST 1 ROWS ONLY; --<< nur eine Zeile zurückgeben
        
        -- neuen Namen erzeugen
        l_column_name := l_column ||'_WID';
      
        -- und Spalte hinzufügen.
        EXECUTE IMMEDIATE 'ALTER TABLE '||slotname||' ADD COLUMN '||l_column||' VARCHAR2(100)';
      END IF;
    
      -- keine Ahnung ob ich das richtig verstanden habe.
      l_sequence_name := 'BI_ADMIN_||l_column_name;
     
      SELECT count(*)
        INTO l_count
      FROM user_sequences
      WHERE sequence_name = l_sequence_name;
    
      IF l_count = 0 THEN
        EXECUTE IMMEDIATE 'CREATE SEQUENCE '||l_sequence_name;
      END IF;
     
      RETURN 1;
    end;
    /
    
     
  15. Babsi

    Babsi Fleissiger Benutzer

    Hallo Castrop,

    ich schaue, vielen Danke für deien Mühe...
    Melde mich auf jden Fall..
     
Die Seite wird geladen...

Diese Seite empfehlen

  1. Diese Seite verwendet Cookies. Wenn du dich weiterhin auf dieser Seite aufhältst, akzeptierst du unseren Einsatz von Cookies.
    Information ausblenden