oracle - PLS-00225: subprogram or cursor 'CHR' reference is out of scope -

i make procedure in plsql return error know post code , error snap shot here

    create or replace procedure chr.att_insert_test    cursor att          select   emp.employee_id employee_id,          io.checktype checktype,          io.machine_num machine_num,          io.att_id att_id,           io.swipe_date swipe_date,          io.swipe_time swipe_time     inout_live_machine_test io, chr_emgt_employee emp    emp.employee_code = io.employee_code  , io.att_id not in (select   att_id_ref                                    chr_ta_emp_swipe_in_out                                   io.att_id = att_id_ref); begin    in att    loop       insert chr_ta_emp_swipe_in_out (employee_id,                                            swipe_date,                                            swipe_time,                                            swipe_id,                                            swipe_type,                                            created_by,                                            creation_date,                                            client_ip)         values   (i.employee_id,                   to_date (i.swipe_date, 'mm/dd/yyyy'),                   i.swipe_time,                   chr_ta_swipe_in_out_seq.nextval,                   i.checktype,                   i.employee_id,                   to_date (i.swipe_date, 'mm/dd/yyyy'),                   '192.168.0.71'                   );    end loop; commit; end; / 

this procedure make subquery time time return me kind of error. think error show issue regarding alias define alias each column read artical code still stuck

pls-00402: alias required in select list of cursor avoid duplicate column names

this table structure

create table chr.chr_ta_emp_swipe_in_out (   employee_id       number(10)                  not null,   swipe_date        date                        not null,   swipe_time        date                        not null,   swipe_id          number(10)                  not null,   swipe_type        varchar2(1 byte),   created_by        number(10),   creation_date     date,   last_updated_by   number(10),   last_update_date  date,   is_manual         varchar2(1 byte),   reason_lkp        number(10),   client_ip         varchar2(50 byte),   is_requested      varchar2(1 char),   terminal_id       varchar2(50 byte),   att_id_ref        number )  create table chr.chr_emgt_employee (   employee_id                number(10)         not null,   employee_code              varchar2(30 byte)  not null   )    create table chr.inout_live_machine_test  (   att_id         number,   userid         number,   employee_code  number,   sensorid       number,   machine_num    varchar2(20 byte),   checktime      varchar2(20 byte),   checktype      varchar2(20 byte) ) 

your procedure has no mistakes. problem in table "inout_live_machine_test". has missing swipe_date & swipe_time columns. see below working demo.

created table both columns:

create table inout_live_machine_test  (   att_id         number,   userid         number,   employee_code  number,   sensorid       number,   swipe_date        date                        not null,   swipe_time        date                        not null,   machine_num    varchar2(20 byte),   checktime      varchar2(20 byte),   checktype      varchar2(20 byte) ) 

procedure compiled :

create or replace procedure att_insert_test    cursor att          select   emp.employee_id employee_id,          io.checktype checktype,          io.machine_num machine_num,          io.att_id att_id,           io.swipe_date swipe_date,          io.swipe_time swipe_time     inout_live_machine_test io, chr_emgt_employee emp    emp.employee_code = io.employee_code  , io.att_id not in (select   att_id_ref                                    chr_ta_emp_swipe_in_out                                   io.att_id = att_id_ref); begin    in att    loop       insert chr_ta_emp_swipe_in_out (employee_id,                                            swipe_date,                                            swipe_time,                                            swipe_id,                                            swipe_type,                                            created_by,                                            creation_date,                                            client_ip)         values   (i.employee_id,                   to_date (i.swipe_date, 'mm/dd/yyyy'),                   i.swipe_time,                   chr_ta_swipe_in_out_seq.nextval,                   i.checktype,                   i.employee_id,                   to_date (i.swipe_date, 'mm/dd/yyyy'),                   '192.168.0.71'                   );    end loop; commit; end; /