Dump of SQL is Missing Data when using Navicat.

[matthewfriend]

Hi Everyone:

I am using the latest version of Oracle Navicat Enterprise edition.

While I consider Navicat my favorite Oracle IDE, I am very disatisfied with its ability to dump/import data.

When I right click on a schema and select the dump option, it doesn't export important data such as names give to primary/foreign/constraints (instead it doesn't list the names and when an import is done everything is give one of oracles names like: sys23322322. Several times I have lost a lot of work this way.

The other thing it does not do properly is export views properly when dumping. If the views are very simple and don't use aliases it works ok. Where I work some of the views can be very large and include a lot of aliases. when a dump is done with Navicat, the aliases are never exported and the views as a result are often broken.

Am I missing some setting that will allow me to export these kinds of data or do I have to keep using SQL Developer to export schemas?

Matthew

[siuha]

We would like your assistance to identify the problem. Please provide information as follows:

1. Which version of Navicat are you using? e.g. Navicat for MySQL (Windows) 64-bit version 11.0.16.
2.The Oracle server version.
3. The exported SQL file created by Navicat. When you mention "it doesn't export important data such as names give to primary/foreign/constraints", would you describe more to us? If possible, you can also provide us screenshots showing the issue.

[matthewfriend]

Hi Siuha:

I was using Navicat Oracle 11.0.18, but just upgraded to version 11.1.
The Oracle Versions I have tried (and all of which have the same issue are: 11.0.2 Enterprise, 11.0.2 XE, and the latest version 12c.

I have linked to two files:
The first is an actual dump using Navicat 11.1. You will see that all the foreign key, constraint, and primary key names are not included (so when imported into another database Oracle assigns some generic name like sys34333 or whatever):

http://cwebpro.com/FARMING.sql

The other file is the same database dumped by a different product that includes all the information properly:

http://cwebpro.com/farmingsqldev.sql

As an example:

Navicat:
-- ----------------------------
-- Foreign Key structure for table "FARMING"."WINDROWS"
-- ----------------------------
ALTER TABLE "FARMING"."WINDROWS" ADD FOREIGN KEY ("CROP") REFERENCES "FARMING"."CROPS" ("CROP");
ALTER TABLE "FARMING"."WINDROWS" ADD FOREIGN KEY ("CUTTING") REFERENCES "FARMING"."CUTTINGS" ("CUTTING");
ALTER TABLE "FARMING"."WINDROWS" ADD FOREIGN KEY ("FIELD") REFERENCES "FARMING"."FIELDS" ("FIELD");
ALTER TABLE "FARMING"."WINDROWS" ADD FOREIGN KEY ("CROP", "GROWTH_STAGE") REFERENCES "FARMING"."GROWTH_STAGE" ("CROP", "GROWTH_STAGE");

Other SQL Product:
--------------------------------------------------------
-- Ref Constraints for Table WINDROWS
--------------------------------------------------------

ALTER TABLE "FARMING"."WINDROWS" ADD CONSTRAINT "FK_WD_CROPS" FOREIGN KEY ("CROP")
REFERENCES "FARMING"."CROPS" ("CROP") ENABLE;
ALTER TABLE "FARMING"."WINDROWS" ADD CONSTRAINT "FK_WD_CUTTINGS" FOREIGN KEY ("CUTTING")
REFERENCES "FARMING"."CUTTINGS" ("CUTTING") ENABLE;
ALTER TABLE "FARMING"."WINDROWS" ADD CONSTRAINT "FK_WD_FIELDS" FOREIGN KEY ("FIELD")
REFERENCES "FARMING"."FIELDS" ("FIELD") ENABLE;
ALTER TABLE "FARMING"."WINDROWS" ADD CONSTRAINT "FK_WD_GROWTH_STAGE" FOREIGN KEY ("CROP", "GROWTH_STAGE")
REFERENCES "FARMING"."GROWTH_STAGE" ("CROP", "GROWTH_STAGE") ENABLE;

While the constraints still will 'work' (though they become unintelligible with Oracles goofy automatic naming system), the view become broke if an alias is used since the alias does not export properly. Here is an example:

Navicat Export:

-- ----------------------------
-- View structure for ANIMAL_DIAGNOSIS_ACTIVITIES_T
-- ----------------------------
CREATE OR REPLACE FORCE VIEW "CATTLE"."ANIMAL_DIAGNOSIS_ACTIVITIES_T" AS
SELECT substr(al2.animal,3,6), AL2.ANIMAL, AL1.EXAM_DATE, AL1.DIAGNOSTICIAN,
AL1.DIAGNOSIS,AL1.severity, AL1.treatment_protocol,
AL2.RECORDING_POLESHED, AL2.DIAGNOSIS_LOCATION, AL3.OUTCOME,
AL3.OUTCOME_DATE,al1.diagnosis_observations,'Error '
FROM cattle.ANIMAL_DIAGNOSES_e AL1,
cattle.DIAGNOSIS_LOCATIONS_e AL2,
cattle.ANIMAL_DIAGNOSIS_OUTCOMES_e AL3
WHERE (AL1.ANIMAL=AL3.ANIMAL(+)
AND AL1.EXAM_DATE=AL3.EXAM_DATE(+)
AND AL1.DIAGNOSTICIAN=AL3.DIAGNOSTICIAN(+)
AND AL1.DIAGNOSIS=AL3.DIAGNOSIS(+)
AND AL2.ANIMAL=AL1.ANIMAL
AND AL2.EXAM_DATE=AL1.EXAM_DATE)
union
SELECT substr(al2.animal,3,6), AL2.ANIMAL, AL1.EXAM_DATE, AL1.DIAGNOSTICIAN,
AL1.DIAGNOSIS,AL1.severity, AL1.treatment_protocol,
AL2.RECORDING_POLESHED, AL2.DIAGNOSIS_LOCATION, AL3.OUTCOME,
AL3.OUTCOME_DATE,al1.diagnosis_observations, null
FROM CATTLE.ANIMAL_DIAGNOSES AL1,
CATTLE.DIAGNOSIS_LOCATIONS AL2,
CATTLE.ANIMAL_DIAGNOSIS_OUTCOMES AL3
WHERE (AL1.ANIMAL=AL3.ANIMAL(+)
AND AL1.EXAM_DATE=AL3.EXAM_DATE(+)
AND AL1.DIAGNOSTICIAN=AL3.DIAGNOSTICIAN(+)
AND AL1.DIAGNOSIS=AL3.DIAGNOSIS(+)
AND AL2.ANIMAL=AL1.ANIMAL
AND AL2.EXAM_DATE=AL1.EXAM_DATE);

Export from other SQL Product that includes the alias names for the view (other wise the view will NOT compile):

CREATE OR REPLACE FORCE EDITIONABLE VIEW "CATTLE"."ANIMAL_DIAGNOSIS_ACTIVITIES_T" ("FIELD_ANIMAL", "ANIMAL", "EXAM_DATE", "DIAGNOSTICIAN", "DIAGNOSIS", "SEVERITY", "TREATMENT_PROTOCOL", "RECORDING_POLESHED", "DIAGNOSIS_LOCATION", "OUTCOME", "OUTCOME_DATE", "DIAGNOSIS_OBSERVATIONS", "STATUS") AS
SELECT substr(al2.animal,3,6), AL2.ANIMAL, AL1.EXAM_DATE, AL1.DIAGNOSTICIAN,
AL1.DIAGNOSIS,AL1.severity, AL1.treatment_protocol,
AL2.RECORDING_POLESHED, AL2.DIAGNOSIS_LOCATION, AL3.OUTCOME,
AL3.OUTCOME_DATE,al1.diagnosis_observations,'Error '
FROM cattle.ANIMAL_DIAGNOSES_e AL1,
cattle.DIAGNOSIS_LOCATIONS_e AL2,
cattle.ANIMAL_DIAGNOSIS_OUTCOMES_e AL3
WHERE (AL1.ANIMAL=AL3.ANIMAL(+)
AND AL1.EXAM_DATE=AL3.EXAM_DATE(+)
AND AL1.DIAGNOSTICIAN=AL3.DIAGNOSTICIAN(+)
AND AL1.DIAGNOSIS=AL3.DIAGNOSIS(+)
AND AL2.ANIMAL=AL1.ANIMAL
AND AL2.EXAM_DATE=AL1.EXAM_DATE)
union
SELECT substr(al2.animal,3,6), AL2.ANIMAL, AL1.EXAM_DATE, AL1.DIAGNOSTICIAN,
AL1.DIAGNOSIS,AL1.severity, AL1.treatment_protocol,
AL2.RECORDING_POLESHED, AL2.DIAGNOSIS_LOCATION, AL3.OUTCOME,
AL3.OUTCOME_DATE,al1.diagnosis_observations, null
FROM CATTLE.ANIMAL_DIAGNOSES AL1,
CATTLE.DIAGNOSIS_LOCATIONS AL2,
CATTLE.ANIMAL_DIAGNOSIS_OUTCOMES AL3
WHERE (AL1.ANIMAL=AL3.ANIMAL(+)
AND AL1.EXAM_DATE=AL3.EXAM_DATE(+)
AND AL1.DIAGNOSTICIAN=AL3.DIAGNOSTICIAN(+)
AND AL1.DIAGNOSIS=AL3.DIAGNOSIS(+)
AND AL2.ANIMAL=AL1.ANIMAL
AND AL2.EXAM_DATE=AL1.EXAM_DATE);

[siuha]

Your request has been forwarded to our development team. We will keep improving our Navicat in the future.