I have a problem with a procedure that I will use to populate a table in the data warehouse. I'll try to exemplify.
I have three types defined as follows:
create or replace type room_t as object(
ID INTEGER,
n_seats INTEGER,
cinema ref cinema_t
) not instantiable not final;
/
create or replace type film_screening_t as object (
screen_date TIMESTAMP,
room ref room_t,
);
/
create or replace type ticket_t as object(
film ref film_screening_t,
purchase_date DATE,
price FLOAT,
n_ticket INTEGER
);
/
And the associated tables:
create table rooms of room_t(
ID primary key
);
/
create table film_screenings of film_screening_t(
room NOT NULL
);
create table tickets of ticket_t(
n_ticket primary key,
film NOT NULL
)
/
I also created a database link in another database that I called:
op_db_link
When I use this database link to get the id of the rooms like in the query below, everything is ok:
select deref(deref(film).room).id from tickets@op_db_link;
but when I use it in a procedure, I obtain only null values. The procedure is:
create or replace
PROCEDURE prova_procedure AS
room_id integer;
cursor c is
select deref(deref(film).room).id from tickets@op_db_link;
BEGIN
open c;
loop
fetch c into room_id;
dbms_output.put_line(user_id);
exit when c%notfound;
end loop;
end;
How can I solve this problem? I need to solve this problem in order to create an ETL procedure to populate the data warehouse
