Environment
Oracle 11.2.0.4Symptoms
Using parametersTABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"
output
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
UDI-00014: invalid value for parameter, 'query'
I believe Oracle cannot identify a table of the first claim_ID.
Solutions
Syntax
QUERY = [schema.][table_name:] query_clause
Using [schema.][table_name:] for the same query
TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY=CDR.F_CLAIM_WORK_CAPACITY:"where claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"
that works (the schema was remapped from CDR to Z)
. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows
The table alias used by Data Pump for the table being unloaded is
KU$
. Using KU$.TABLES= CDR.F_CLAIM_WORK_CAPACITY
QUERY="where KU$.claim_ID in (select claim_id from Z.E_ROLLING_CLAIM )"
works
. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows
It works for multiple tables too, elapsed 0 00:17:43
TABLES= CDR.F_CLAIM_WORK_CAPACITY,CDR.K_CLAIM_KEY_FIGURES
. . imported "Z"."K_CLAIM_KEY_FIGURES" 4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows
The following two parameters do not work and return the same error:
UDI-00014: invalid value for parameter, 'query'
QUERY="where exists (select 1 from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
QUERY="where exists (select claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
I understand that "select claim_ID" is ambiguously defined, but I expected "select 1" would work.
Anyway the following works fine.
QUERY="where exists (select rc.claim_ID from Z.E_ROLLING_CLAIM rc where rc.claim_id=ku$.claim_ID )"
. . imported "Z"."K_CLAIM_KEY_FIGURES" 4.088 GB 36215408 out of 41843789 rows
. . imported "Z"."F_CLAIM_WORK_CAPACITY" 9.391 MB 154122 out of 157095 rows
and it is a bit faster (elapsed 0 00:12:27) than "where claim_id in ()". Maybe just because of cache :-)