single_quote

如果在使用数据泵时不采用parfile参数文件的话，query参数指定的查询条件是需要使用单引号括起来的，而当使用parfile时则不需要加上单引号，加上后反而会出现LPX-314: an internal failure occurred错误:

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:"where t1<2000"

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:32:33

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:where t1<2000
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-00936: missing expression
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:32:36

/* 不采用参数文件形式，没有加单引号的情况下出现ORA-00936: missing expression错误*/

[maclean@rh2 mesg]$ expdp maclean/maclean directory=dump  tables=estimate_me query=estimate_me:'"where t1<2000"'

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:33:39

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** directory=dump tables=estimate_me query=estimate_me:"where t1<2000"
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:33:43

/* 不采用参数文件，在query参数中加上单引号则导出成功 */

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:'"where t1<2000"'
directory=dump

[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp

[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par 

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:35:08

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
ORA-31693: Table data object "MACLEAN"."ESTIMATE_ME" failed to load/unload and is being skipped due to error:
ORA-06502: PL/SQL: numeric or value error
ORA-31605: the following was returned from LpxXSLSetTextVar in routine kuxslSetParam:
LPX-314: an internal failure occurred
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at 22:35:11

/* 采用parfile选项，参数文件中query参数加入单引号出现LPX-314: an internal failure occurred错误，导出失败*/

[maclean@rh2 mesg]$ cat quote.par
tables=estimate_me
query=estimate_me:"where t1<2000"
directory=dump
[maclean@rh2 mesg]$ rm /s01/dump/expdat.dmp
[maclean@rh2 mesg]$ expdp maclean/maclean parfile=quote.par           

Export: Release 10.2.0.4.0 - 64bit Production on Tuesday, 02 November, 2010 22:36:27

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MACLEAN"."SYS_EXPORT_TABLE_01":  maclean/******** parfile=quote.par
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "MACLEAN"."ESTIMATE_ME"                     32.42 KB    1999 rows
Master table "MACLEAN"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MACLEAN.SYS_EXPORT_TABLE_01 is:
  /s01/dump/expdat.dmp
Job "MACLEAN"."SYS_EXPORT_TABLE_01" successfully completed at 22:36:30

/* 采用parfile，参数文件中query未加入单引号，导出成功 */

1. QUERY in Parameter file.

Using the QUERY parameter in a parameter file is the preferred method. Put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* from table scott.emp all employees whose job is analyst or whose salary is 3000 or more; and
* from from table hr.departments all deparments of the employees whose job is analyst or whose salary is 3000 or more.

File: expdp_q.par
—————–
DIRECTORY = my_dir
DUMPFILE = exp_query.dmp
LOGFILE = exp_query.log
SCHEMAS = hr, scott
INCLUDE = TABLE:”IN (‘EMP’, ‘DEPARTMENTS’)”
QUERY = scott.emp:”WHERE job = ‘ANALYST’ OR sal >= 3000″
# place following 3 lines on one single line:
QUERY = hr.departments:”WHERE department_id IN (SELECT DISTINCT
department_id FROM hr.employees e, hr.jobs j WHERE e.job_id=j.job_id
AND UPPER(j.job_title) = ‘ANALYST’ OR e.salary >= 3000)”

— Run Export DataPump job:

%expdp system/manager parfile=expdp_q.par

Note that in this example the TABLES parameter cannot be used, because all table names that are specified at the TABLES parameter should reside in the same schema.
2. QUERY on Command line.

The QUERY parameter can also be used on the command line. Again, put double quotes around the text of the WHERE clause.

Example to export the following data with the Export Data Pump client:

* table scott.dept; and
* from table scott.emp all employees whose name starts with an ‘A’

— Example Windows platforms:
— Note that the double quote character needs to be ‘escaped’
— Place following statement on one single line:

D:\> expdp scott/tiger DIRECTORY=my_dir DUMPFILE=expdp_q.dmp
LOGFILE=expdp_q.log TABLES=emp,dept QUERY=emp:\”WHERE ename LIKE ‘A%’\”

— Example Unix platforms:
— Note that all special characters need to be ‘escaped’

% expdp scott/tiger DIRECTORY=my_dir \
DUMPFILE=expdp_q.dmp LOGFILE=expdp_q.log TABLES=emp,dept \
QUERY=emp:\”WHERE ename LIKE \’A\%\’\”

— Example VMS platform:
— Using three double-quote characters

$ expdp scott/tiger DIRECTORY=my_dir –
DUMPFILE=exp_cmd.dmp LOGFILE=exp_cmd.log TABLES=emp,dept –
QUERY=emp:”””WHERE ename LIKE ‘A%'”””

Note that with the original export client two jobs were required:
— Example Windows platforms:
— Place following statement on one single line:

D:\> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp
QUERY=\”WHERE ename LIKE ‘A%’\”

D:\> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example Unix platforms:

> exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp \
QUERY=\”WHERE ename LIKE \’A\%\’\”

> exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

— Example VMS platform:

$ exp scott/tiger FILE=exp_q1.dmp LOG=exp_q1.log TABLES=emp –
QUERY=”””WHERE ename LIKE ‘A%'”””

$ exp scott/tiger FILE=exp_q2.dmp LOG=exp_q2.log TABLES=dept

Note that with original export client in Oracle8i on VMS, the syntax was different (also note the extra space that is needed between two single quotes):
… QUERY=”‘WHERE ename LIKE \’A%\’ ‘”
That is: [double_quote][single_quote]WHERE ename LIKE [backslash][single_quote]A%[backslash][single_quote][space][single_quote][double_quote]

single quote needed in expdp query?