Home » SQL & PL/SQL » SQL & PL/SQL » Find rowtype / record type dynamically (PL/SQL for oracle 10i)
Find rowtype / record type dynamically [message #305391] Mon, 10 March 2008 10:52 Go to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
Hi everyone!
I'll try to explain my problem as shortly as possible..

The goal of my project is to migrate data from one DB to a new one. Some of the data has to be changed during the migration (e.g. some IDs are changing etc.). Since not all changes are known right now (are they ever?) and since we hope to get some kind of framework going for future migrations (and because I don't want to migrate 76 tables individually), I try to do this in a quite generic way.
I already have a first version working, which dynamically generates statements like

INSERT INTO SCHEMA.NEW_TABLE_NAME (new_attr1, new_attr2, ....)SELECT (old_attr1, old_attr2, ....) FROM SCHEMA.OLD_TABLE_NAME@old_db

for all tables and executes them using the DBMS_SQL package. (the old_attr thingies in the select part get replaced by whatever is appropriate, e.g. a new ID)

Unfortunately, our client wants more information on what happens during the migration (e.g errors while migrating individual records), which means, I need to split that one insert-statement per table into individual ones for each record, so I can track errors in more details and so that, if an error occurs for one record, the rest of the table is not affected.

I hope someone is still reading this :-/

My first thought was, to use a cursor for the select statement and then fetch each record and insert it into the new DB. My problem with this approach is though, that this way I need to query the DB for every single record in that DB (and this migration will run a lot, before it works.. and also once it works) and I suppose this will be very very slow.
So the next thing I thought of, was to bulk collect the whole table (or at least parts of it) from the cursor into a variable and then iterate over that variable.. And this is where my problem is: To bulk collect the records into a variable, I have to declare that variable, and for that declaration I need to have the rowtype of the table I'm migrating. I only have the name of that table though.
Does anyone have an idea how to find that rowtype? Or a different (and probably way smarter) way of handling my problem?

I'd really really appreciate your help! Please feel free to ask, if anything is unclear!



Re: Find rowtype / record type dynamically [message #305398 is a reply to message #305391] Mon, 10 March 2008 11:07 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Always try to keep it simple.
http://www.orafaq.com/node/76

Regards

Raj
Re: Find rowtype / record type dynamically [message #305399 is a reply to message #305391] Mon, 10 March 2008 11:13 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
I knew there must be a smarter way of doing this!

You are my personal hero for today Smile Thanks!
Re: Find rowtype / record type dynamically [message #305402 is a reply to message #305399] Mon, 10 March 2008 11:39 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Also We would like to know how you get on with this approach and your findings, it will be very useful for others who could potentially end up doing the same or similar sort of things.

Regards

Raj
Re: Find rowtype / record type dynamically [message #305403 is a reply to message #305402] Mon, 10 March 2008 11:41 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
yep, I'll write down my experience once I really get it working!
Re: Find rowtype / record type dynamically [message #305622 is a reply to message #305403] Tue, 11 March 2008 06:59 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
ok, so I got this to work! and I'm loving it Smile

Instead of automatically creating an error table for each table I am migrating, I created a table that contains the mandatory columns for the error logging (These are ORA_ERR_NUMBER$, ORA_ERR_MESG$, ORA_ERR_ROWID$, ORA_ERR_OPTYP$ and ORA_ERR_TAG$) and changed the insert statements to


INSERT INTO SCHEMA.NEW_TABLE_NAME (new_attr1, new_attr2, ....)SELECT (old_attr1, old_attr2, ....) FROM SCHEMA.OLD_TABLE_NAME@old_db LOG ERRORS INTO schema.error_table reject limit unlimited

This stores the error message (e.g. cannot insert null into[..]) in the error_table..

You can pass a string to the error logger that is stored in the erorr_table. I am thinking about how to get a meaningfull tag that lets me identify the exact row the error occured at, but I will probably need to define what column of each table identifies the record.. Maybe default this to the primary key of the table if there is one...
Re: Find rowtype / record type dynamically [message #305905 is a reply to message #305622] Wed, 12 March 2008 05:49 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
More questions:

I decided to automatically generate the error_logging tables automatically using DBMS_ERRLOG.CREATE_ERROR_LOG()..
Unfortunately this gives me the error

ORA-01655: unable to extend cluster SYS.C_OBJ# by 128 in tablespace SYSTEM

Does anyone have any idea, why I get this? The error_logging table should not be generated in tablespace SYSTEM, so why is it complaining about something in tablespace SYSTEM? (I get the same error, even if I explicitly specify the tablespace to use..)

Re: Find rowtype / record type dynamically [message #305911 is a reply to message #305905] Wed, 12 March 2008 05:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because Oracle needs to increase or create something that implies increasing c_obj# that it can't do because there is no more space in SYSTEM tablespace.

Your DBA is not doing good job. SYSTEM tablespace should have at least 25% of free space at ANY time.

Regards
Michel
Re: Find rowtype / record type dynamically [message #305919 is a reply to message #305911] Wed, 12 March 2008 06:06 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
ok, I'll tell him Smile I don't quite get why anything should be added to the system tablespace.. I figured all the procedure is doing is generating this one table (in a tablespace different than SYSTEM).. But I'll have the admin check the tablespace.
Re: Find rowtype / record type dynamically [message #305928 is a reply to message #305391] Wed, 12 March 2008 06:17 Go to previous messageGo to next message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
haha, that was really the problem Mad "I can't work like that!!!" Thanks for the help and sorry for asking such a trivial question, I should've been able to figure that one out myself.
The problems aren't stopping... [message #307275 is a reply to message #305391] Tue, 18 March 2008 05:33 Go to previous message
DeeDee
Messages: 14
Registered: March 2008
Junior Member
It turns out, that there is just too much data in some of those tables to migrate it all in one statement. (Not sure what happens, I suppose the transaction just gets way too big.. ), so I figured I'd split the insert statements into smaller parts, for some tables.
This gives me statements like the following

INSERT INTO new_table (newAttr1, newAttr2, ..)
SELECT * FROM(SELECT oldAttr1, oldAttr2, .. FROM old_table@oldDB) WHERE ROWNUM BETWEEN x and y

(This seems to be the standard approach to this kind of "paging" problem)

Unfortunately, this does not work, if the table has columns of type BLOB, which some of my tables do. I get oracle error 22992 "cannot use LOB locators selected from remote tables".
That error appears, as soon, as I have these two nested selects. I do realize, this is not an error, but just the way the whole thing is specified, so, does anyone have an alternative solution to this problem?
Previous Topic: why I can not "create a table " in procedure?
Next Topic: Use of Round() function.....Ceiling () function
Goto Forum:
  


Current Time: Fri Dec 02 18:27:36 CST 2016

Total time taken to generate the page: 0.48899 seconds