Discussions
Categories
- 385.1K All Categories
- 2.6K Data
- 605 Big Data Appliance
- 2K Data Science
- 452.2K Databases
- 222.5K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 32 Multilingual Engine
- 576 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 575 SQLcl
- 4K SQL Developer Data Modeler
- 187.8K SQL & PL/SQL
- 21.5K SQL Developer
- 12 Data Integration
- 12 GoldenGate
- 297.7K Development
- 3 Application Development
- 18 Developer Projects
- 140 Programming Languages
- 294.4K Development Tools
- 118 DevOps
- 3.1K QA/Testing
- 646.5K Java
- 30 Java Learning Subscription
- 37K Database Connectivity
- 185 Java Community Process
- 107 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 174 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 206 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 588 LiveLabs
- 41 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 5.8K Other Languages
- 2.3K Chinese
- 197 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 240 Portuguese
Acessing columns relevant to dynamic table
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
Answers
-
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.
-
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.
-
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
-
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
-
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).
-
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
-
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.
-
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.
-
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
