Re: Diff QUERY= syntax in EXP PARFILE for 8.1.7 -vs- 10.2?

On Sep 13, 11:46 am, joel garry <joel-ga..._at_home.com> wrote:
> On Sep 12, 10:43 am, "John L. Allen" <allen..._at_mail.northgrum.com>
> wrote:
>
>
>
> > Hi,
>
> > I am wondering why an exp command works with Oracle 8.1.7, but not
> > with Oracle 10.2.0.4.  Here's my input file and the exp command that
> > uses it:
>
> > $ cat inp3.exp
> > FILE=/tmp/confitem.dat
> > LOG=/tmp/confitem.log
> > TABLES=( \"VPMADM.configuration_item_\" )
> > BUFFER=40000
> > TRIGGERS=N
> > GRANTS=N
> > INDEXES=N
> > ROWS=Y
> > QUERY="where \"$COID\" = '414199C0C52A6416'"
>
> > $ exp vpmadm/xxxxxxxx parfile=inp3.exp
>
> > Here's the successful output with 8.1.7:
>
> > Export: Release 8.1.7.4.0 - Production on Fri Sep 12 13:32:29 2008
>
> > (c) Copyright 2000 Oracle Corporation.  All rights reserved.
>
> > Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 -
> > Production
> > With the Partitioning option
> > JServer Release 8.1.7.4.0 - Production
> > Export done in US7ASCII character set and US7ASCII NCHAR character set
> > Note: grants on tables/views/sequences/roles will not be exported
> > Note: indexes on tables will not be exported
>
> > About to export specified tables via Conventional Path ...
> > . . exporting table            configuration_item_          1 rows
> > exported
> > Export terminated successfully without warnings.
>
> > And here's the error with 10.2.0.3:
>
> > LRM-00111: no closing quote for value '
> > '
> > LRM-00113: error when processing file 'inp3.exp'
>
> > EXP-00019: failed to process parameters, type 'EXP HELP=Y' for help
> > EXP-00000: Export terminated unsuccessfully
>
> > It appears to be complaining about the QUERY line:
>
> > QUERY="where \"$COID\" = '414199C0C52A6416'"
>
> > And yes, the field name is really $COID, which seems to be crux of the
> > problem.
>
> > I've tried various versions of this line without success.   The
> > closest I can get to running is with
>
> > QUERY="where '$COID' = '414199C0C52A6416'"
>
> > but it's not exporting anything like it should:
>
> > Export: Release 10.2.0.3.0 - Production on Fri Sep 12 13:36:33 2008
>
> > Copyright (c) 1982, 2005, Oracle.  All rights reserved.
>
> > Connected to: Oracle Database 10g Enterprise Edition Release
> > 10.2.0.3.0 - 64bit Production
> > With the Partitioning, OLAP and Data Mining options
> > Export done in US7ASCII character set and UTF8 NCHAR character set
> > server uses WE8ISO8859P1 character set (possible charset conversion)
> > Note: grants on tables/views/sequences/roles will not be exported
> > Note: indexes on tables will not be exported
>
> > About to export specified tables via Conventional Path ...
> > . . exporting table            configuration_item_          0 rows
> > exported
> > EXP-00091: Exporting questionable statistics.
> > Export terminated successfully with warnings.
>
> Just a wild guess, have you tried a backslash before the dollar sign?
> And I agree with Daniel, be sure the query works with sqlplus.

Well, oddly enough, the solution turned out to be that with 10g, I can no longer backwhack the double quotes. I have to double them up instead. Ie., though this query string works with 8.1.7:

QUERY="where \"$COID\" = '414199C0C52A6416'"

I have to use this with 10g:

QUERY="where ""$COID"" = '414199C0C52A6416'"

Granted, 8.1.7 is pretty old, but still, I am really surprised that Oracle would invalidate old quoting syntax this way.

John. Received on Sun Sep 14 2008 - 11:04:15 CDT