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!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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

anishjp

Anybody?

Answer

No...if you need more than that, might be time to look at something like APEX. Or build a rest api to pull the data and use your favorite js library to chart the data.
We're building this feature into SQL Developer Web - stay tuned.

Marked as Answer by anishjp · Apr 23 2021
anishjp

Okay, thank you. I will look at APEX then.

Regards,
Anish

1 - 3

Post Details

Added on Nov 6 2018
9 comments
281 views