IMPORT FOREIGN SCHEMA
You can import multiple foreign table or stream definitions into s-Server automatically with the IMPORT FOREIGN SCHEMA statement. This is influenced by what the foreign data wrapper supports; for example, the SYS_JDBC wrapper supports IMPORT FOREIGN SCHEMA. This wrapper is described in the topic SYS_JDBC in this guide.
FROM SERVER <server-name>
<foreign-schema-name> := <schema_name>
[ [ LIMIT TO | EXCEPT ] ( <table-list> ) ]
[ TABLE_NAME LIKE <string-literal> ]
<server-name> := <identifier>
<table-name> := <object_name>
After executing IMPORT FOREIGN SCHEMA, the effect is the same as if you had executed several CREATE FOREIGN TABLE or CREATE FOREIGN STREAM commands by hand. The catalog contains the foreign table/stream definitions, and you can use them in queries.
As always for foreign tables/streams, only metadata is imported: the data remains in the foreign server, and SQLstream accesses it when you execute a query.
IMPORT FOREIGN SCHEMA Sales
FROM SERVER OraSalesServer
SELECT * FROM OraSales.emp;
The LIMIT TO clause restricts the import either to an explicit list of table names or to those matching a LIKE pattern. If a list is provided, all of the named tables or streams must exist on the foreign server or the import will fail. Conversely, the EXCEPT clause imports everything except those named in the list or matching the LIKE pattern. Note that the LIKE pattern is a non-standard extension.
For example, using the foreign table definition below, the import-foreign-schema command will import only table names starting with R:
CREATE FOREIGN TABLE MovieSchema.reviewers
OPTIONS ( directory 'review_facts', file 'reviewers.xml' )
IMPORT FOREIGN SCHEMA MovieFacts
LIMIT TO TABLE_NAME LIKE 'R%'
FROM SERVER MovieCatalog
Note that as with CREATE FOREIGN TABLE, local schema MovieSchema must already exist before the IMPORT statement is executed.
•The topic CREATE FOREIGN DATA WRAPPER in this guide.
•The topic CREATE SERVER in this guide.
•The topic CREATE FOREIGN TABLE in this guide.
•The topic CREATE FOREIGN STREAM in this guide.