Home » RDBMS Server » Server Utilities » Original Export with Query Parameter (Oracle 9i R2 + Sun Solaris)
Original Export with Query Parameter [message #349951] Tue, 23 September 2008 10:19 Go to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Hello,

Please Correct me the below syntax.


$HOSTNAME:$ORACLE_SID(DB)$PWD>exp babub/babu file=31736.dmp tables=TE.te_mth_dbase_inv_bal_all 
Query = \" WHERE item_id BETWEEN \1\ and \999999999999\ and LTRIM (RTRIM (MONTH)) = \'AUGUST'\ AND YEAR = \'2008'\"

syntax error: `(' unexpected


Actually SQL Query

SQL> SELECT count(creation_date)
FROM TE.te_mth_dbase_inv_bal_all
WHERE item_id BETWEEN 1 AND 999999999999
AND LTRIM (RTRIM (MONTH)) = 'AUGUST'
AND YEAR = '2008'


Thank you in advance

Babu

[Updated on: Tue, 23 September 2008 10:21]

Report message to a moderator

Re: Original Export with Query Parameter [message #349958 is a reply to message #349951] Tue, 23 September 2008 10:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't understand why you have \ around numbers.
Try to put some before ( and ).

Regards
Michel
Re: Original Export with Query Parameter [message #349964 is a reply to message #349958] Tue, 23 September 2008 10:50 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Thanks Mr.Michel,

Actually i take from

http://download.oracle.com/docs/cd/B19306_01/server.102/b14215/exp_imp.htm#SUTIL001

$HOSTNAME:$ORACLE_SID(DB)$PWD>exp babub/babu file=31736.dmp tables=TE.te_mth_dbase_inv_bal_all 

Query = \" WHERE item_id BETWEEN (1) and (999999999999) and \'LTRIM (RTRIM (MONTH))'\ = \'AUGUST'\ AND YEAR = \'2008'\"
syntax error: `(' unexpected

$HOSTNAME:$ORACLE_SID(DB)$PWD>syntax error: `TRIM' unexpected

$HOSTNAME:$ORACLE_SID(DB)$PWD>exp babub/babu file=31736.dmp tables=TE.te_mth_dbase_inv_bal_all 
Query = \" WHERE item_id BETWEEN \1\ and \999999999999\ and \'LTRIM (RTRIM (MONTH))\' = \'AUGUST\' AND YEAR = \'2008\'"
syntax error: `(' unexpected


Kindly correct me.

Thank you in advance

Babu
Re: Original Export with Query Parameter [message #349965 is a reply to message #349964] Tue, 23 September 2008 10:56 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


One more Mr.Michael.

Actually some thing i made mistake from here.

Quote:
\'LTRIM (RTRIM (MONTH))\' =


Thank you in advance.

Babu
Re: Original Export with Query Parameter [message #349973 is a reply to message #349965] Tue, 23 September 2008 11:39 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

SQL> SQL> SELECT count(creation_date)
FROM te.te_mth_dbase_inv_bal_all
WHERE item_id BETWEEN 1 AND 999999999999
AND LTRIM (RTRIM (MONTH)) = 'AUGUST'
AND YEAR = '2008'  2    3    4    5
  6  /

COUNT(CREATION_DATE)
--------------------
               40453

SQL> !
$HOSTNAME:$ORACLE_SID($ORACLE_ENV)$PWD>exit

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
ne-ocadb01:DP100(DB)/global/DIS/ENVAP/oracle/db/9.2.0>exp babub/babu file=31736.dmp tables=(TE.TE_MTH_DBASE_INV_BAL_ALL)
 Query = \" WHERE item_id BETWEEN \1\ and \999999999999\ and \'LTRIM\'\'RTRIM\'\'MONTH\' = \'AUGUST\' AND YEAR = \2008\" statistics=none

Export: Release 9.2.0.6.0 - Production on Tue Sep 23 17:37:55 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to TE
. . exporting table       TE_MTH_DBASE_INV_BAL_ALL          0 rows exported
Export terminated successfully without warnings.


I think i made some mistakes. Please correct me.

Thank you in advance.

Babu

[Updated on: Tue, 23 September 2008 11:40]

Report message to a moderator

Re: Original Export with Query Parameter [message #349985 is a reply to message #349973] Tue, 23 September 2008 12:03 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'd try:
\" WHERE item_id BETWEEN 1 and 999999999999 and LTRIM \(RTRIM \(MONTH\)\) = 'AUGUST' AND YEAR = '2008'\"

Regards
Michel

[Updated on: Tue, 23 September 2008 12:04]

Report message to a moderator

Re: Original Export with Query Parameter [message #349986 is a reply to message #349985] Tue, 23 September 2008 12:05 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

ne-ocadb01:DP100(DB)/global/DIS/ENVAP/oracle/db/9.2.0>exp babub/babu file=31736.dmp tables=(TE.TE_MTH_DBASE_INV_BAL_ALL) 

Query = \" WHERE item_id BETWEEN 1 and 999999999999 and LTRIM \(RTRIM \(MONTH\)\) = 'AUGUST' AND YEAR = '2008'\"

Export: Release 9.2.0.6.0 - Production on Tue Sep 23 18:05:12 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to TE
. . exporting table       TE_MTH_DBASE_INV_BAL_ALL
EXP-00056: ORACLE error 904 encountered
ORA-00904: "AUGUST": invalid identifier
Export terminated successfully with warnings.


Babu
Re: Original Export with Query Parameter [message #349988 is a reply to message #349986] Tue, 23 September 2008 12:07 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Hello Again,

Thanks for your good support.

Now it's working..


Query = \" WHERE item_id BETWEEN 1 and 999999999999 and LTRIM \(RTRIM \(MONTH\)\) = \'AUGUST\' AND YEAR = '2008'\"

Export: Release 9.2.0.6.0 - Production on Tue Sep 23 18:05:58 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
Current user changed to TE
. . exporting table       TE_MTH_DBASE_INV_BAL_ALL      40453 rows exported
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
EXP-00091: Exporting questionable statistics.
Export terminated successfully with warnings.


But as per your syntax & documentation syntax total differ..

Babu
Re: Original Export with Query Parameter [message #349989 is a reply to message #349986] Tue, 23 September 2008 12:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Ok, also protect the quote:
\" WHERE item_id BETWEEN 1 and 999999999999 and LTRIM \(RTRIM \(MONTH\)\) = \'AUGUST\' AND YEAR = \'2008\'\"

Regards
Michel

Re: Original Export with Query Parameter [message #349990 is a reply to message #349989] Tue, 23 September 2008 12:08 Go to previous message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member

Once again Thanks.

Mr.Michael, I already replied.

But as per your syntax & documentation syntax total differ..

Please advice me.

Babu

[Updated on: Tue, 23 September 2008 16:16]

Report message to a moderator

Previous Topic: Exports
Next Topic: How to Export only packages and procedures
Goto Forum:
  


Current Time: Fri Dec 09 07:56:15 CST 2016

Total time taken to generate the page: 0.13434 seconds