marrowyung
asked on
migrating table schema from Oracle to MariaDB. data type conflicted
hi,
I am converting oracle table structure and data to mariaDB V10.3, using tools https://www.convert-in.com/oracle-to-mysql.htm, and the log say there are table can't be convert, let's see what it is:
1)
Table 'AQ$_QUEUES': column 'SUBSCRIBERS' has unsupported type 'AQ$_SUBSCRIBERS', skip it
2)
Table 'ROLLING$STATISTICS': column 'VALUEINT' has unsupported type 'INTERVAL DAY(3) TO SECOND(2)', skip it
3)
Table 'SCHEDULER_JOB_ARGS_TBL': column 'ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it
4)
Table 'SCHEDULER_PROGRAM_ARGS_TB
is ANYDATA a customer defined type ? what type should it be when port to MariaDB ?
what type should this be : INTERVAL DAY(3) TO SECOND(2) when port to MariaDB ?
I am converting oracle table structure and data to mariaDB V10.3, using tools https://www.convert-in.com/oracle-to-mysql.htm, and the log say there are table can't be convert, let's see what it is:
1)
Table 'AQ$_QUEUES': column 'SUBSCRIBERS' has unsupported type 'AQ$_SUBSCRIBERS', skip it
2)
Table 'ROLLING$STATISTICS': column 'VALUEINT' has unsupported type 'INTERVAL DAY(3) TO SECOND(2)', skip it
3)
Table 'SCHEDULER_JOB_ARGS_TBL': column 'ANYDATA_VALUE' has unsupported type 'ANYDATA', skip it
4)
Table 'SCHEDULER_PROGRAM_ARGS_TB
is ANYDATA a customer defined type ? what type should it be when port to MariaDB ?
what type should this be : INTERVAL DAY(3) TO SECOND(2) when port to MariaDB ?
ASKER CERTIFIED SOLUTION
![Avatar of Alex [***Alex140181***]](https://filedb.experts-exchange.com/files/public/2022/11/13/8257e912-ee3c-4a93-9df0-fe9a8a1515cf.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
slightwv,
"Looks like you are still doing a FULL import of a FULL export. "
is porting DB schema to OTHER DIFFERENT DB, the mariaDB, so will have conflict like this as that type is not supported, so in real migration, we would like to know what type should we change to !
"ANYDATA is an Oracle data type:"
I agree, then what type we have to convert to?
" You will not have to port the system and data dictionary tables."
which one are you referring to? AQ$_QUEUES?
Alexander,
"I'd skip because you will have to rewrite them anyway ;-)"
I know, but point is when we rewrite, what type we should rewrite to ? this is what I am asking.
tks.
"Looks like you are still doing a FULL import of a FULL export. "
is porting DB schema to OTHER DIFFERENT DB, the mariaDB, so will have conflict like this as that type is not supported, so in real migration, we would like to know what type should we change to !
"ANYDATA is an Oracle data type:"
I agree, then what type we have to convert to?
" You will not have to port the system and data dictionary tables."
which one are you referring to? AQ$_QUEUES?
Alexander,
"I'd skip because you will have to rewrite them anyway ;-)"
I know, but point is when we rewrite, what type we should rewrite to ? this is what I am asking.
tks.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
anydata ?And I've seen this in method4 dynamic SQL: http://www.oracle-developer.net/display.php?id=422
i've seen that in anton's pivoting approach
anydata != anydataset.
ASKER
" != "
not equal ?
not equal ?
" != "No, it means "2 cheesburger with hot salsa dip" Come on!!
not equal ?
it's actually a smiley for a "skinny guy running into a stack of plates"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
"I stand by my statement: Forget you ever saw that data type. You will likely go through your entire career and NEVER have to worry about it."
as I will need to rewrite the whole application, right?
as I will need to rewrite the whole application, right?
ASKER
tks all.
>>as I will need to rewrite the whole application, right?
Very likely. Even if you could find software to automatically port the code, it will likely be pretty inefficient once "ported".
Just because the code is syntactically correct and compiles in the new database, it likely doesn't do things the best or most efficient way in the new language.
Very likely. Even if you could find software to automatically port the code, it will likely be pretty inefficient once "ported".
Just because the code is syntactically correct and compiles in the new database, it likely doesn't do things the best or most efficient way in the new language.
ASKER
" it will likely be pretty inefficient once "ported"."
excellent, and my job is to make it portable first ! then application team rewrite that.
" it likely doesn't do things the best or most efficient way in the new language."
yeah.
excellent, and my job is to make it portable first ! then application team rewrite that.
" it likely doesn't do things the best or most efficient way in the new language."
yeah.
>>excellent, and my job is to make it portable first ! then application team rewrite that.
Seems like wasted effort. To port it, you will need a good understanding of the requirements and you'll need to pretty much tough every line of code. After you port it, you will still need to go through a full regression test to make sure the new code does the same thing as the old code.
Why not touch the code once and do it right the first time?
Seems like wasted effort. To port it, you will need a good understanding of the requirements and you'll need to pretty much tough every line of code. After you port it, you will still need to go through a full regression test to make sure the new code does the same thing as the old code.
Why not touch the code once and do it right the first time?
ASKER
" After you port it, you will still need to go through a full regression test to make sure the new code does the same thing as the old code."
yeah,
"Seems like wasted effort. "
why ? we have a vendor comes to and talk about that. he also say so.
the only thing he is worry about is the OLAP and data warehouse tier, he agree that too but we will rewrite using java + python ! he said python is faster, is it ?
moving to from oracle OLAP to mariaDB AX seems not possible, need third party tools. do you agree?
So finally we should have the mix of java and python ! he said only python can talk to device! I am not sure.
yeah,
"Seems like wasted effort. "
why ? we have a vendor comes to and talk about that. he also say so.
the only thing he is worry about is the OLAP and data warehouse tier, he agree that too but we will rewrite using java + python ! he said python is faster, is it ?
moving to from oracle OLAP to mariaDB AX seems not possible, need third party tools. do you agree?
So finally we should have the mix of java and python ! he said only python can talk to device! I am not sure.
>>why ? we have a vendor comes to and talk about that. he also say so.
You rewrite all the code. The team performs a FULL regression test.
Go into production.
Someone then rewrites/verifies all the code to ensure best practices. The team performs a FULL regression test.
Go into production.
See the complete duplication of effort?
>>he said python is faster, is it ?
Faster than what?
https://en.wikipedia.org/wiki/Law_of_the_instrument
If you know Python, the code you write will probably be faster than writing something in some tool you do not know.
I don't know Python so cannot give a review. I know there are many people that use it but there are also many that write in pure "/bin/sh".
There are many schools of thought at what tier the business logic should be located. All schools of thought have pros and cons.
>>he said only python can talk to device! I am not sure.
I don't know what "device" you are talking about but Java is a programming language and as such should be able to talk to just about anything that anything else can talk to.
>> do you agree?
I cannot say. Don't do a lot with Oracle OLAP and know nothing about AX.
You rewrite all the code. The team performs a FULL regression test.
Go into production.
Someone then rewrites/verifies all the code to ensure best practices. The team performs a FULL regression test.
Go into production.
See the complete duplication of effort?
>>he said python is faster, is it ?
Faster than what?
https://en.wikipedia.org/wiki/Law_of_the_instrument
if the only tool you have is a hammer, to treat everything as if it were a nail
If you know Python, the code you write will probably be faster than writing something in some tool you do not know.
I don't know Python so cannot give a review. I know there are many people that use it but there are also many that write in pure "/bin/sh".
There are many schools of thought at what tier the business logic should be located. All schools of thought have pros and cons.
>>he said only python can talk to device! I am not sure.
I don't know what "device" you are talking about but Java is a programming language and as such should be able to talk to just about anything that anything else can talk to.
>> do you agree?
I cannot say. Don't do a lot with Oracle OLAP and know nothing about AX.
ASKER
"Faster than what? "
java
"You rewrite all the code. Th"
I don't,developer will.
"There are many schools of thought at what tier the business logic should be located. All schools of thought have pros and cons."
actually I agree ! vendor comes up and said that but our developer say no. it seems java is faster.
"Don't do a lot with Oracle OLAP and know nothing about AX."
so you didn't know what solution we can solve this OLAP migration problem ?
java
"You rewrite all the code. Th"
I don't,developer will.
"There are many schools of thought at what tier the business logic should be located. All schools of thought have pros and cons."
actually I agree ! vendor comes up and said that but our developer say no. it seems java is faster.
"Don't do a lot with Oracle OLAP and know nothing about AX."
so you didn't know what solution we can solve this OLAP migration problem ?
>>so you didn't know what solution we can solve this OLAP migration problem ?
Sorry but no.
>> it seems java is faster.
There will ALWAYS be benchmarks tailored to prove X is "better/faster/cheaper" then Y. Even if you reverse the products they will create some specific test that "proves" the point.
There are some benchmarks for Python and Java already out there:
https://benchmarksgame-team.pages.debian.net/benchmarksgame/faster/python.html
>> but our developer say no.
Go back to my hammer link: A developer always wants to solve every problem with code in the language they know.
I feel the business rules should drive where it is located. For example: If the process does a LOT of data processing it doesn't make sense to pull all the data from the database to a app layer to process. Database servers have the horsepower and are built to process data. Let it do it.
Sorry but no.
>> it seems java is faster.
There will ALWAYS be benchmarks tailored to prove X is "better/faster/cheaper" then Y. Even if you reverse the products they will create some specific test that "proves" the point.
There are some benchmarks for Python and Java already out there:
https://benchmarksgame-team.pages.debian.net/benchmarksgame/faster/python.html
>> but our developer say no.
Go back to my hammer link: A developer always wants to solve every problem with code in the language they know.
I feel the business rules should drive where it is located. For example: If the process does a LOT of data processing it doesn't make sense to pull all the data from the database to a app layer to process. Database servers have the horsepower and are built to process data. Let it do it.
ASKER
"Go back to my hammer link: A developer always wants to solve every problem with code in the language they know."
ahahhah, specially when they ONLY know ONE language but not the other so they try to say their language is the best ?
ahahhah, specially when they ONLY know ONE language but not the other so they try to say their language is the best ?
ahahhah, specially when they ONLY know ONE language but not the other so they try to say their language is the best ?No one said that! Don't "read" more than what's written!
People just stick to what they got used to, that's all ;-)
>>People just stick to what they got used to, that's all ;-)
Somewhat true.
When languages are similar like Java and C, a good C programmer can probably get by in Java. There will always be a learning curve. I wouldn't expect the C programmer to be able to write stellar Java code from day one. The more differences in the language the less effective they will be. A Senior level .Net person can probably figure out enough PHP to get something up and running because web is web and the core principles are the same. Probably won't be the most efficient/best PHP site on the planet but it will function.
Somewhat true.
When languages are similar like Java and C, a good C programmer can probably get by in Java. There will always be a learning curve. I wouldn't expect the C programmer to be able to write stellar Java code from day one. The more differences in the language the less effective they will be. A Senior level .Net person can probably figure out enough PHP to get something up and running because web is web and the core principles are the same. Probably won't be the most efficient/best PHP site on the planet but it will function.
ASKER
"Somewhat true."
good explanation, just somewhat! one explanation do not present the whole world.
" Probably won't be the most efficient/best PHP site on the planet but it will function."
yeah.
good explanation, just somewhat! one explanation do not present the whole world.
" Probably won't be the most efficient/best PHP site on the planet but it will function."
yeah.
https://docs.oracle.com/database/121/ARPLS/t_anydat.htm
Looks like you are still doing a FULL import of a FULL export. You should stop that now. You will not have to port the system and data dictionary tables.
You need to focus ONLY on the schemas used by the application.