Issue in using cursor with parameters

Question

I am using a cursor with both input and output parameters to fetch data from a select query and insert into another table at intervals:-

DECLARE

  i_start_date varchar2(20) := '2019-01-02';
  i_end_date varchar2(20):= '2019-01-09';
  v_ack_records record_inst%ROWTYPE;

  CURSOR record_inst(i_start_date varchar2,i_end_date varchar2) IS
  select a.id,b.status
  from table1.a,table2 b
  on a.msg_id=b.msg_id
  and b.t_date>=i_start_date 
  and b.t_date<=i_end_date ;

BEGIN
    OPEN record_inst; 
    LOOP
      FETCH record_inst INTO v_ack_records;
      EXIT WHEN record_inst%NOTFOUND;
      INSERT INTO test_table
        (id,status)
      VALUES(v_ack_records.id,
             v_ack_records.status);

    END LOOP;
    CLOSE record_inst;
    COMMIT; 
END; 
/

I am not quite clear on the syntax to use for this.Could you please help on this? I got the below errors:-

[Error Code: 6550, SQL State: 65000]  ORA-06550: line 5, column 15:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 5, column 15:
PL/SQL: Item ignored
ORA-06550: line 14, column 5:
PLS-00306: wrong number or types of arguments in call to 'RECORD_INST'
ORA-06550: line 14, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 28:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 16, column 5:
PL/SQL: SQL Statement ignored
ORA-06550: line 22, column 15:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 18, column 1:
PL/SQL: SQL Statement ignored

Start date and end date are inputs and id & status are the outputs.

Thanks,

Answer 1

This is a very simplified code you wrote; important - for your case - is the declare section.

SQL> declare
  2    v_emp_rec    record_emp%rowtype;
  3
  4    cursor record_emp is
  5      select empno, ename from emp;
  6  begin
  7    null;
  8  end;
  9  /
  v_emp_rec    record_emp%rowtype;
               *
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00320: the declaration of the type of this expression is incomplete or
malformed
ORA-06550: line 2, column 16:
PL/SQL: Item ignored


SQL>

As you see, the same error you got. Why? Because you first declared a cursor variable - which is based on cursor's definition - but cursor hasn't been declared yet. In other words, should be vice versa: cursor first, variable next:

SQL> declare
  2    cursor record_emp is
  3      select empno, ename from emp;
  4
  5    v_emp_rec    record_emp%rowtype;
  6    begin
  7    null;
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL>

---

Apart from that, you don't need to use such a complex code; a simple insert does the job. Note the way I declared variables - their datatype is date as it probably should be, unless b.t_date column's datatype is varchar2. If it is, then you have a bigger problem than order in declaration section. Never store dates as strings.

Also, you should switch to explicit joins instead of oldfashioned from clause where all tables are separated by commas, and where joins are mixed with conditions. Your code is somewhat strange as it contains the on keyword but without join and where so ... that won't work.

declare
  i_start_date  date := date '2019-01-02';    -- date literal is always YYYY-MM-DD
  i_end_date    date := date '2019-01-09';
begin  
  insert into test_table (id, status)
    select a.id, b.status
    from table1 a join table2 b on a.msg_id = b.msg_id
    where b.t_date between i_start_date and i_end_date;
end;
/

---

If you want to use a cursor, consider cursor FOR loop as it is much easier to use; Oracle does most of the dirty job for you (opening the cursor, fetching from it, paying attention when to exit the loop, closing the cursor):

declare
  i_start_date  date := date '2019-01-02';
  i_end_date    date := date '2019-01-09';
begin  
  for cur_r in (select a.id, b.status
                from table1 a join table2 b on a.msg_id = b.msg_id
                where b.t_date between i_start_date and i_end_date
               )
  loop
    insert into test_table (id, status)
      values (cur_r.id, cur_r.status);
  end loop;
end;
/

---

If you still want to do it your way, please, do so. The rest of your procedure looks OK (can't test it, I don't have your tables).