how to populate database using procedures

Question

I have about 15 different Tables filled with different data and different entity relationships.

I need to create a script which will populate my database with the content of those tables.

After script is finished, i run it in cmd, using sqlplus and later START path to file

i have two different sql files, one named db_spec.sql and another db_body.sql.

In my db_body.sql i've created a procedure to store data from two tables which have 1:N relationship.

First table

CREATE TABLE LOCATION (
ID_LOCATION INTEGER NOT NULL,
LOCATION_NAME VARCHAR2 (20) NOT NULL,
POSTCODE INTEGER NOT NULL
);

ALTER TABLE LOCATION
ADD (CONSTRAINT PK_LOCATION PRIMARY KEY (ID_LOCATION));

Second table

CREATE TABLE ADDRESS (
ID_ADDRESS INTEGER NOT NULL,
STREET VARCHAR2 (20) NOT NULL,
HOUSE_NUMBER INTEGER NOT NULL,
FK_ID_LOCATION INTEGER NOT NULL
);

ALTER TABLE ADDRESS
ADD (CONSTRAINT PK_ADRESS PRIMARY KEY (ID_ADRESS));

ALTER TABLE ADRESS
ADD (CONSTRAINT FK_ADRESS_ID_LOCATION FOREIGN KEY
(FK_ID_LOCATION) REFERENCES LOCATION(ID_LOCATION));

Now i need to populate them using data. Let's say

LOCATION_NAME = "London" POSTOCDE = "394505" ...and so on

I've created this script, but as i run it, nothing shows up, so there's obviously some error in it.

db_spec.sql script

CREATE OR REPLACE PACKAGE apartment AS
PROCEDURE fill_location(location_number NUMBER);
PROCEDURE fill_address(number_of_addresses NUMBER);
END apartment;

db_body.sql script

SET SERVEROUTPUT ON
SET LINESIZE 400
SET TIMING ON
CREATE OR REPLACE PACKAGE BODY address AS
PROCEDURE fill_location(location_number NUMBER) IS
    p_location_name VARCHAR2(20);
    p_postcode NUMBER (10,2);
BEGIN
    FOR num IN 1..location_number LOOP
        p_location_name := 'Location';
        p_postcode := dbms_random.value(1000,9600);
        p_postcode := p_postcode ||' '|| TO_CHAR(num);
        INSERT INTO LOCATION (ID_LOCATION, LOCATION, POSTCODE)
        VALUES (num, p_location_name, p_postcode);
        dbms_output.put_line(num);
    END LOOP;
END fill_location;

PROCEDURE fill_address(number_of_adresses NUMBER)IS
    p_street_name VARCHAR(20);
    p_house_number NUMBER (10,2);
    p_id_address NUMBER(10);

    CURSOR data IS
        SELECT ID_LOCATION
        FROM LOCATION;
BEGIN
    FOR num_loop IN data LOOP
        FOR num IN 1..number_of_adresses LOOP
        p_street_name := 'Ulica';
        p_house_number := dbms_random.value(1,99);
        p_street_name := p_street_name ||' '|| TO_CHAR(num);
        SELECT NVL(MAX(p_id_address)+1,1)
        INTO p_id_address
        FROM ADDRESS;
        INSERT INTO ADDRESS (ID_ADDRESS, FK_ID_LOCATION, STREET, HOUSE_NUMBER)
        VALUES (p_id_address, num_loop.ID_LOCATION, p_street_name, p_house_number);
        dbms_output.put_line(num_loop.ID_LOCATION);
        END LOOP;
    END LOOP;           
END fill_address;
END;
SHOW ERRORS;

Can you guys please help me fix the problem so the code will run and work fine? Any input is appreciated!

Btw oprema_stanovanja.polni_kraj = address.fill_location

Answer 1

Firstly, the package posted doesn't compile without errors.

You would need to fix the following first:

1. Terminate FOR LOOP statements with END LOOP
2. Terminate BEGIN of procedures with END <procedure_name_here>
3. PL/SQL variable names need to be different than the actual column name to avoid ambiguity in the INSERT statement. [ You can't have LOCATION the column name and PL/SQL variable with the same name. Oracle cannot resolve which is to be used where in the INSERT]

To get you started, I have fixed the fill_location procedure below. Proceed similarly for the other procedure.

create or replace package body apartment as
procedure fill_location(location_number number) is
p_location_name varchar2(20);
p_postcode number(10,2);
begin 
    for num in 1.. location_number loop
        p_location_name := 'location';
        p_postcode := dbms_random.value(1000,9600);
        p_location_name := p_location_name ||' '|| to_char(num);
        insert into location (id_location, location_name, postcode)
            values (num, p_location_name, p_postcode);
        dbms_output.put_line(num);
    end loop;
end fill_location;

Fix the above errors/suggestions until you get 'PL/SQL successfully compiled`. If you get 'compiled with errors' use "show errors" to find and fix any other errors.

Answer 2

Unless I'm missing it... COMMIT;
Based on the screenshot you issued, add a / to the end of your body and procedure definitions.