import schemas

[Enno]

Hello,

I'm trying to Import an Oracle database via ODBC. My Oracle db has multiple schemas. Some schemas have tables which have a name which is the same as some tables in another schema. Now when importing, the ODBC import wizard seems to combine the tables with the same name from different schemas into one table, and this will generate 'duplicate' error while executing the wizard.

Am I doing something wrong ? Did I forget to set some option ? Is the ODBC wizard capable of importing multiple schemas ?

Thanx,

Enno.

[Ritesh]

Hello

Can you put your question from MySQL point of view?

I am not very clear about what you mean by My Oracle db has multiple schemas

HTH

[Enno]

Ritesh wrote on Apr 25 2003, 05:34 PM:

Hello

Can you put your question from MySQL point of view?

I am not very clear about what you mean by My Oracle db has multiple schemas

HTH

Hi,

I don't know if I can. I'm a novice on databases.

But as I understand it, a schema is a collection of logical database objects, such as tables, views, etc. My oracle db has a number of schemas, and some of them look at the same table. I think it works something like this:

Table T1

Field F1

Field F2

Field F3

Schema S1

Table T1

Field F1

Field F2

Schema S2

Table T1

Field F1

Field F3

Now when I run the ODBC wizard and try to import Table T1, The grid shows me

Table T1

Field F1

Field F2

Field F1

Field F3

The wizard allows me to disable the import of the second Field F1, but then I have to manually import all tables separately, and check for duplicate fields, iso doing the whole db 'in a flash' 🙂

Does this make it any clearer ?

Enno.

[Ritesh]

Hello

When you try to Import data from Oracle ( or for the matter any Database )…. SQLyog will allow you to select all ( or individual ) tables.

This you will get in the Select Tables… page of the wizard 🙂 Select the tables which you want to import and press Next and Finish[/B. Thats it 🙂

If you are still facing any problem, then upload the screenshots of various pages of the wizard. Probably, I will be able to help you more on the subject 🙂

HTH

[Enno]

Ritesh wrote on Apr 26 2003, 12:14 PM:

Hello

When you try to Import data from Oracle ( or for the matter any Database )…. SQLyog will allow you to select all ( or individual ) tables.

This you will get in the Select Tables… page of the wizard  🙂 Select the tables which you want to import and press Next and Finish[/B. Thats it  🙂

If you are still facing any problem, then upload the screenshots of various pages of the wizard. Probably, I will be able to help you more on the subject  🙂

HTH

Hi,

I will try to explain what I mean with a few screen dumps (but that will make this post somewhat big).

First I will show you two screen dumps made of the Toad tool showing two different schemas (AIMS and AQUIRE) both holding a table with the same name (ARRAY_TYPE), and both tables have a column with the same name (ARRAY_TYPE). The files are called aims1.gif and aims2.gif.

Next I will show you 4 screen dumps of the SQLyog ODBC import wizard. I logon to the odbc dsn using the user id and password for the AIMS schema, select a table to import, ARRAY_TYPE (aims-yog1.gif), and click on the transform button which shows me the next two screen dumps (aims-yog2.gif and aims-yog3.gif) which shows the colums for the table ARRAY_TYPE from both the schemas AIMS and AQUIRE (and not just from the AIMS schema). When clicking on the 'Edit SQL' button, one can see why the problem occurs, that is, the sql is trying to create a table with two columns of the same name, ARRAY_TYPE (aims-yog4.gif).

So I think that SQLyog does not take the various different schemas/owners into account when building the 'select table' list/grid. I have a final Toad screendump (aims3.gif) which shows a view of the same database for the user 'sys'. It shows all the table columns for my database for a table called ARRAY_TYPE, and here I see the exact same list as I see on the 'Column Mapping and Transformation' screen of the odbc import wizard. Only the first column shows the schema/owner of the table.

I hope this clearifies thins a bit.

So my questions remain: Am I doing something wrong ? Did I forget to set some option ? Is the ODBC wizard capable of importing multiple schemas ?

Best regards,

Enno.

p.s. I put the screendumps in a word document. I don't know how to add multiple gifs to this text 🙁

[Ritesh]

Hello

I have downloaded the attachments. I will get back with more news as soon as possible 😀

[Ritesh]

We are still working on it…..

[Author]

Posts