Toad World® Forums

Generated Alter script has errors, things are not there, others already are


#1

ALTER TABLE “HM”.“MGL_ALLGEMEINE_DATEN” MODIFY (“SEX” CONSTRAINT “MGL_ALLG_DAT_MOW” CHECK (AsText))

And what’s that ?

GRANT SELECT ON “HM_TEST”.“PLZ_IN” TO “HM”, gives ORA-01749

/GRANT EXECUTE ON AIRAIS_MGL_SPRACHEN TO HM_WEB;

GRANT EXECUTE ON AIRAIS_MGL_SPRACHEN TO HM_ADMIN;
is has been generated. The original script shows it. It’s no change made by me.

Hello,

it’s boring !

Now I have a generated script and wants to make the changes, but I’m heavily struggeled in entities and indexes are not there but supposed to be, others are there, but believed they are not … .

After 80 errors I stopped running the script. And more are in …
I put the ORA-xxxx Messages in the script. Please see it. There is one 01741, many 02273, many 02449, many 02429, some 00942, three 01418, one 02260, one 00920,

In my model I devided my entities up into three Workspaces. Every Workspace has Tables in one user. The sript should do this.

Did I use it wrong ?

I have already corrected the identifying relationships like you told me. I have also the new scripts in place for generating Trigger for sequences and for FK case 773601

See attached files

And : what’s this ?



And take a look at this :


I clicked “generating with purge”. No Table was dropped with purge !#

  1. All TS_ Triggers are invalid

  2. FK Indices and Constraints are not generated ( see 773601 )

Please show me a solution for all 7 bullet points.

Best regards

Linus

AlterScript_PCA_aus_generiertem_HAMAA_extra_Reengeneered_mit_FK_Patch.sql (651 KB)

HalloMarie8.0.txp (2.46 MB)

HalloMarieRE_HAMAA.txp (5.45 MB)


#2

Hi Linus,

Just a quick update - we are checking the problem.
Please expect an email from me. Thanks for your patience.

Regards,

Vladka


#3

Hello Vladka,

I read your answer.
OK, I will check point for point.
I was a little … ( I don’t know to describe ) after all the errors.
OK, I’m in the BETA, but I have to do a little ( just a little ) work !
( And sometimes there is no blue sky visible … ).

I will answer, if something is not clear or not working.

Best regards

Linus


#4

Hi,

Here’s the reply to the errors reported.

  1. ORA-00920
    This problem was in previous Beta version. The problem occurred during script generation when instead of correct expression of check constraint a text As Text was shown. This problem was fixed for Beta 3.5.3.1 (CR 68 992)

  2. ORA-1418
    This is a problem of Using Index versus a logged in user.
    In the Key Properties dialog, box “Name of Using Index” you can write a name of the index that you want to use for the particular key. You can also use User/Schema there. Nevertheless, during reverse engineering only the index name is loaded there. So, when you execute the generated script then, Oracle will not find it (or it finds a wrong index from another user/schema) because it searches for it under another user/schema.

We will fix this problem of the RE for next TDM version.

At the moment we can offer you the following fix.
Please see below a script that you will run in Scripting Window (Tools menu, Expert mode must be enabled.)
The script will write information about User/Schema to every used box Using Index in the Key Properties dialog.
The User/Schema will be selected by the User/Schema used in particular entity where the appropriate key lies.

function main()
{
var app = System.GetInterface(“Application”);
var Model = app.Models.GetObject(0);
//… parameter in GetObject determines with which model the script should work.
//0 = first model listed in the Application View, 1 = second model listed in the Application View etc.
var e, k, Entity, Attribute, Key;

Model.Lock();
for (e=0; e<Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
Entity.Lock();
for (k=0; k<Entity.Keys.Count; k++)
{
Key = Entity.Keys.GetObject(k);
if ((Key.KeyItems.Count > 0) && (Key.UsingIXName != “”))
{
if ((Entity.DbOwner != null) && (Key.UsingIXName.search(/./) == -1))
{
Key.UsingIXName = Entity.DbOwner.Name+"."+Key.UsingIXName;
Log.Information(“Using Index in key “+Key.Name+” in entity “+Entity.Name+” was changed.”);
}
}
}
Entity.UnLock();
}
Model.UnLock();
}

  1. ORA-02273, 02429, 02449
    Unfortunately, there is a bug in RE.
    Problem description: When entities of same names exist in different users/schemas and these entities are connected with relationships of same names, TDM loads during RE these relationships only of one particular user/schema.
    Workaround: Please do not load tables from multiple users/schemas (if there are entities of same names in these users/schemas) during RE.

We will fix the problem. Thanks.

  1. ORA-00942
    This error occurred e.g. during deletion of table “HM_GEO_TEST”.“MDRT_156F8$”. This error says that a table that doesn’t exist is being deleted. At the moment we can’t explain it as we can see the table in the reversed model.
    We will further check it out. Thanks.

  2. ORA-01741
    This error (attempt to delete constraint but with an empty name) is possibly connected with problem 3. We need further verification.

  3. There is another error that occurs during creation of trigger “HM”.“MELDUNG_BUR”. It is a problem in your model HalloMarie(8.0)), because there is column MELDUNG (in “BEFORE UPDATE OF MELDUNG”) that doesn’t exist in the table “HM”.“SPERRGRUENDE”.

In my model I devided my entities up into three Workspaces. Every
Workspace has Tables in one user. The sript should do this.

TDM can’t do it automatically now. But yes, this request has already been raised. For now you can use the attached script. Please execute the script in Scripting Window.

Regards,

Vladka


#5

To let others know, now we’re discussing error # 1.
Linus:
You are writing the problem was fixed with 3.5.3.1. But this i s 3.5.3.1. It semms to me that it is n o t fixed.
For instance : Thje entity GEBUEHREN has an attribute TYP. This attribute has a check constraint and this check constraint is pointing to an check constraint rule. In the model itself I can not see SQL in the “check constraint property” whether it is selected. If I editd the check constraint rule, I can see “TYP IN (‘E’,‘Z’,’-’)”. May be you should take a “second look” at the solution for this error ?

Our reply:
We have verified it again and keep failing to simulate the problem in Beta 3.5.3.1. If you have the time, please try to make very simple models (two or three entities with some attributes plus check constraints + rules) illustrating the problem. We are very sorry but are not successful simulating it.
Thanks in advance.


We will continue the discussion in this thread only.
Thanks.

Vladka


#6

Hello Linus,

Thanks to your help we have managed to simulate the problem finally.
Problem description: When temporary tables are being created during alter script generation (tables with a check constraint), then ‘As Text’ is generated instead of the proper SQL of the check constraint.
This problem is registered under CR # 68 862.

We will fix it.

Thanks a lot!

Regards,

Vladka


#7

Hi,

Just a quick update: Problem resolved for the next TDM version.

Linus, thanks a lot!

Regards,

Vladka