Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Acessing columns relevant to dynamic table

505f63af-d19b-43bc-ba92-afc3daef3c7fNov 6 2018 — edited Nov 6 2018

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


Comments

Post Details

Added on Nov 6 2018
9 comments
285 views