Hi guys, i have been having trouble solving what seems to me a complex aspect of plsql, I have a table1 with a list of table_names and a table2 with backups to those table_names
Table1 Table2
id name max_rows date id name_bck FK date_created date closed
1 a 100 2018-10-06 1 a_bck 1
2 b 100 2018-10-06 2 b_bck 2
3 c 100 2018-10-06 3 c_bck 3
so the idea is for me to insert rows from a onto a_bck until a_bck reaches its limit (max_rows) then i would create a new a_bck2 and close(update close date from table2);
the problem im having is i cant access columns from table a of my table1, i want to insert data from a to a_bck where its date column from table a is < then sysdate
so i created 2 cursors to go through both tables and return the data i need to perform operations over.
cursor c1 is
select id name max_rows from table1 where date<sysdate;
curso2 c2 is
select id_fk , name, from table2 where table1.id=table2.id_fk and close_date is null;
then i would loop through and fetch the data related to table1 and table2
fetch c1 into id, n_tab, n_rows;
FETCH c2 INTO id_fk, n_tab2;
I would like some help on how to dynamically access the columns from the tables on table1.
I tried to summarize the best way possible.
If anyone could show me a small example of how i could implement this.
PS: i cant use partitions
Thank you in advance