Forum Stats

  • 3,815,187 Users
  • 2,258,974 Discussions
  • 7,892,983 Comments

Discussions

Categories

Acessing columns relevant to dynamic table

505f63af-d19b-43bc-ba92-afc3daef3c7f
505f63af-d19b-43bc-ba92-afc3daef3c7f Member Posts: 4
edited Nov 6, 2018 11:14AM in SQL & PL/SQL

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


Tagged:
· Share on TwitterShare on Facebook

Answers

  • BEDE
    BEDE Oracle Developer Member Posts: 2,404 Gold Trophy
    edited Nov 6, 2018 6:43AM

    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.

    · Share on TwitterShare on Facebook
  • 505f63af-d19b-43bc-ba92-afc3daef3c7f
    505f63af-d19b-43bc-ba92-afc3daef3c7f Member Posts: 4
    edited Nov 6, 2018 6:57AM

    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.

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,404 Gold Trophy
    edited Nov 6, 2018 7:10AM

    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

    · Share on TwitterShare on Facebook
  • 505f63af-d19b-43bc-ba92-afc3daef3c7f
    505f63af-d19b-43bc-ba92-afc3daef3c7f Member Posts: 4
    edited Nov 6, 2018 7:12AM

    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

    · Share on TwitterShare on Facebook
  • BEDE
    BEDE Oracle Developer Member Posts: 2,404 Gold Trophy
    edited Nov 6, 2018 7:17AM

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

    · Share on TwitterShare on Facebook
  • 505f63af-d19b-43bc-ba92-afc3daef3c7f
    505f63af-d19b-43bc-ba92-afc3daef3c7f Member Posts: 4
    edited Nov 6, 2018 7:23AM

    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

    · Share on TwitterShare on Facebook
  • BluShadow
    BluShadow Member, Moderator Posts: 41,869 Red Diamond
    edited Nov 6, 2018 10:43AM 
    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.

    · Share on TwitterShare on Facebook
  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Nov 6, 2018 11:01AM 
    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.

    · Share on TwitterShare on Facebook
  • Mike Kutz
    Mike Kutz Member Posts: 6,180 Silver Crown
    edited Nov 6, 2018 11:14AM 
    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

    · Share on TwitterShare on Facebook