SQLPLUS returns only 1 and 0 numbers using dbms_output.put_line

293 Views Asked by At

http://shrani.si/f/S/52/2l8ZPmn7/stack.jpg

I have problem in a M:1 relation. I have entity named "zaposleni" (M) and entity named "zapori" (1). The problem appears when I want to execute my procedure. Error says that my parent key wasn't found, even tho I have filled my zapori entity with 20 examples. When I use "dbms_output.put_line" to see what procedure returns (before INSERT statement), procedure returns only 1 and 0, sometimes 2 or 4. I know that procedure should have returned numbers form 1 to 20.

SQL looks like this: http://shrani.si/f/45/Nt/ig2Gi8t/stack2.jpg

Now I need to know why this happens and I would be thankful if someone could resolve this or atleast help me with some advices.

SQL Code:

CREATE OR REPLACE PACKAGE BODY ui AS    
PROCEDURE polni_zaposleni(stevilo_zaposleni NUMBER) IS

    id_za NUMBER(10);   

    ime VARCHAR2(20);
    priimek VARCHAR2(20);
    del_doba VARCHAR2(20);
    id_z NUMBER(10);
    id_z_count number:= 0;

BEGIN
    select count(1)
    into id_z_count
    from zaporniki;

        FOR st IN 1..stevilo_zaposleni LOOP

            id_z := dbms_random.value(1, id_z_count);

            ime := 'Ime';
            priimek := 'Priimek';
            del_doba := 'Del_doba';

            ime := ime||' '|| TO_CHAR(st);
            priimek := priimek ||' '|| TO_CHAR(st);
            del_doba := del_doba ||' '|| TO_CHAR(st);

            dbms_output.put_line(id_z);

            --INSERT INTO ZAPOSLENI (ID_ZA, IME, PRIIMEK, DEL_DOBA, ID_Z)
            --VALUES (st, ime, priimek, del_doba, id_z);

            --dbms_output.put_line(st);


        END LOOP;
END;    

END ui;

1

There are 1 best solutions below

2
On

It appears to be working fine on SQL Fiddle.

Oracle 11g R2 Schema Setup:

CREATE TABLE zaporniki ( a ) AS
SELECT LEVEL
FROM   DUAL
CONNECT BY LEVEL <=20
/

CREATE TABLE ZAPOSLENI (
  ID_ZA    NUMBER,
  IME      VARCHAR2(20),
  PRIIMEK  VARCHAR2(20),
  DEL_DOBA VARCHAR2(20),
  ID_Z     NUMBER
)
/

CREATE OR REPLACE PACKAGE ui
AS
  PROCEDURE polni_zaposleni(stevilo_zaposleni NUMBER);
END ui;
/

CREATE OR REPLACE PACKAGE BODY ui AS

PROCEDURE polni_zaposleni(stevilo_zaposleni NUMBER)
IS
    id_za      NUMBER(10);   
    ime        CONSTANT VARCHAR2(20) := 'Ime';
    priimek    CONSTANT VARCHAR2(20) := 'Priimek';
    del_doba   CONSTANT VARCHAR2(20) := 'Del_doba';
    id_z       NUMBER(10);
    id_z_count NUMBER;
BEGIN
  select count(1)
  into id_z_count
  from zaporniki;

  FOR st IN 1..stevilo_zaposleni LOOP
    id_z := dbms_random.value(1, id_z_count);

    dbms_output.put_line(id_z);

    INSERT INTO ZAPOSLENI (ID_ZA, IME, PRIIMEK, DEL_DOBA, ID_Z)
    VALUES (
      st,
      ime||' '|| TO_CHAR(st),
      priimek ||' '|| TO_CHAR(st),
      del_doba ||' '|| TO_CHAR(st),
      id_z
    );

    --dbms_output.put_line(st); 
  END LOOP;
END;    

END ui;
/

Query 1:

SELECT * FROM zaporniki

Results:

|  A |
|----|
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
|  7 |
|  8 |
|  9 |
| 10 |
| 11 |
| 12 |
| 13 |
| 14 |
| 15 |
| 16 |
| 17 |
| 18 |
| 19 |
| 20 |

Query 2:

BEGIN
  ui.polni_zaposleni(10);
END;

Results:

Query 3:

SELECT * FROM ZAPOSLENI

Results:

| ID_ZA |    IME |    PRIIMEK |    DEL_DOBA | ID_Z |
|-------|--------|------------|-------------|------|
|     1 |  Ime 1 |  Priimek 1 |  Del_doba 1 |    8 |
|     2 |  Ime 2 |  Priimek 2 |  Del_doba 2 |   16 |
|     3 |  Ime 3 |  Priimek 3 |  Del_doba 3 |    7 |
|     4 |  Ime 4 |  Priimek 4 |  Del_doba 4 |   20 |
|     5 |  Ime 5 |  Priimek 5 |  Del_doba 5 |   11 |
|     6 |  Ime 6 |  Priimek 6 |  Del_doba 6 |   11 |
|     7 |  Ime 7 |  Priimek 7 |  Del_doba 7 |   12 |
|     8 |  Ime 8 |  Priimek 8 |  Del_doba 8 |    8 |
|     9 |  Ime 9 |  Priimek 9 |  Del_doba 9 |   20 |
|    10 | Ime 10 | Priimek 10 | Del_doba 10 |    2 |