Toad World® Forums

Create Oracle Table from ODBC Table


#1

Hi All,

This works fine with a table from a non-Oracle, ODBC-connected database:

SELECT * FROM
{{Odbc(“odbc_connection_string”)}}.non_oracle.tbl non_oracle_tbl

This works fine from an ODBC-connected Oracle database:

SELECT * FROM
{{Odbc(“odbc_connection_string”)}}.“schema.oracle_tbl” oracle_tbl

I’ve tried dozens of ways to create a table inside the Oracle database from a table inside the non-Oracle database to no avail.

Can anyone help me with the syntax / an example?

Thanks in advance.
–Michael–


#2

I don’t know if you’ll run into any data type problems from your ODBC source to your Oracle Source, but one thing you might try is to right-mouse click on your ODBC table, select the Generate SQL -> To Editor -> Creation Script menu. You could try running this DDL against your Oracle data source. I’d proceed with caution as there could be type issues between an ODBC source and an Oracle source.


#3

With Oracle you can create a table from the data in another Oracle database by using a dblink. But I do not know of a way to do this with a non-Oracle database.

Instead you can try one of the following export/import methods. Both are 2 step processes but get the job done when across database types.

  1. Export data into a common format such as Excel, CSV, etc. Then import and have the Import Wizard build a table of columns of the same data type. See attached png.

  2. Export the data and include the DDL. You may need to tweak the sytax. Then run in the editor on the other connection. See attached png.

Debbie
NewTable1.png


#4

With Oracle you can create a table from the data in another Oracle database by using a dblink. But I do not know of a way to do this with a non-Oracle database.

Instead you can try one of the following export/import methods. Both are 2 step processes but get the job done when across database types.

  1. Export data into a common format such as Excel, CSV, etc. Then import and have the Import Wizard build a table of columns of the same data type. See attached png.

  2. Export the data and include the DDL. You may need to tweak the sytax. Then run in the editor on the other connection. See attached png.

Debbie
NewTable2.png


#5

Thanks for your help. I’ll give these a shot.
–Michael–