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

B

Babsi

SQL-Guru
Beiträge
122
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?
 
Werbung:
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:
 
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.
 
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.
 
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:
 
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!!!
 
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?
 
Babsi schrieb:
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.
Zum Vergrößern anklicken....
LIKE 'WID%' testet aber ob der Spaltenname mit WID anfängt.
Der Test ob ein Name mit WID endet muss: LIKE '%WID' sein.

Babsi schrieb:
wenn ja ist gut, wenn nein, soll dieser Spalte erstellt werden.
Zum Vergrößern anklicken....
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?

Babsi schrieb:
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.
Zum Vergrößern anklicken....
Das klappt in Oracle aber nur mit Namenskonventionen da es keine "feste" Verbindung zwischen einer Sequence und einer Spalte gibt.

WHERE SEQUENCE_NAME LIKE 'v_myColumn'
Zum Vergrößern anklicken....
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)

Babsi schrieb:
Was heisst denn das nun, dass er mehr als eine Zeile liefert, der Cursor?
Zum Vergrößern anklicken....
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;
/
 
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...
 
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.
 
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?
 
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
 
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.
 
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;
/
 
Werbung:
Zurück
Oben