Hello Toad Community,
when I generate a script and enable the option “Generate Schema/User to Objects”, the schema name is added to tables, relationships, sequences, but not to my functions and procedures. I checked the functions and procedures, the schema name is assigned to them. Anything I can do or anything I am doing wrong, so the schema name is added to all objects ?
Ilja
Hi Ilja,
What TDM version do you have? What Oracle db do you use?
I’ve just tested it in latest TDM 3.4.14 for Oracle 10g and it works fine. Schema/User for functions, procedures, sequences etc. has been generated.
One thing - Please open Property dialog of your function and procedure and on tab General check out the ‘Generate SQL Only’ checkbox. Is it selected or clear?
Note: If this checkbox is selected, only the SQL from tab SQL is generated in the code - with no dependency on the checkbox “Generate Schema/User to Objects”.
Regards,
Vladka + TDM Team Message was edited by: vladka
Message was edited by: vladka
Hello Vladk,
I am using the oracle 11g, Version of TDM is 3.4.14.6. and its true, the checkbox (Generate SQL Only) is selected, so thats the cause the schemaname was not created. But I am using the checkbox for a reason. I would like to use the ‘CREATE OR REPLACE’ syntax for my procedures and without the checkbox the SQL Code comes only with the ‘CREATE’ syntax.
Besides that, when I create a script and activate the checkbox for the generation of the user/schema name to the objects, it would like to have a ‘all or nothing’ functionality, no matter how the objects are defined. so it would be a good idea, to check the objects if they already have the username or not and then add them to all objects without the username.
But for now I guess, I need to deactivate the checkbox (Generate SQL Only) and suit with the Create syntax ?
Ilja
Hello Ilja,
I would like to use the 'CREATE OR REPLACE' syntax for my procedures...
See the attached screenshot.
Regards,
Vladka
Hello Vladka,
very nice, worked for me, thanks a lot for your help. I am still a TDM beginner and I am sure there is a lot more to discover.
As a suggestion it would be nice to have the ‘all or nothing’ functionality with the username when creating a script. on the other hand it would be comfortable, when I have a checkbox in the edit box for the ‘CREATE OR REPLACE’ syntax, so I have a better control of the specific object rather then to set it up for all objects of that type. lets asume I have a procedure already in my databse, editing the procedure with TDM, then all I need to do is to copy and paste the procedure from the SQL Prieview and dont have to create a script.
thanks again
Ilja
Thanks.
Regards,
Vladka + TDM Team
Hello Ilja,
Thanks for your suggestions. We will discuss them in team.
Just some tips:
Hello Vladk,
thanks for your tips, for some cases before and after scripts are nice to have. While I am working with TDM and getting used to it, I found out that it would be nice to have the ability to fire an extra script not only at the start or end of the creation script, but in the middle, at a place I can define. For example I dont want to Create the Primarykeys inside TDM, but with a PL/SQL routine. Then I need to start the routine after the table creation but before the ForeignKey commands. I need to execute this script no matter what schema I generate. Would be nice if you could arrange those extra scripts at the “Order of generated Objects”, the same for the creation of the relationships. On the other hand I have initial Data I want to insert with the creation of the tables. This data would be bound to the schema and should only be generated at the end of the Objects, when the schema is generated.
I missing some other usefull features like the creation of object pattern. for example when I create a table, I want some “automatic” columns with the same name and type. of course you should be able to hide those columns, but when you generate the table, they will be added. and I have some more ideas, not sure where the best place is to add my suggestions.
Ilja
Message was edited by: Ilja
Hello Ilja,
Thanks for your suggestions.
Extra script - We will see what we can do about it. CR # 65 800.
Notes:
Automatic columns - it is not possible at the moment. However, you can use the scripting feature.
Find attached a script that adds an attribute with data type Char(40) to every entity in the model (Oracle 10g db).
Feel free to modify the script at your convenience.
Note to the script: ID of data type Char(x) is valid for Oracle 10g database. ID of data types of other database systems is available in the Reference Guide (Help menu, Expert mode must be on).
Run the script in Scripting Window (Tools menu, Expert mode on).
To turn on Expert Mode, select Settings | Options | General | Expert mode.
Thanks again for your great co-operation.
Regards,
Vladka + TDM Team
AddColumn.js (658 Bytes)
hello Vladk,
I already disabled the checkbox for generating keys and do it with aPL/SQL routine. the after scripts for some obejcts is nice to have, butwont work in my case. I need to wait till all tables are created first and then fire the script I want. the last table is not always the same in the generate file, but the script needs to fire any time, no matter what tables I generate
there is something else I recogniced about the primary keys. when you copy a table, the programm will rename the tablename, so you dont have the same name for different tables. but the name of the keys is still the same. that wont make sense to me.
about the automatic columns, thanks a lot for the attachment. but its not the same. I dont want those automatic columns in my model (visual). those columns are for example a date for the first insert or the last update of a record. thats nothing I need in my model, but when the script is generated, they should be added.
and I have more suggestions:
domains: I am working with domains only for the data types. that is handle so I need to define them only one time and not for every attribute. when I want to add a domain to a new or existing attribute, I get a list of all possible data types. but I would like to see only my custom domains. it would safe a lot of time. no clue if that is alreadypossible.
directories: you cannot assign the directory to a schema, so its always generated or never. but if I generate a script for each user I have, I have the directory code for each schema I create.
schema/owner assigment: would be nice to be able to assign a schema to all objects of a workspace.
order of generated objetcs: nice feature, but would be better to have more control over it, for example control the order of the foreign keys code.
same kind of columns: in some tables I have the same type of columns, so not only the same data type, but also the same name, etc. would be nice to be able to set up a definition for columns and save them under a name. all I need is to arrange that name to a new column or if I want to change the definition, all columns related to that name will get the changes aswell.
systemvariables: would be great to have some systemvariables for example objectname I can use for the creation of attributes in their names as PK_%tablename%.
shadow effect: the shadow is over the relationship lines, what makes it impobbile sometimes to see the cardinality.
and at the end I have a stupied question, so please dont laugh. You gave me a number for the extra script (CR # 65 800). where can I find that (link) on the website and other numbers ?
greetings
Ilja
Hello Ilja,
Thanks for your response and ideas. Let me reply/comment on them.
a]
I already disabled the checkbox for generating keys and do it with aPL/SQL routine. the after scripts for some obejcts is nice to have, butwont work in my case. I need to wait till all tables are created first and then fire the script I want. the last table is not always the same in the generate file, but the script needs to fire any time, no matter what tables I generate.
We understand but unfortunately don't have any solution for you at the moment. We will see what we can do about it (solution within user packages).
b]
there is something else I recogniced about the primary keys. when you copy a table, the programm will rename the tablename, so you dont have the same name for different tables. but the name of the keys is still the same. that wont make sense to me.
Yes, when you make a copy of entity, you create a new entity. It has a new name, however as it's a copy, the content is the same as in the original entity - same attributes, same keys etc. (of the same name).
c]
about the automatic columns, thanks a lot for the attachment. but its not the same. I dont want those automatic columns in my model (visual). those columns are for example a date for the first insert or the last update of a record. thats nothing I need in my model, but when the script is generated, they should be added.
Thanks for your explanation. Now we understand. You want to add column to the final DDL script. It is not possible at the moment, but we will work on it for next TDM version (solution within user packages).
d]
domains: I am working with domains only for the data types. that is handle so I need to define them only one time and not for every attribute. when I want to add a domain to a new or existing attribute, I get a list of all possible data types. but I would like to see only my custom domains. it would safe a lot of time. no clue if that is alreadypossible.
When you select the domain in the Entity Properties form, Attributes tab, directly in the grid, also data types, user data types etc. are available. However, if you edit the attribute, see the Domains box where you can find/select only domains.
e]
directories: you cannot assign the directory to a schema, so its always generated or never. but if I generate a script for each user I have, I have the directory code for each schema I create.
The object 'Directories' in Oracle is not linked to schema, so it is always generated. In Toad Data Modeler, you can disable generation of this object - from the OTPs list, clear the Directories checkbox.
f]
schema/owner assigment: would be nice to be able to assign a schema to all objects of a workspace.
See the Model menu | Schema/Owner Assignment.
http://modeling.inside.quest.com/entry.jspa?externalID=1694&categoryID=158
g]
order of generated objetcs: nice feature, but would be better to have more control over it, for example control the order of the foreign keys code.
Thanks. CR # 65 940.
h]
same kind of columns: in some tables I have the same type of columns, so not only the same data type, but also the same name, etc. would be nice to be able to set up a definition for columns and save them under a name. all I need is to arrange that name to a new column or if I want to change the definition, all columns related to that name will get the changes aswell.
Thanks for your suggestion. We will consider it. CR # 65 937.
Temporary solution: You can create a kind of a temporary entity with these attributes and copy them from there. Not to generate the entity, edit it and clear the Generate checkbox.
i]
systemvariables: would be great to have some systemvariables for example objectname I can use for the creation of attributes in their names as PK_%tablename%.
Thanks. Yes, this request is registered under CR # 55 315 and we will deal with it for the next TDM release.
You gave me a number for the extra script (CR # 65 800). where can I find that (link) on the website and other numbers ?
This is a number of a change request (bug or enhancement) in our internal system. Our developers will deal with this request. As soon as it is fixed and implemented in a TDM release, you will find the particular number in the Release Notes document.
Until it is fixed, you can ask here, in the community, about the progress/status.
Thanks for your great co-operation.
Regards,
Vladka + TDM Team
hello Vladka and TDM team,
first I want to say thank you for all the time you spend for your community. I think a product will never be perfect, so its up to the developer to listen to new suggestions and problems their customers face. and you doing a great job on that. but lets go back to “work”, I would like to answer your comments.
b] Yes, when you make acopy of entity, you create a new entity. It has a new name, however asit’s a copy, the content is the same as in the original entity - sameattributes, same keys etc. (of the same name).
I disagree on this, for me it makes no sense to copy a table, give it a new name, but leave everything else like the original table. take the programm excel for example, if you copy a cell, its not only a different name for the cell, more importend the content can change aswell and thats a very nice feature. as we all know, you cannot have the same objectname in the same schema. so why keep the name for the keys ? I mean your own function to verify a model will find a warning if you copy a table. so if you copy an object, changing the content aswell is nothing bad, when you make the change at the right place.
d] When you select the domain in the EntityProperties form, Attributes tab, directly in the grid, also data types,user data types etc. are available. However, if you edit the attribute,see the Domains box where you can find/select only domains.
thats true, if I edit a attribute, I have a box for my domains only. but when I add attributes to a table, I dont want to open the extra edit window for every attribute. I would like to use the table window to assign the domains to my attributes. I have about 12 domains I use, but when I change the data type, I have a big select box with domains mixed with “real” data types. would be nice to have an option somwhere “only show domains as data types”. its not only a time saver, its even more easy not to use a standard data type by accident.
e] Theobject ‘Directories’ in Oracle is not linked to schema, so it is alwaysgenerated. In Toad Data Modeler, you can disable generation of thisobject - from the OTPs list, clear the Directories checkbox.
of cousre you are right again, you cannot assign a directory to a schema, but thats not what we need. your programm offers to have more then one schema/user in one model. if you generate the DDL script, you can select which schema you want to create. lets say I have 3 different users in my model, I create them one by one, so I have 3 different DDL scripts at the end. and all 3 scripts will have a DDL statement to create the directory. maybe that makes sense in some cases, but for me one statement for one model is enough. so what we need is not to assign a directory to a schema in the database, but we need to assign it to the “creation time”. when we generate the DDL script over all users, then its clear we need the statement for the directory. but when we dont generate the hole model, then the directories need a mechanism, when to create them.
f] See the Model menu | Schema/Owner Assignment.
http://modeling.inside.quest.com/entry.jspa?externalID=1694&categoryID=158
well, I watched the movie, but this is not what I am looking for. in my opinion you need that function only, when you have more then one schema/user in your model. if not, I dont need to assign an objekt to a user, cause all objects belongs to the same user. so lets assume the case we have three users again. for every user I use one or more workspace, but I dont mix objects from different users in the same workspace. now I have to make sure, every object belongs to the proper user. I can do it when I create a new objects, but thats risky cause sometimes you just forget it. and cause I have more then one user, I cannot use the check box to assign them all to just one user. but when I have the option to assign all objects from one workspace to a user, it would safe a lot of time and less mistakes.
let me say some more words about modeling in general. for me I use a data modeler for three main reasons (of course there are more good reasons to use a modeler).
so the functions should based on design, speed and safty. I am not a designer, so cannot say much to that point, but the mechanism you use in a modeler to safe time and make it more safe are the same you use when you programm or if you want, the same how I design my data layout. The word I am looking for is . I dont want to do the same work more then one time. that belongs to many objects in the modeler, data types, attributes, tables, workspaces and much more. the better functions you offer to control my model, less time I will spend to create it and less mistakes I will make.
ok, besides that I have one more problem. I have about 6 users in my model, thats why I assign objects to their users. now I would like to generate the DDL script, not only for one user, but for all of them. how do I fire the script at once in my database ? I mean the obejcts from different users need different connections, yet I cannot see how to handle it without making scripts for every user…
Ilja
Hi Ilja,
thank you for your feedback. I really appreciate it! Let me write you comments to all items:
b) There is always something what can work better than it actually works 
In this case and for Oracle models, you are right. (The same action made in MySQL model is OK, because there are differences in what MySQL and Oracle support and allow you to do). Copying of objects can be improved… On other side, how ofter do you copy one object in the same model? I would say copying objects from one model to another is more frequent action. And adding just an increment is some sort of immediate visual warning… I created new change request for this: CR#66141.
d) Thank you for your suggestion. I created new change request: CR#66142
e) Click Help | Help Topics to open documentation and look at section Features -> OTPs. This is something what can help you. You can save settings you define on tab What to Generate of the DDL Script Generation dialog and generate SQL for one schema with default settings and then SQL for another with another OTPs settings.
f) I don’t know your model, but in some cases you have to specify schema. For example, in model for MS SQL Server, you have to specify schema if you wish to generate Descriptions. If no schema is specified, descriptions will not be generated (MS SQL requirement). Maybe the key question is: if you don’t mix object in workspaces, do you have relationships between them or do you use objects that belong to different users somewhere else? I mean… wouldn’t it be better to make several smaller models and store them under one project in Version manager, for example?
Once again, thank you for your feedback and please don’t hesitate to write me back. All your suggestions are welcome. We really want to know what you expect from the software and what you think should be improved. Sometimes the solution is a matter of priorities and sources that’s why I can’t promise all the suggestions will appear in TDM soon, but we do listen to our customers. Many change requests from this community were transformed to solutions and every new version of TDM contains features requested by community members.
Have a nice day,
Vaclav
hello Vaclav,
b) On other side, how ofter do you copy one object in the same model? Iwould say copying objects from one model to another is more frequentaction.
well, that makes my suggestion even more logical. I tried it, when you copy a table to another model, you dont change the name of the table, but inside the same model, you change it. so its dynamic, if you change the name or not. what I am asking for is a same for the all objectsnames you copy and not just the table name. if I copy an object into the same or different model, all I need to do is to check, if the name already exists in the same schema. if not leave it, else change it.
f) I mean… wouldn’t it be better to make several smaller models and store them under one project in Version manager, for example?
I would have the same problem, one script for every user. TDM offers the functionality to have multiple users for one Model. so what I want is to create one script, fire it on the database without changing it and drink a beer while is creating the objects. but right now I cannot do that, in the script I have objects from mutiple users, so I cannot fire it with one connection. so what I need is some kind of logic in the generate process, everytime an object has a different user then the last object, I need a connection change.
there is one more topic, when you verify the model (for example object or attribute names), you check for reserved names in the dbms ?
Ilja
Hi Ilja,
Thanks a lot.
b) O.K. I've added your comments to the CR 66 141.
f) I've created a new CR. We will see what we can do about it. 66 289.
When you verify the model (for example object or attribute names), you check for reserved names in the dbms ?
No, they're not checked during the model verification.
Just a note to e) - OTPs - flash movie on this issue:
http://modeling.inside.quest.com/entry.jspa?externalID=1695&categoryID=281
If you have any questions, suggestions, comments etc., please do not hesitate to write us back.
Thanks for your great co-operation!
Regards,
Vladka + TDM Team
Hello Vladka,
busy at work so cannot add much atm. but do you plan to check against reserved words when you verify a model ? the scripts will fail if you use reserved words…
Ilja
Hi Ilja,
It’s not planned at the moment. We will consider it. CR # 66 540.
Thanks.
Regards,
Vladka
Hello TDM team,
still not much time, but I found another thing. I think the reason for some problems I have is, when I use one projekt with different schema objects. When I add two tables, same name but different schema, I get no warning using the same name, what I consider as correct (different schema). But when I have a relationhip between two objects from a schema (lests call it A) and give it the same name as a relationship between two objects from schema B, I get a warning, that an object with the same name exist already, what I consider as wrong.
Ilja
Hi Ilja,
Yes, you are right. TDM returns a warning that the object (relationship) already exists. Nevertheless, you can save it regardless and continue modeling.
You consider this behavior as wrong. We will discuss it in our team. Thanks for raising this issue. CR # 66 743.
Regards,
Vladka
Hello Vladka,
I found another issue. when I create a script, and disable the option “Generate User/Schemato Objects”, all objetcs wont have the schema name before theirobjectnames. thats a nice feature and works well.
but when I have public synonyms and assign a schema.objectname to it, the schema named will be omitted aswell when generating the script. but for me that makes no sense when I have a public synonym.
Ilja
Hello Ilja,
To be sure:
You clear the “Generate User/Schema to Objects” checkbox for DDL/SQL script generation and want TDM to generate the public synonym this way:
CREATE TABLE TableName (a int)
/
CREATE PUBLIC SYNONYM SynonymName FOR SCOTT.TableName
/
Well, we think it could cause some troubles then, therefore think the way it is generated now is O.K.:
CREATE TABLE TableName (a int)
/
CREATE PUBLIC SYNONYM SynonymName FOR TableName
/
There can be users who are satisfied with how it works now and also users who would prefer your way.
Anyway, if you want to generate it another way than it is now, you can do the following:
Open the Synonym Properties dialog, from the Object box select and click the After Script tab where you can write a command, e.g.:
CREATE PUBLIC SYNONYM SynonymName FOR SCOTT.TableName
/
If you have any questions, please write us back. Thanks.
Regards,
Vladka + Mario
