按条件导出数据

最新推荐文章于 2024-10-17 22:29:12 发布

原创最新推荐文章于 2024-10-17 22:29:12 发布 · 191 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle #Unix #Windows #SQL #C

ORACEL优化系列专栏收录该内容

6 篇文章

订阅专栏

本文介绍了解决在MS-Windows平台上使用Oracle Export工具时出现的EXP-00056及ORA-00911错误的方法。主要原因是查询参数中的语法错误导致，特别是涉及特殊字符时。文章提供了正确的语法示例来帮助用户避免此类错误。

Subject: EXP-56 and ORA-911 During Export Using The Query Clause
Doc ID: Note:205074.1 Type: PROBLEM
Last Revision Date: 25-OCT-2005 Status: PUBLISHED

Facts
~~~~~
1. Oracle Releases 8.1.7 or upwards on MS-Windows Platform.

2. Perform a Table export using the QUERY clause
e.g.:
C:\> exp scott/tiger tables=scott.emp
query=\"WHERE job=\'SALESMAN\' AND sal\<1600\"
file=PartEmp.dump log=PartEmpExp.log

Symptoms
~~~~~~~~
Your Export using the QUERY clause terminates successfully with
the following error messages:

... (left out first part)

About to export specified tables via Conventional Path ...
. . exporting table EMP
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
Export terminated successfully with warnings.

If you import the dump file in another existing user schema, e.g.:
C:\> imp system/manager fromuser=scott touser=steve
file=PartEmp.dump log=PartEmpImp.log
only the table without rows will be imported.

Changes
~~~~~~~
1. You recently added or modified the QUERY clause of your export statement.

2. You recently ported a Unix script on your MS-Windows platform.

Cause
~~~~~
You are most probably encountering these
EXP-00056: ORACLE error 911 encountered
ORA-00911: invalid character
error messages due to some syntax errors in your where condition specified
by the QUERY parameter.

The escape character ('\') has to be used in a different way depending your
platform.

Fix
~~~
Double-check the value assigned to your QUERY parameter.

On MS-Windows platforms, don't use the escape character (\) neither before
single quotes ('), double-quotes (" nor before any arithmetical signs (<, >
within the where condition.

You only need to enclose your QUERY parameter with either \" or with
triple quotes """.

For example:
To export a subset of scott's emp table equivalent to

SQL> SELECT * FROM emp WHERE job='SALESMAN' AND sal<1600;

On MS-Windows platforms, you'll need to use one of the following syntax:
Either:
C:\> exp scott/tiger tables=scott.emp
query=\"WHERE job='SALESMAN' AND sal<1600\"
file=PartEmp.dump log=PartEmpExp.log
or
C:\> exp scott/tiger tables=scott.emp
query="""WHERE job='SALESMAN' AND sal<1600"""
file=PartEmp.dump log=PartEmpExp.log

Instead of the following one used on most common Unix platforms:
$ exp scott/tiger tables=scott.emp
query=\"WHERE job=\'SALESMAN\' AND sal\<1600\"
file=PartEmp.dump log=PartEmpExp.log

Note that double quotes (", single quotes ('), greater than (> and lower
than (< are UNIX reserved characters and thus need to be escaped on UNIX
platforms.

Related Documents
~~~~~~~~~~~~~~~~~
- Note 91864.1 Query= Syntax in Export in 8i
- Oracle9i Database Utilities Release 2 (9.2), Chapter 1 - Export
available online :
http://docs.oracle.com/cd_a97630 ... 52/ch01.htm#1005843