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,
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).