Toad World® Forums

Name length restriction and Defaults in Oracle


#1

Hi,

I ran reverse engineering for MS Access database and then converted result to Oracle 10g model . Using TDM naming conventions I was capable to get acceptable table and field names. Thanks, very good features.

I would just pay attention that TDM doesn’t’t validate table and attribute name length. Oracle’s restriction is 30 characters whereas MS Access fields can have much more.
verify model or Generate script doesn’t give any warnings about it… Hope it can be fixed in new versions.

But I don’t understand how naming conventions work for default values. After conversion from MS Access to Oracle all default values were in double quotes like “1” even for numeric field. Generated script has double quotes for default values as well and it is not acceptable for Oracle. I would consider it as TDM bug… Do you agree with that? Default values need to be in single quotes for varchar2 fields and no quotes for numeric fields.
I have about 100 fields with defaults… Do I really need to correct all of them manually or there is a way to correct them automatically in according with Oracle rules for defaults?
How do naming conventions work for defaults? Does it make sense to have any naming conventions for defaults for Oracle model?

Thanks
Vitaliy


#2

Hi Vitalyi,

thank you for your feedback. Toad Data Modeler is customizable tool and some features can be added to the software via custom packages.

Here you can find package that improves model verification for Oracle models:
http://modeling.inside.quest.com/entry.jspa?externalID=3251&categoryID=34

Information about how to import the package to TDM can be found at:
http://blogs.inside.quest.com/modeling/2010/10/21/how-to-import-custom-packages/

Re default values and conversions: Toad Data Modeler can help you with the conversion, it is not possible to automatically convert stored procedures from Oracle to MS SQL etc… Toad Data Modeler is not able to parse such items and because so many database platforms are supported, it is almost impossible to cover all possibilites. Fortunately, you can write simple script to iterate all defaults and change double quotes to single quotes etc. You can modify the above mentioned macro or create your own macro. Please have a look at this article:
http://blogs.inside.quest.com/modeling/2010/11/09/run-macro-for-selected-entities-and-store-ddl-preview-to-files/
you can find there information about how to write new macro and how to iterate entities.

Let me know if you need my help.

Regards,

Vaclav

Message was edited by: modelingadmin


#3

Thanks Vaclav for your answer. I added your package and now have warning on too long field names. Thanks a lot.

I’ll need to learn how to use TDM metamodel to write script but It looks I’ll need to develop some universal verification/parsing of Oracle default values. If I remeber correctly default values can be pseudo columns, text for varchar2 and char field and number for number fields.
I hope this verification can be included in basic TDM functionality.

Sorry, I have additional question. Do you have a package that verify column names if they coincide with Oracle key words ( like index)? if yous, could you give me a link for that?

Thanks
Vitaliy


#4

Hi Vitaliy,

you don’t have to learn metamodels etc. You can write a macro that will iterate columns and if you find quotation marks in data types where you don’t want them then you can use javascript functions to remove the quotes.

Verification of reserved words is not available. But when I find some time I will try to prepare some example and publish article on our blog.

Regards,

Vaclav


#5

Thanks Vaclav for your answer.

Actually you can’t just remove double quotes because if table or field name has spaces or other special characters double quotes are necessary.

I was not able to find easy way ( without writing java scripts) to replace spaces in table and field names on underscores. Could you recommend some simple approach to that?

Thanks
Vitaliy


#6

Sorry, I figured out how to replace spces on underscores.
It is character replacement , not word replacement.

Thanks