after convert Oracle Object to MySQL/MariaDB, how can we verify that the object is working good.

hi,

as I am tried to migrate from Oracle to MariaDB/MySQL and we are try to tools Ispirer, once the tools convert the following object for us how can we verify the converted objects can works without any problem:

1) sequence,

by running Select NEXTVAL(<sequence name>); ?

what should be the output?

2) Function.

3) trigger

4) package,

5) synonyms.

any idea?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dmitry KurashkinDeveloperCommented:
"without any problem" - it is a very wide notion.
To check database objects, Oracle has a view 'ALL_OBJECTS' with the column 'STATUS'. Check for analogs of this view in your version of MySQL (BTW, what is the version?).
As for other points:
1. Selecting a new value of the sequence should return new value each time. Also, if you are migrating, you need to create a sequence with its current value in Oracle, because otherwise (if it starts from 1, for example) it will generate values, which already exist in tables (and you will get a lot of constraint violations).
2. A really reliable way to check functions is to write unit tests. Of course, for database functions, it could be hard because they often query tables.
Mark GeerlingsDatabase AdministratorCommented:
This is a *VERY* big task!  Converting an Oracle-based application to a non-Oracle database, then confirming that everything works as before will likely takes days or weeks of testing, depending on how much logic was included in the database.

1) sequence,

  Yes, this is the first test: "Select NEXTVAL(<sequence name>) from dual;

 what should be the output?

It should be the next available value from the sequence.  Keep in mind this will create a gap in the sequence numbers that your application will see.  Is that a problem in your application?  We don't know.

Also, does this sequence only need to provide values to the schema owner?  Or, can other database users also request values from this sequence?  If yes, you will need to check if the grants for that got copied to the new system.  

 2) Function.

This depends totally on what each function does.  If the function simply selects a value to return, that is easy to test.  But, if the function also does any SQL inserts, updates or deletes, you will need to confirm that those also happen as intended.  But, be careful.  Do you want test inserts, updates and deletes happening in your new production system?

 3) trigger

This depends totally on what each trigger does.  You will have to examine the code in each trigger to see which other tables should be  affected, then check those tables to see if the insert(s), update(s) and/or delete(s) actually happened.  And, do you want test inserts, updates and deletes happening in your new production system?

 4) package,

This depends totally on what each package contains.  PL\SQL packages often contain multiple functions and/or procedures that can be called.  You will need to examine each package spec, and test each procedure or function declared there.

 5) synonyms.

You can test these (in Oracle at least) by trying to refer to the actual object in SQL statements as a user other than the schema owner without providing the "owner." prefix.  I don't know if MariaDB supports synonyms just like Oracle, or not.
marrowyungSenior Technical architecture (Data)Author Commented:
Dmitry Kurashkin,

"To check database objects, Oracle has a view 'ALL_OBJECTS' with the column 'STATUS'. Check for analogs of this view in your version of MySQL (BTW, what is the version?)."

we will use MariaDB version v 10.3.x and after !

I don't know what we are expecting from the status of  oracle and the corresponding value in MariaDB, and what table in MariaDB will show that ?

"2. A really reliable way to check functions is to write unit tests. Of course, for database functions, it could be hard because they often query tables."

but anyway to just make it works as I am not a developer and I don't know how to do a unit test ?

"1. Selecting a new value of the sequence should return new value each time. Also, if you are migrating, you need to create a sequence with its current value in Oracle, because otherwise (if it starts from 1, for example) it will generate values,

then in MariaDB, how can I select a new value of the sequence? this is what thing I want.

Mark Geerlings,

"This is a *VERY* big task! "

at this stage, just try to check if the converted result /oracle objects to MariaDB is working fine is ok. So we want to see the tools Ispirer is good for us to buy,

"It should be the next available value from the sequence.  Keep in mind this will create a gap in the sequence numbers that your application will see.  Is that a problem in your application?  We don't know."

the what the command to test it ?

"Do you want test inserts, updates and deletes happening in your new production system?"

if it has a function like that I;d like that, but simple enough is ok .

"This depends totally on what each trigger does. "

but how to do a simple call to the trigger? a small test

"You can test these (in Oracle at least) by trying to refer to the actual object in SQL statements as a user other than the schema owner without providing the "owner." prefix. "

any example ?
Ensure Business Longevity with As-A-Service

Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.

Dmitry KurashkinDeveloperCommented:
I don't know what we are expecting from the status of oracle and the corresponding value in MariaDB, and what table in MariaDB will show that?
This status in Oracle shows the possibility to use the object. Possible values are "VALID" and "INVALID". It is not applicable to tables, they are always "VALID", but views, functions, triggers, etc. could be invalid, which means compilation error(s). For example, if you drop a table, all functions, procedures, packages, and views which used it will become invalid. If you will try to use invalid object, Oracle will raise an exception.
As for the exact system table in MariaDB, I never worked with it and can't say right now. I tried to google, but can't find. Also, I tried to see differences between MariaDB and Oracle. It seems like MariaDB has no Oracle-style packages and some types (like cursors), so, if you use them widely, moving to MariaDB could be as hard as developing a new application.

but anyway to just make it works as I am not a developer and I don't know how to do a unit test ?
There are two levels of check. The first one is to check if it compiled. Try to run the function. If it returns the result, it is compiled. The second level is to check does it returns correct result for every input value - and it is much more hard thing. There is no easy way to check every function.
Also, there could be another problem, I'll explain on the example. Let's say you call the standard function SUBSTR in you Oracle function. It returns a substring of a string and takes three arguments: a source string, a begin index, and a length. But the same standard function in some other languages, takes a source string, a begin index, and an end index, which makes impossible the direct conversion.

then in MariaDB, how can I select a new value of the sequence? this is what thing I want.
It depends on how do you use a sequence in Oracle. In 99% cases, it is used as a primary key values generator, so you need to try to insert a row into a table, which uses this sequence.

but how to do a simple call to the trigger? a small test
A trigger can't be called directly. Triggers are called when a certain event happens. Most of the triggers are used when you execute INSERT, UPDATE or DELETE statements on tables.
Mark GeerlingsDatabase AdministratorCommented:
"we want to see [if] the tool Ispirer is good for us to buy."
I've never seen a conversion tool that could convert 100% of the data and programs from one system to another, with no changes or problems.  But, I've never seen this Ispirer tool, so maybe it is better than any I have seen.  I don't expect an automated conversion tool to do more than 90 - 95% of the work.  I expect that some manual data conversions and/or code re-writes will be required.

"what [is] the command to test [sequences in MariaDB]?"
I don't know MariaDB, so I can't help you with that.  But, in Oracle if you do a simple "select [sequence_name].nextval from dual;" that will advance the sequence, and this value then will not be available to the application.  That will cause a gap in the numbers in your application.  Is that number gap a problem in your application?  We cannot tell you.

In Oracle, I can use this query to check the last value provided by a sequence without advancing the sequence, and causing a gap:
select s.sequence_name, s.min_value "Min", s.max_value "Max", s.increment_by "By",
s.last_number "Last", s.cache_size "Cache", cycle_flag, o.created "Created"
from user_objects o, user_sequences s
where o.object_name = s.sequence_name
and o.object_type = 'SEQUENCE'
order by s.sequence_name;

I don't know if MariaDB offers similar dictionary views, or not.
slightwv (䄆 Netminder) Commented:
>> Is that number gap a problem in your application?  We cannot tell you.

If it is then the app has bigger issues.  Sequences are NEVER gapless.
marrowyungSenior Technical architecture (Data)Author Commented:
Dmitry Kurashkin.

" It seems like MariaDB has no Oracle-style packages and some types (like cursors), so, if you use them widely, moving to MariaDB could be as hard as developing a new application."

we will try to migration as much object as possible, we can migrate most of it and we know we will have to rebuild the application.

"There are two levels of check. The first one is to check if it compiled. Try to run the function. If it returns the result, it is compiled. "

yes ,or run without any error! agree ?


"The second level is to check does it returns correct result for every input value - and it is much more hard thing. There is no easy way to check every function."

agree! need developers help on that.

", it is used as a primary key values generator, so you need to try to insert a row into a table, which uses this sequence."

sorry please give an example.

also an example on what you mean primary key values generator. tks.

"Most of the triggers are used when you execute INSERT, UPDATE or DELETE statements on tables."

make sense, seems can only test when doing DML.

Mark Geerlings,

" But, I've never seen this Ispirer tool, so maybe it is better than any I have seen.  I don't expect an automated conversion tool to do more than 90 - 95% of the work."

yeah, you should see this tools, it is funny that all service provider when comes up to our office all talk about this tools. But they didn't even know tools like sqlline and convert-in can only convert AT MOST table and data, not ANYTHING else involve coding !

I work proactively with Ispirer on using their tools and they will give NEW/UPDATE release everytime they know thier tools has problem.

The best service provider here said this tools can handle 85-90% of migration issue for you. They can also customize the tools to suit your need !

they also very proactive to keep asking you what problem you found out and they will release update for you.

" I expect that some manual data conversions and/or code re-writes will be required."

web tier need ! we are at the same times know we will rebuild the whole application but this tools take care database level quite well.

"That will cause a gap in the numbers in your application.  Is that number gap a problem in your application?  We cannot tell you."

what is a gap in numbers? any example ?

slightwv,

I just want to do a simple test to make sure that the conversion works ! the rest of the test need to relies on developers.
slightwv (䄆 Netminder) Commented:
>>yes ,or run without any error! agree ?

You need to unit test functions for the edge cases:  At least  Min/Max allowed values.

>>sorry please give an example.

There really isn't an example.  Hopefully you understand what a Primary Key is:  A unique row identifier.

A sequence is simply a number generator that provides a number in a defined manner.  If you set up a sequence that starts at 1000 and increments by 2, calling it will return: 1000, 1002, 1004, ...  until the MAX value defined.  Then it can either start over (CYCLE) or stop working.

>>what is a gap in numbers? any example ?
1
2
4
5

3 is missing, thus a gap in the sequence generation.
marrowyungSenior Technical architecture (Data)Author Commented:
hi all,

 can test sequence now and it is quite easy:

select nextval(<sequence name>)

Open in new window


whenever I run it, it will return next value, this is what i want ! you know migrated to MariaDB, how can we make sure that the same sequence still working as expected  ?

this can be one of them and I tested 2 x sequence and I call that in this way !
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

How to call a function and package in oracle and MariaDB? make it complie is not hard as the first test.
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

we have a function like that:

create or replace FUNCTION        to_string (nt_in IN varchar2_ntt, delimiter_in IN VARCHAR2 DEFAULT ', ') RETURN VARCHAR2 IS
v_idx PLS_INTEGER;
v_str VARCHAR2(32767);
v_dlm VARCHAR2(10);
BEGIN
v_idx := nt_in.FIRST;
WHILE v_idx IS NOT NULL LOOP
v_str := v_str || v_dlm || nt_in(v_idx);
v_dlm := delimiter_in;
v_idx := nt_in.NEXT(v_idx);
END LOOP;
RETURN v_str;
END to_string;

Open in new window


how can I call it and make it function ?
slightwv (䄆 Netminder) Commented:
select to_string(varchar2_ntt('Hello','World'),':') from dual;

My result:
Hello:World
marrowyungSenior Technical architecture (Data)Author Commented:
hi,

still this kind of error:

Error starting at line : 37 in command -
select CISUAT.to_string(varchar2_ntt('Hello','World'),':') from dual
Error at Command Line : 37 Column : 25
Error report -
SQL Error: ORA-00904: "VARCHAR2_NTT": invalid identifier
00904. 00000 -  "%s: invalid identifier"
*Cause:    
*Action:

Open in new window



but I see this type from SQL developer object panel .

oracle data type.
slightwv (䄆 Netminder) Commented:
Are you connecting to the database as the owner of the object?

Likely a permission issue.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.