2

In my other thread, I have asked how to extract only the last 3 months. However, I managed to extract data from the last 3 months only from one table and it extracted all data for other tables. I have several tables in my schema and the column names for the timestamp are different.

In my par file I have the following QUERY but it did not work. I got ORA-00911 error message. I would like to know if the syntax of the below query is correct/possible .

    QUERY=TABLE1,TABLE2:"where TABLE1_STARTTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy') and TABLE2_STARTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy');"
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Joie Tamayo
  • 501
  • 3
  • 8
  • 21
  • 2
    I don't know expdp but the error `ORA-00911` suggests invalic character. I would suggest you to recreate the same query in db to see if it is working. – Utsav Oct 16 '15 at 03:35
  • 2
    I'd guess that you don't want the semicolon at the end of the query string. If you remove that, does the error disappear? – Justin Cave Oct 16 '15 at 04:02
  • Actually, your date function `TO_DATE('01-AUG-2015','dd-mon-yyyy')` doesn't work in oracle. I don't know if that was your problem. But try `TO_DATE('01/08/2015','DD/MM/YYYY')` and let see what happen. – Hotdin Gurning Oct 16 '15 at 04:06

1 Answers1

1

QUERY=TABLE1,TABLE2:"where ... TO_DATE('01-AUG-2015','dd-mon-yyyy');"

Remove the semi-colon in the QUERY parameter.:

QUERY=TABLE1,TABLE2:"where TABLE1_STARTTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy') 
                     and TABLE2_STARTIME >= TO_DATE('01-AUG-2015','dd-mon-yyyy')"

On a side note:

Not directly related to your issue. But remember TO_DATE is NLS dependent. You should specify the NLS_DATE_LANGUAGE, else your query might fail for a different nls_date_language.

For example,

SQL> alter session set nls_date_language='FRENCH';

Session altered.

SQL> SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy') FROM DUAL;
SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy') FROM DUAL
               *
ERROR at line 1:
ORA-01843: not a valid month


SQL> SELECT TO_DATE('01-AUG-2015','dd-mon-yyyy', 'nls_date_language=ENGLISH') FROM DUAL;

TO_DATE('01
-----------
01-AO█T -15

I would prefer using ANSI Date literal, when you do not have any time portion. It is NLS independent. It uses a fixed format YYYY-MM-DD.

For example,

SQL> alter session set nls_date_language='FRENCH';

Session altered.

SQL> SELECT DATE '2015-08-01' FROM DUAL;

DATE'2015-0
-----------
01-AO█T -15

SQL> alter session set nls_date_language='AMERICAN';

Session altered.

SQL> SELECT DATE '2015-08-01' FROM DUAL;

DATE'2015
---------
01-AUG-15
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124