Home » SQL & PL/SQL » SQL & PL/SQL » Looping through all records of an unknown table
Looping through all records of an unknown table [message #276830] Fri, 26 October 2007 07:20 Go to next message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
Hi All,

I've been searching and trying to find a solution for this problem, but haven't really found anything. My problem is the following:

I have a table with a number of columns, which I have to migrate into two other tables. During the migration quite a bit of data processing of every single record is required, which I can do with PLSQL, but which also means a simple insert-into-select-from query won't work. Since I have to write a generic function for this, I do not know at design time what the makeup is of the table (columns and data types). I've got a VARRAY of the columns using a select from user_tab_columns, but how can I use a cursor or a collection to loop through all records? I can't use %ROWTYPE, since the table is unknown, for the same reason I can't use a cursor, because I'd have to fetch into something, but I don't know what something is until I'm running.

I could get the number of rows quite easily. Would it be possible to use something along the likes of select where RowID=counter, and then for-loop from 1..count(*) or something?

TIA for any tips, hints or tricks,
Stefan
Re: Looping through all records of an unknown table [message #276838 is a reply to message #276830] Fri, 26 October 2007 07:40 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
When you don't know what the columns are at design time, how can you assign the column values to the columns of the other tables?

There must be some kind of logic behind that assignment, and for that you would have to know the columns, no?

The way I would do it :

1. Select the values into an tab1%rowtype
2. Assign the values to tab2%rowtype and tab3%rowtype
3. insert tab2%rowtype and tab3%rowtype into the target tables

that way the select/insert logic doesn't have to be changed, only the assignment logic once you know the columns.

That assignment could even be made in another procedure with tab1%rowtype as input parameter and tab2 and tab3%rowtype as an output parameter.
Re: Looping through all records of an unknown table [message #276839 is a reply to message #276830] Fri, 26 October 2007 07:42 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:

I can't use %ROWTYPE, since the table is unknown,
So you mean that the table name is unknown until runtime? You presumably intend passing this as a string parameter? You might want to look at Dynamic SQL and REF CURSORS. Problem is you haven't really supplied an awful lot of info, and the info that you have supplied is a bit vague, which means that the answers you get will probably be abit on the vague side too
Re: Looping through all records of an unknown table [message #276852 is a reply to message #276838] Fri, 26 October 2007 08:15 Go to previous messageGo to next message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
ThomasG wrote

When you don't know what the columns are at design time, how can you assign the column values to the columns of the other tables?

As I said, I use user_tab_columns to get info about all columns (the old ones and the ones to migrate to), and compare those, using the column name, data type, data length etc. That gives me a list of columns which I have to migrate, of which I know that they are available in both tables.


pablolee wrote

you mean that the table name is unknown until runtime
Yes, exactly. This routine is meant to be used at more than one location (different customers, different datamodels, etc.).

pablolee wrote

You might want to look at Dynamic SQL and REF CURSORS

Hmm. OK, I'll look into that. Do you mean that this

  type account_cur is ref cursor;
  cur account_cur;
  accountrec account_cur%rowtype;
  --or maybe cur%rowtype?;


  sqlquery := 'select * from ' || OOMTable; 
  open cur for sqlquery;
  loop
    fetch cur into accountrec;
    exit when cur%notfound;
    --do my processing of the records ...
  end loop;
  close cur; 

is something that would actually work?
Re: Looping through all records of an unknown table [message #276908 is a reply to message #276830] Fri, 26 October 2007 14:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
why not write something using dyanmic sql and execute immediate?

Build yourself a query based on compatible columns and the execute it.

Kevin
Re: Looping through all records of an unknown table [message #276919 is a reply to message #276830] Fri, 26 October 2007 16:55 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
drop table temp1;
drop table temp2;

create table temp1 (a number,b date,c varchar2(10),d clob,e integer);
create table temp2 (a number,b date,c varchar2(20),d clob);

insert into temp1 values (1,sysdate,'a','this is a clob',0);
commit;

select * from temp2;

define 1='TEMP1'
define 2='TEMP2'

set serveroutput on

declare
   column_list_v varchar2(32000);
   sqlv varchar2(32000);
begin
   for r1 in (
               select *
               from user_tables
               where table_name = upper('&&1')
             ) loop
      for r2 in (
                  select *
                  from user_tables
                  where table_name = upper('&&2')
                ) loop
         for r3 in (
                     select column_name,data_type,data_length,data_precision,data_scale
                     from user_tab_columns
                     where table_name = r1.table_name
                     and data_type in ('VARCHAR','VARCHAR2','CHAR','NUMBER','INTEGER','FLOAT','DATE')
                     intersect
                     select column_name,data_type,data_length,data_precision,data_scale
                     from user_tab_columns
                     where table_name = r2.table_name
                     and data_type in ('VARCHAR','VARCHAR2','CHAR','NUMBER','INTEGER','FLOAT','DATE')
                   ) loop
           column_list_v := column_list_v||','||r3.column_name;
         end loop;
         column_list_v := substr(column_list_v,2);
dbms_output.put_line('column_list_v = '||column_list_v);
         if column_list_v is null then goto theend; end if;
         sqlv := 'insert into '||r2.table_name||' ('||column_list_v||') select '||column_list_v||' from '||r1.table_name;
dbms_output.put_line('sqlv = '||sqlv);
         execute immediate (sqlv);
      end loop;
   end loop;
<<theend>> null;
end;
/

select * from temp2;
Re: Looping through all records of an unknown table [message #277161 is a reply to message #276919] Mon, 29 October 2007 02:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Ok, I'll bite.

Goto: that would NEVER pass at our shop. But I guess that was the reply you were waiting for, Kevin. Very Happy. Go on, convince me to use GOTO.

MHe
Re: Looping through all records of an unknown table [message #277179 is a reply to message #277161] Mon, 29 October 2007 02:52 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Ok, Maarten, I will follow..

Apart from the terrible idea, which must be the result of a design-error and can only lead to more trouble.. This looks like an extract-data-from-one-database-and-import-into-another-so-we-can-copy-testcases thing. I have TERRIBLE experiences with such 'flexible' programs.
Kevin, can you tell us approximately how many records you expect to be returned by c1 and c2?
Re: Looping through all records of an unknown table [message #277183 is a reply to message #277179] Mon, 29 October 2007 03:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
About 1 or less? Laughing

Regards
Michel
Re: Looping through all records of an unknown table [message #277201 is a reply to message #277179] Mon, 29 October 2007 03:59 Go to previous messageGo to next message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
Frank wrote on Mon, 29 October 2007 08:52


Apart from the terrible idea, which must be the result of a design-error and can only lead to more trouble.. This looks like an extract-data-from-one-database-and-import-into-another-so-we-can-copy-testcases thing.

No, it is not. It is a migration between two applications that both store their data in Oracle tables, except that one application is using one datamodel and the other application is using another datamodel. Both use Oracle Spatial as well, but different versions, which is why I need a lot of processing when migrating. If it was just the attributes it would have been straightforward SQL, but that is not possible.

@Kevin: I already have the matching columns, that is not the problem.

I guess though that it isn't possible, which is a bit of a disappointment to me. Oracle should be able to tell the format of a table at runtime if you ask me, but I guess I'll have to write code that writes code ... not the most elegant solution but it'll work.
Re: Looping through all records of an unknown table [message #277317 is a reply to message #277201] Mon, 29 October 2007 09:32 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
OK

StefJager, I provided an example of the "code from code". Acutally quite elegant. Also, efficient too given there is no looping involved in the actual data movement. Did you guys examine the plsql? Execute Immediate does the trick. This code examines the two tables and builds a select statement based on common columns, then executes it. I don't like the idea of looking at dicationary tables as part of a production piece of code, but it will work. It was an example for StefJager to learn from.

Frank, loops r1 and r2 each return at most one row. I just like for loops because they are easier to code and cleaner to loop at. Yes, technically, I supposed that fact that I am using a for loop suggests multiple rows are possible, but I still like it coded better this way. I can always comment it.

Maaher, as for goto: goto is a tool like any other tool and as such is to be exploited when the time is right. There are few absolutes in IM. Indeed, one of the greatest features of plsql is a goto. What do you think Exception Handling amounts to? It is just an intelligent goto. I can comment the goto easily as well.

If we don't use the goto in this piece of code then what are the alternatives? I can think of two, neither of which I like better:

1) if ... else exit; end if; to get out of the first loop. Oh wait, then we need other to get out the the next loop, and so on for however many loops we need. How confusing is that?

2) raise an exception and jump to an exception handler that does nothing on the error. Hmm.. Kind of like the goto only uglier. It requires the declaration of a user definined error, and an exception handler to say "sorry, didn't really mean it was an error". How confusing is that?

So, one of you geniuses come up with a better re-write. I will use it if it really is better. All in all, goto has its places, just not too many.

Kevin
Re: Looping through all records of an unknown table [message #277323 is a reply to message #277317] Mon, 29 October 2007 10:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
It is not a choice how to code the c1/r1 and c2/r2 loops, they are not necessary. You can use the tablenames directly in c3, instead of r1.table_name and r2.table_name.
Furthermore, code that needs GOTOs is not what I would call "Actually quite elegant". On the contrary, it lacks structure.
If you don't see the difference between using goto and using exceptions or proper loops, you really have some catching up to do about coding-structures.

You would not even need to jump out of all your 'loops' if you'd remove useless cursorloops anyway.

[Updated on: Mon, 29 October 2007 10:14]

Report message to a moderator

Re: Looping through all records of an unknown table [message #277326 is a reply to message #277323] Mon, 29 October 2007 10:35 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
1) Yes, I could have moved the table names directly into loop three. However, this code is not a piece of production material. It was inteded only as a demonstration tool. I used the looping to highlight the fact that we were dealing with two tables up front, as a clarification for the demonstration. Your comments show I succeeded in that regard.

2) The elegance I was referening to is in the fact the resulting insert statement is a single select issued against the database. As such it does not do slow by slow processing against the database as was originally suggested as a solution in the post. There is nothing inherently in-elegant about dynamic sql of this sort. Indeed though it should be used sparingly as a genearl rule, there are several problem spaces where the best solution is a dynamic one.

3) As for goto, it is those who do not understand when goto should be used, who are the ones who need to re-learn structure.

Where is your version of the plsql for comparison that is so superior without the goto?

Liken it to Shakespeare. In college a student once asked about one of Shakespeare's works, "how can this be a literary masterpiece of the english language when in it, Shakespeare breaks every rule of the language I have been taught?", to whit the professor replied, "the true master knows when rules are to be broken. When you are a proven master as is Shakespeare, I will re-grade your papers".
Re: Looping through all records of an unknown table [message #277436 is a reply to message #277326] Tue, 30 October 2007 02:20 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Sorry, still not convinced Kevin. Not at all. I never programmed a single GOTO in PL/SQL. Never. And I tend to keep it that way. I guess we all have our habits and our own theory but I still did not see a single block of PL/SQL that really required a goto. But I must admit: the single INSERT statement is nice.

Quote:

the true master knows when rules are to be broken
Nice quote, I might borrow it some day. But I think it's a bit cheap in this case to hide behind a platitude like that. And it might backfire Very Happy.

I was thinking something along this way:
DECLARE
   column_list_v   VARCHAR2(32000);
   sqlv            VARCHAR2(32000);

   CURSOR c_get_table(c_tname VARCHAR2)
   IS
      SELECT table_name
      FROM   user_tables
      WHERE  table_name = UPPER(c_tname);

   v_tname1        VARCHAR2(30);
   v_tname2        VARCHAR2(30);
BEGIN
   -- verify table existance
   OPEN c_get_table(:tname1);
   FETCH c_get_table INTO  v_tname1;
   CLOSE c_get_table;

   OPEN c_get_table(:tname2);
   FETCH c_get_table INTO  v_tname2;
   CLOSE c_get_table;

   -- gather common columns
   FOR r3 IN (SELECT column_name
                   , data_type
                   , data_length
                   , data_precision
                   , data_scale
              FROM   user_tab_columns
              WHERE  table_name = v_tname1
              AND    data_type IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE')
              INTERSECT
              SELECT column_name
                   , data_type
                   , data_length
                   , data_precision
                   , data_scale
              FROM   user_tab_columns
              WHERE  table_name = v_tname2
              AND    data_type IN
                        ('VARCHAR', 'VARCHAR2', 'CHAR', 'NUMBER', 'INTEGER'
                       , 'FLOAT', 'DATE'))
   LOOP
      column_list_v    := column_list_v || ',' || r3.column_name;
   END LOOP;

   column_list_v    := SUBSTR(column_list_v, 2);
   DBMS_OUTPUT.put_line('column_list_v = ' || column_list_v);

-- check whether there are shared columns:
   IF column_list_v IS NOT NULL
   THEN
-- concatenate the insert statement    
      sqlv    := 'insert into ' || v_tname2 || ' (' || column_list_v 
              || ') select ' || column_list_v || ' from ' || v_tname1;
      DBMS_OUTPUT.put_line('sqlv = ' || sqlv);
-- execute the insert statement
      EXECUTE IMMEDIATE (sqlv);
   END IF;
END;
/


No hard feelings, of course. I was just curious why an experienced programmer would use goto. And I still am, actually.

MHE
Re: Looping through all records of an unknown table [message #277464 is a reply to message #277436] Tue, 30 October 2007 04:21 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Again unnecessary fetches.
Why do you need to go to user_tables to check the existance of the tables?
column_list_v will be null if either table does not exist.

[Edit: changed typo in name of variable]

[Updated on: Tue, 30 October 2007 04:22]

Report message to a moderator

Re: Looping through all records of an unknown table [message #277474 is a reply to message #277464] Tue, 30 October 2007 05:04 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Right. I was focused on the loops/goto construction. That would simplify things even more.

MHE
Re: Looping through all records of an unknown table [message #277553 is a reply to message #277464] Tue, 30 October 2007 09:27 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Frank wrote on Tue, 30 October 2007 10:21

Again unnecessary fetches.
Why do you need to go to user_tables to check the existance of the tables?
column_list_v will be null if either table does not exist.

[Edit: changed typo in name of variable]


What about views?
A view won't show up in USER_TABLES, but its coumns do show up in USER_TAB_COLUMNS however!

Maybe one need to distinct between a TABLE and a VIEW at some point.

Re: Looping through all records of an unknown table [message #277558 is a reply to message #277553] Tue, 30 October 2007 09:45 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
That is not the point here, not the reason why Maarten or Kevin added it.
Re: Looping through all records of an unknown table [message #277570 is a reply to message #276830] Tue, 30 October 2007 10:53 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
A few more comments:

no offense taken by me, on anything, I enjoy the banter.

First and foremost, we are hijacking the thread, and this was never an intention of mine. I would like to know if StefJager feels he has a solution he can move forward with.

Next, some people are getting tied up on a piece of code that would never have been a production item to begin with. There are many rewrites of the piece I provided. If anyone wants to disucss this and goto at length, let us start a different thread for it. I could mention, I avoid explicitly declared cursors like the plague. In most cases I would much prefer a for loop to open fetch close, or alternatively a singleton select, for several reasons, none of which have to do the thread started by StefJager so if you want to talk about them please start another thread. Or we could also talk about the difference in code to be written if this piece was to execute from the server, or from plsql in a forms app for example and how the two selects are one too many round trips. But lets wait for the other thread.

Lastly as for goto, this is a long standing debate as we know. I could say lets leave it as a "difference of professional opinion" but I can't help repeating myself...

Guns are not bad, its how people use them that can be bad...

Goto is not bad, its how people use them that can be bad...

Goto is a tool. Every tool has a place, and when it is the right tool for the right job at the right time, it is a mistake not to use it. With a structured language like PL/SQL and especially with the existence of EXCPTIONS and the exception handling that PL/SQL does, GOTO is a rare thing, but not something to be never used.

OK, that's my last post on this subject. I am giving the thread back to StefJager.

Kevin

Re: Looping through all records of an unknown table [message #277606 is a reply to message #277570] Tue, 30 October 2007 14:38 Go to previous messageGo to next message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
Kevin Meade wrote on Tue, 30 October 2007 16:53


I am giving the thread back to StefJager.


Thank you.
Kevin Meade wrote on Tue, 30 October 2007 16:53

I would like to know if StefJager feels he has a solution he can move forward with.

No, I don't. As I mentioned before, the above code I had already figured out, the matching columns was not a problem, and if it was just a simple query I would not even have posted this problem here. I need to do a lot of processing on the data (FWIW: I need to go from Oracle's Spatial Object Model to Oracle's Spatial Relational Model), so it is not a simple matter of a select-insert (wish it was...).
But since it looks like it's not possible what I want to do, I'm going to write code generation code, because that will do what I want. Not the way I'd like to go, but needs must.
Re: Looping through all records of an unknown table [message #277618 is a reply to message #277570] Tue, 30 October 2007 17:10 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
My apologies for hijacking this thread, especially since I have not been involved in the discussion in any way up 'till now. But there is a point that I feel should be mad
Quote:

Goto is a tool.

So is a flint axe. I pretty sure I'm not going to choose a flint axe when technology has advanced to such a stage that I have a chain saw available to me.
Re: Looping through all records of an unknown table [message #277666 is a reply to message #277618] Wed, 31 October 2007 02:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Have you thought of a REF CURSOR?
Are the source table and destination tables fixed?
Is this a one-off thing or a repeating process?

You're describing an ETL process, something that reminds me of Oracle Warehouse Builder.

MHE
Re: Looping through all records of an unknown table [message #277678 is a reply to message #277666] Wed, 31 October 2007 02:35 Go to previous messageGo to next message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
Maaher wrote on Wed, 31 October 2007 08:01

Have you thought of a REF CURSOR?

Yes, I have. But that would still require the use of %ROWTYPE, for as far as I can see. And I do not know the tables. I know that certain columns must be there, but the user can add columns at will.

Maaher wrote on Wed, 31 October 2007 08:01

Are the source table and destination tables fixed?

No, that is my whole problem.

Maaher wrote on Wed, 31 October 2007 08:01

Is this a one-off thing or a repeating process?

It's going to be a repeating process, for different customers, with different databases, with different tables. If it was a one off I'd hardcode lots of things and not have the problem I have.

Maaher wrote on Wed, 31 October 2007 08:01


You're describing an ETL process, something that reminds me of Oracle Warehouse Builder.
Hmmm. Yes and no. It is an ETL process, but one that is very likely to happen a couple of times a year per customer, in some cases at least once a month. It's more a migration from one application to the other.

Like I said, I'm currently writing code that writes and compiles code. That allows me to do exactly what I want it to do. It's not improving performance but that is not a very big concern (when you're talking about an average of 100 tables with a million records per table, a process that runs on across all tables will take some time - and my clients accept that).

Thanks for the rather amusing discussion though Smile

Re: Looping through all records of an unknown table [message #277706 is a reply to message #277678] Wed, 31 October 2007 04:38 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I meant to say: is there some kind of regularity in the "source-target pairs"? Or can table A be the source for target table X and Y. And can source table B be the source for Y as well? Sorry for repeating but I'm just trying to find some line here.

I was thinking about Warehouse Builder and it's scripting language (OMB scripting). That could automate some things but I'm afraid it's too limited for what you require.

So far we get:
- Unknown source table at runtime
- Unknown target table(s) at runtime
- Unknown transformations at runtime

Pfew...quite difficult if you ask me. This looks like customization for each source/target/client(?) to me...

As for REF CURSORs, you have weak types too.

MHE
Re: Looping through all records of an unknown table [message #277714 is a reply to message #277706] Wed, 31 October 2007 05:06 Go to previous message
StefJager
Messages: 6
Registered: October 2007
Location: The Netherlands
Junior Member
Maaher wrote on Wed, 31 October 2007 10:38

Pfew...quite difficult if you ask me. This looks like customization for each source/target/client(?) to me...


Smile yes I know. That's why they gave it to me here Shocked And we've been doing this for a number of times indeed for each source/target, but that is (on the whole) taking too much time. A generic function would save us a lot.

Anyway: I expect that most of the time there will be a one to one relationship between source and target tables, but that is not necessary the case, hence the need for checking the columns.

I can get the code working for all geometry types etc, so now I am working on code that generates the code that actually does the processing. That way I can use <variable table name>%ROWTYPE, because that statement happens in the first procedure, and is considered to be a string by the compiler, until the first procedure gives it to the compiler with the value of the string.


Previous Topic: utl_file.fopen
Next Topic: Select rows depending on when they where create
Goto Forum:
  


Current Time: Sun Dec 04 00:39:01 CST 2016

Total time taken to generate the page: 0.18124 seconds