PLS-00201: identifier 'COL1.POTO' must be declared

Question

Hi I have problem with variable:

Here is my code

CREATE OR REPLACE Procedure WstawPotomka(  wPesel IN    Osoba.Pesel%TYPE, wImie IN Osoba.Imie%Type ,
wNazwisko IN    Osoba.Nazwisko%TYPE,  wDataUr IN Osoba.Data_Urodzenia%Type,    wMiejsceUr IN Osoba.Miejsce_Urodzenia%Type, PeselMatka IN    Osoba.Matka%Type, PeselOjciec IN Osoba.Ojciec%Type) 
AS Cursor Potomek    
IS SELECT 
o.pesel as Col1, b.pesel AS Col2, c.pesel AS Col3 FROM    OSOBA o 
CROSS JOIN Osoba b 
CROSS JOIN Osoba c; 
poto Potomek%ROWTYPE;    
BEGIN 
OPEN Potomek; 
LOOP 
FETCH Potomek INTO poto; 
EXIT WHEN    Potomek%NotFound; 
IF Col1.poto != wPesel THEN    
IF COL2.poto = PeselMatka and COL3.poto = PeselOjciec THEN 
INSERT INTO Osoba Values (wPesel, wImie, wNazwisko, wDataUr, wMiejsceUr,    null, PeselMatka, PeselOjciec, null); 
END IF; 
END IF; 
END LOOP; 
Close Potomek; 
END;

The Osoba table:

It's my first post so don't be so angry for formatting.

Please stop using `OPEN` / `FETCH INTO` / `EXIT WHEN` / `CLOSE`. Use the much simpler cursor FOR LOOP instead, introduced about 20 years ago. — Codo, Jan 29 '22 at 22:53
And if your stored procedure should be performant, replace the entire loop with a single `INSERT INTO ... SELECT` statement. — Codo, Jan 29 '22 at 22:55

Codo · Accepted Answer · 2022-01-30T21:40:05.700

This can be done with a single efficient SQL statement.

I highly doubt that the SELET statement and the general logic in your code is correct. CROSS JOINs create a cartesian product and are always suspect. But even worse: The inserted rows only take data from the input parameters and thus are all the same. The SELECT statement only determines the number of rows.

CREATE OR REPLACE PROCEDURE WstawPotomka(
    wPesel      IN  Osoba.Pesel%TYPE,
    wImie       IN  Osoba.Imie%TYPE ,
    wNazwisko   IN  Osoba.Nazwisko%TYPE, 
    wDataUr     IN  Osoba.Data_Urodzenia%TYPE,
    wMiejsceUr  IN  Osoba.Miejsce_Urodzenia%TYPE,
    PeselMatka  IN  Osoba.Matka%TYPE,
    PeselOjciec IN  Osoba.Ojciec%TYPE
) 
AS
BEGIN
    INSERT INTO Osoba
    SELECT wPesel, wImie, wNazwisko, wDataUr, wMiejsceUr, NULL, PeselMatka, PeselOjciec, NULL
    FROM    OSOBA o 
    CROSS JOIN Osoba b 
    CROSS JOIN Osoba c
    WHERE o.pesel <> wPesel
      AND b.pesel = PeselMatka
      AND c.pesel = PeselOjciec;
END;

I think you forgot "BEGIN" ater "AS". Thank you for all your replies. It was very helpful. — Lucjan, Jan 30 '22 at 20:59

PLS-00201: identifier 'COL1.POTO' must be declared

1 Answers1