Acessing columns relevant to dynamic table

505f63af-d19b-43bc-ba92-afc3daef3c7f

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

BEDE

Hello,    505f63af-d19b-43bc-ba92-afc3daef3c7f !

Well, the idea of having N tables a_bck1, a_bck2,... a_bckn is completely flawed.

Better use only one table. And that table can be partitioned. So you will access your data easier using SQL. Otherwise, how would you know in which of those N tables you may find some specific data?

Wrong database design breeds all kinds of problems that will haunt you until redesign - possibly for years...

And try avoiding to use PL/SQL when a simple insert select will do the job faster.

P.S.

I think anyone in this forum would appreciate if you changed your user name to something easier to memorize and pronounce.

505f63af-d19b-43bc-ba92-afc3daef3c7f

The close_date would allow me to know where in those N tables i would look for my date, and this data most likely i will never need to go look it up, its just in case something extrordinary happened.

I can totally see what you meen, it would be must simpler to use partitions, but i cant.

BEDE

So, have only one table that will be partitined by cloded date, partitioned by interval of one year or one month, as it suits you, if the close_date is essential for that historic data. That is a better solution than having N tables. You will not be able to refer those tables in stored procedures unless you use dynamic SQL, which is difficult to maintain and also leads to poor performance.

See for instance:

Oracle interval partitioning tips

https://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm

505f63af-d19b-43bc-ba92-afc3daef3c7f

thats what i was trying to implement , using dynamic sql but having trouble implementing what i want. The idea is to just export the data after a while onto a file, we dont really need to access it, we just need to know its there, my boss came up with this way and does not want partitions i dont know why

BEDE

Maybe you can't tell your boss he has things to learn, but you certainly may apply the solution with one partitioned table, which is much better than what you have been trying to do. That's as much as I can tell you on this topic. I'd also give you the create table statement for the historic table if only you had given the DDL for that "table1" of yours (I bet that is not the table name in your actual system).

505f63af-d19b-43bc-ba92-afc3daef3c7f

anyway thanks for your help, ya its not the name i use , just tried to summarize the stuff, its something like sii_bck_cfg_tab

BluShadow

BEDE wrote:Maybe you can't tell your boss he has things to learn, but you certainly may apply the solution with one partitioned table, which is much better than what you have been trying to do. That's as much as I can tell you on this topic. I'd also give you the create table statement for the historic table if only you had given the DDL for that "table1" of yours (I bet that is not the table name in your actual system).

Partitioning does have licence/cost implications, so maybe the boss has said not to use partitions because they aren't licenced for it.

EdStevens

BEDE wrote:Maybe you can't tell your boss he has things to learn, but you certainly may apply the solution with one partitioned table, which is much better than what you have been trying to do. That's as much as I can tell you on this topic. I'd also give you the create table statement for the historic table if only you had given the DDL for that "table1" of yours (I bet that is not the table name in your actual system).

Why do you seem to imply that the 'one table' design requires partitioning?  Yes, partitioning might be nice if, as the OP says, most of the data will seldom if ever be referenced.  But we have seen nothing to indicate that the amount of data is really so massive as to require partitioning to maintain some semblance of performance.

Mike Kutz

BEDE wrote:
P.S.I think anyone in this forum would appreciate if you changed your user name to something easier to memorize and pronounce.

(for the OP) You can change your screen name by watching this video:

Video tutorial how to change nickname available

MK