[SQL] Insert data into table using cursor in Oracle

I am learning pl/sql and I have an exercise to use cursor and procedure or function. The exercise requires to insert data into a table (this will be a support/view table) using a cursor that takes data from other three tables.

Find tables below:

CREATE TABLE t_csc (
nr_csc NUMBER NOT NULL,
nr_client NUMBER(10) NOT NULL,
cd_product NUMBER(10) NOT NULL,
cd_employee NUMBER(10),
ds_detail_csc CLOB,
dt_open_csc DATE,
hr_open_csc NUMBER(2),
dt_service DATE,
hr_service NUMBER(2),
nr_time_total_csc NUMBER(10),
ds_detail_retorn_csc CLOB,
tp_csc CHAR(1) NOT NULL,
st_csc CHAR(1),
nr_index_satisfaction NUMBER(2));


CREATE TABLE t_product (
cd_product NUMBER(10) NOT NULL,
cd_category NUMBER NOT NULL,
nr_cd_bar_prod VARCHAR2(50),
ds_product VARCHAR2(80) NOT NULL,
vl_unitary NUMBER(8, 2) NOT NULL,
tp_pack VARCHAR2(15),
st_product CHAR(1),
vl_perc_profit NUMBER(8, 2),
ds_complete_prod VARCHAR2(4000) NOT NULL);


CREATE TABLE t_category_prod (
cd_category NUMBER NOT NULL,
tp_category CHAR(1) NOT NULL,
ds_category VARCHAR2(500) NOT NULL,
dt_start DATE,
dt_end DATE,
st_category CHAR(1) NOT NULL);


View table:

CREATE TABLE t_ocurrence (
nr_ocurrence_csc NUMBER NOT NULL,
dt_open_csc DATE,
hr_open_csc NUMBER(2),
ds_type_classific_csc VARCHAR2(30) NOT NULL,
ds_index_satisfac_atd_csc VARCHAR2(30),
cd_category_prod NUMBER NOT NULL,
nm_type_category_prod VARCHAR2(25) NOT NULL,
ds_category_prod VARCHAR2(500) NOT NULL,
cd_product NUMBER(10),
ds_product VARCHAR2(80) NOT NULL,
tp_pack VARCHAR2(15));


I tried this code without success:

DECLARE
csc_ocurr t_ocurrence%ROWTYPE; -- Declarando uma variável que vai conter T_MC_SGV_OCORRENCIA
CURSOR cur_search IS -- Cria um cursor chamado cur_search do tipo SELECT
SELECT CSC.nr_CSC, CSC.dt_open_csc, CSC.hr_open_csc, CSC.tp_csc, CSC.nr_index_satisfaction, CATPROD.cd_category,
CATPROD.tp_category, CATPROD.ds_category, PROD.cd_product, PROD.ds_product, PROD.tp_pack, PROD.vl_unitary, PROD.vl_perc_profit
FROM t_csc CSC, t_category_prod CATPROD, t_product PROD
WHERE CATPROD.cd_category = PROD.cd_category AND PROD.cd_product = CSC.cd_product;

BEGIN
OPEN cur_search;
LOOP
FETCH cur_search INTO csc_ocurr.nr_ocurrence_csc, csc_ocurr.dt_open_csc, csc_ocurr.hr_open_csc,
csc_ocurr.ds_type_classific_csc, csc_ocurr.ds_index_satisfac_atd_csc, csc_ocurr.cd_category_prod, csc_ocurr.nm_type_category_prod,
csc_ocurr.ds_category_prod, csc_ocurr.cd_product, csc_ocurr.ds_product, csc_ocurr.tp_pack; -- 20
EXIT WHEN cur_search%notfound;
INSERT INTO t_ocurrence VALUES (csc_ocurr.nr_ocurrence_csc, csc_ocurr.dt_open_csc, csc_ocurr.hr_open_csc,
csc_ocurr.ds_type_classific_csc, csc_ocurr.ds_index_satisfac_atd_csc, csc_ocurr.cd_category_prod, csc_ocurr.nm_type_category_prod,
csc_ocurr.ds_category_prod, csc_ocurr.cd_product, csc_ocurr.ds_product, csc_ocurr.tp_pack);
END LOOP;
CLOSE cur_search;
END;
/

Continue reading...