6 Replies Latest reply: Jan 30, 2013 12:27 AM by 969526 RSS

    error In Query Level export with  multiple conditions

    969526
      When i am running the following Query for Export with i am getting the result.



      C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and sal !=2850"' LOG=log011.log FILE=exp.dmp

      Export: Release 10.2.0.1.0 - Production on Wed Jan 30 10:01:27 2013

      Copyright (c) 1982, 2005, Oracle. All rights reserved.


      Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
      With the Partitioning, OLAP and Data Mining options
      Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

      About to export specified tables via Conventional Path ...
      . . exporting table EMP 10 rows exported
      EXP-00091: Exporting questionable statistics.
      EXP-00091: Exporting questionable statistics.
      Export terminated successfully with warnings.



      But When I run with The following condition then it shows the following error...




      C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!='CLERK' "' LOG=log011.log FILE=Exp01.dmp
      LRM-00111: no closing quote for value ' LOG=log01'

      EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
      EXP-00000: Export terminated unsuccessfully


      C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY="WHERE deptno > 10 and job!='CLERK'" LOG=log5.log FILE=exp01.dmp
      LRM-00112: multiple values not allowed for parameter 'query'

      EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
      EXP-00000: Export terminated unsuccessfully


      Please suggest a solution for it.
        • 1. Re: error In Query Level export with  multiple conditions
          sb92075
          966523 wrote:
          Please suggest a solution for it.
          place everything except username & password in control file; so you don't fight the command line interpreter with "special" characters
          • 2. Re: error In Query Level export with  multiple conditions
            Padma....
            Hi,

            The single quotes used for CLERK are causing the issue most probably.
            C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!='CLERK' "' LOG=log011.log FILE=Exp01.dmp

            try replacing with this
            C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!=''CLERK'' "' LOG=log011.log FILE=Exp01.dmp

            Two single quotes(not double quotes) in the place of one single quote for CLERK.

            Thanks
            Padma...
            • 3. Re: error In Query Level export with  multiple conditions
              969526
              Padma.... wrote:
              Hi,

              The single quotes used for CLERK are causing the issue most probably.
              C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!='CLERK' "' LOG=log011.log FILE=Exp01.dmp

              try replacing with this
              C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!=''CLERK'' "' LOG=log011.log FILE=Exp01.dmp

              Two single quotes(not double quotes) in the place of one single quote for CLERK.

              Thanks
              Padma...
              Thanks A Lot...
              • 4. Re: error In Query Level export with  multiple conditions
                sb92075
                966523 wrote:
                Padma.... wrote:
                Hi,

                The single quotes used for CLERK are causing the issue most probably.
                C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!='CLERK' "' LOG=log011.log FILE=Exp01.dmp

                try replacing with this
                C:\Documents and Settings\ITL>exp scott/tiger@orcl tables=(emp) QUERY='"WHERE deptno > 10 and job!=''CLERK'' "' LOG=log011.log FILE=Exp01.dmp

                Two single quotes(not double quotes) in the place of one single quote for CLERK.

                Thanks
                Padma...
                Thanks A Lot...
                if/when you place all inside control file, then you do not have to worry about such complications
                • 5. Re: error In Query Level export with  multiple conditions
                  baskar.l
                  Hi ,

                  Please check this Export/Import DataPump Parameter QUERY - How to Specify a Query [ID 277010.1] On command line i guess you are missing the '\'

                  thank you,
                  baskar.l
                  • 6. Re: error In Query Level export with  multiple conditions
                    969526
                    baskar.l wrote:
                    Hi ,

                    Please check this Export/Import DataPump Parameter QUERY - How to Specify a Query [ID 277010.1] On command line i guess you are missing the '\'

                    thank you,
                    baskar.l
                    I am using EXP instead of EXPDP so it work with ' 'Clerk' '(two Single Quotas)

                    Edited by: 966523 on Jan 29, 2013 10:27 PM