Hi Tom!

What version of Oracle does the "systimestamp" call work with? Is it a 9i feature or does it work on 8.1.7 too?

Mohit.

Thanks Tom.

It worked perfect.

Now, Will the last three digits of the last 9 digits be ever used?

I tried "select cast(systimestamp as timestamp(9)) from dual". This always gives me the last three digits of the millisecond (or is it micro??) as "000"

ex.

28/02/2002 15:42:40:mmmmmm000

28/02/2002 15:42:40:nnnnnn000

I was just trying the following to add and substract two timestamp fields but adding is not allowed. What me be the reason that you can't add but can substract?

------------------------------------------------------
SCOTT > create table tt (t1 timestamp, t2 timestamp);

Table created.

SCOTT > insert into tt values(systimestamp,systimestamp);

1 row created.

SCOTT > select * from tt;

T1 T2
----------------------------- ------------------------------
28-APR-03 10.07.38.000000 PM 28-APR-03 10.07.38.000000 PM

SCOTT > select t1-t2 from tt;

T1-T2
--------------------------
+000000000 00:00:00.000000

SCOTT > select t1+t2 from tt;
select t1+t2 from tt
*
ERROR at line 1:
ORA-30087: Adding two datetime values is not allowed


SCOTT >

Tom,

Can you please let me know what mistake i am doing

$more /tmp/test.txt
Jan 01 2004 11:05:45:000PM|Jan 02 2005 11:10:45:000AM

SQL>create or replace directory external_tables_dir as  '/tmp/';
Directory created.

SQL> drop table test;
Table dropped.


SQL>CREATE TABLE test (
  a       TIMESTAMP(6),
  b      TIMESTAMP(6)
)
organization external
(type oracle_loader
default directory external_tables_dir
access parameters
(fields terminated by '|'
missing field values are null
(
a      cast(to_timestamp(":a", 'Mon dd yyyy hh:mi:ss:ffPM') as timestamp(6)),
b cast(to_timestamp(":b", 'Mon dd yyyy hh:mi:ss:ffPM') as timestamp(6))
   ) )
location ('test.txt')
)
parallel 2
reject limit unlimited;

Table created.


SQL> select * from test;

*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "identifier": expecting one of: "comma, char, date, defaultif, decimal, double, float,
integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc,
zoned"
KUP-01008: the bad identifier was: cast
KUP-01007: at line 4 column 5
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1

 

you would not apply sql functions in the et defintion.

that is what sql is for.

you can either:

a) map them as "varchar2" and then to_timestamp them when you SELECT from the ET

b) just use a format.


ops$tkyte@ORA9IR2> !echo "Jan 01 2004 11:05:45:123|Jan 02 2005 11:10:45:456" >/tmp/test.txt
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> create or replace directory external_tables_dir as  '/tmp/';
 
Directory created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table test;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE test (
  2    a          varchar2(50),
  3    b          varchar2(50)
  4  )
  5  organization external
  6  (type oracle_loader
  7  default directory external_tables_dir
  8  access parameters
  9  (fields terminated by '|'
 10  missing field values are null
 11  (
 12  a,
 13  b
 14     ) )
 15  location ('test.txt')
 16  )
 17  parallel 2
 18  reject limit unlimited;
 
Table created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> select
  2    cast(to_timestamp(a, 'Mon dd yyyy hh:mi:ss:ffPM') as timestamp(6)) a,
  3    cast(to_timestamp(b, 'Mon dd yyyy hh:mi:ss:ffPM') as timestamp(6)) b
  4    from test;
 
A                              B
------------------------------ ------------------------------
01-JAN-04 11.05.45.123000 AM   02-JAN-05 11.10.45.456000 AM
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> drop table test;
 
Table dropped.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> CREATE TABLE Test
  2  (
  3    A TIMESTAMP,
  4    B TIMESTAMP
  5  )
  6  ORGANIZATION external
  7  (
  8    TYPE oracle_loader
  9    DEFAULT DIRECTORY external_tables_dir
 10    ACCESS PARAMETERS
 11    (
 12      RECORDS DELIMITED BY NEWLINE CHARACTERSET WE8ISO8859P1
 13      FIELDS TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"' LDRTRIM
 14      REJECT ROWS WITH ALL NULL FIELDS
 15      (
 16        A CHAR(255)
 17          TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 18          DATE_FORMAT TIMESTAMP MASK "Mon dd yyyy hh:mi:ss:ffpm",
 19        B CHAR(255)
 20          TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"'
 21          DATE_FORMAT TIMESTAMP MASK "Mon dd yyyy hh:mi:ss:ffpm"
 22      )
 23    )
 24    location
 25    (
 26      'test.txt'
 27    )
 28  )REJECT LIMIT UNLIMITED
 29  /
 
Table created.
 
ops$tkyte@ORA9IR2> select * from test;
 
A                              B
------------------------------ ------------------------------
01-JAN-04 11.05.45.123000 AM   02-JAN-05 11.10.45.456000 AM
 
ops$tkyte@ORA9IR2>
 

Thanks for your prompt response.

I have hardly seen you type in "CAPS" especially syntaxes :-)

Tom,

Can external tables have columns as CLOB datatypes.

Thanks.

Thanks.

After substracting one timestamp value from another, I get +000000000 00:00:15. I used the substr function for now to get the 00:00:15 format. For example..

select substr(timestamp_value2 - timestamp_value1, 12, 8)
from time_table

However, what datatype should I use to store the result of the above SQL query, which will be used to create graphs.

Thanks in advance.

Tom,
9.2.0.4. We are using timestamp(6) with timezone columns for in table row level auditing. On the Solaris machine when inserting using systimestamp, systimestamp returns the fractional seconds with six positions, as in 15:51.12.539880. We are very dependant on all six values being present to identify rows in selection for reporting purposes, etc.

We were asked to port the db to a Windows server. Our queries now return multiple rows because we have found that on Windows the systimestamp value returns fractional seconds with only 3 digits followed always by three zeros, 000, as in 15:51.12.539000.

Why is this and how do we, or can we correctly get Windows to report values similar to UNIX?

Deae Mr. Kyte,

It has just happened today, that my Functions and Procedures and other queries that worked exactly fine before, stopped working.

After doing some R & D, I discovered that a simple select query that was being used in these objects was unexpectedly returning multiple Rows, i.e.-

Select sysdate from Dual;

returned -

SYSDATE
-----------
06/07/2006
06/07/2006
06/07/2006

where as it was working fine previously and used to return only single row -

SYSDATE
-----------
06/07/2006

I am unable to find out the reson behind such an unexpected behaviour and am facing very tough situation in getting the system alright.

Can you please help me in getting a way out of this Problem and kindly suggest, what all should I do to trace and rectify this problem? and also, what could have been the reson behind such an unexpected behaviour?

I am using Oracle 9iR2 Database on Red Hat Fedora Core Linux and Oracle9i client for Windows(XP).

Take Care, Regards.

Dear Mr. Kyte

In response to the above posted Query:

While digging through the details, I was able to trace out the "Devil", a small "Imp".

The problem was, rather than the normal single row entry with value 'X', there were 3 entries that exist in the table DUAL (how they got inserted there in is a long story). It was due to this that my queries using -
"select Sysdate from dual;"
were not working at all. On deleting these entries and restoring Dual to its original state, everything is working fine as before.

This shows us, one of the several importances of Dual and also lays stress on setting right type of permissions on all your Objects. isn't it?

Lesson Learnt: Do a detailed analyses of your application/database, make a comparison between the current and previous state of your ojects and act accordingly before taking any corrective measures. Finally, always start "Troubleshooting" your application/database from the very basic and primary level.

Thanks.

if i give minus value in flat file follwing error occurs ,how can i solve

CREATE TABLE a1
(
  c1   DATE,
  c2   NUMBER,
  c3   VARCHAR2(12),
  c4   NUMBER,
  c5   NUMBER,
  c6   NUMBER,
  c7   varchar(50),
  c8   NUMBER,
  c9   NUMBER,
  c10  NUMBER,
  c11  NUMBER,
  c12  NUMBER,
  c13  NUMBER,
  c14  NUMBER,
  c15  NUMBER,
  c16  NUMBER,
  c17  NUMBER,
  c18  NUMBER,
  c19  NUMBER,
  c20  NUMBER,
  c21  NUMBER,
  c22  NUMBER,
  c23  NUMBER,
  c24  NUMBER,
  c25  NUMBER,
  c26  NUMBER,
  c27  number,
  c28  NUMBER,
  c29  NUMBER,
  c30  NUMBER,
  c31  NUMBER,
  c32  NUMBER
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY DESKTOP
     ACCESS PARAMETERS 
       (        RECORDS DELIMITED BY NEWLINE
       READSIZE 1048576
       BADFILE DESKTOP:'a1.bad'
       DISCARDFILE DESKTOP:'a1.dsc'
       LOGFILE DESKTOP:'a1.log'
       
       FIELDS LRTRIM

       --MISSING FIELD VALUES ARE NULL
       REJECT ROWS WITH ALL NULL FIELDS
       (
         c1 CHAR(255) DATE_FORMAT DATE MASK "mm/dd/yyyy"  
                TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"', 
         c2 Double(255) TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c3 CHAR(255) TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c4 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c5 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c6 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c7 Oracle_number TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c8 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c9 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c10 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c11 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c12 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c13 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c14 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c15 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c16 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c17 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c18 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c19 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c20 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c21 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c22 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c23 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c24 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c25 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c26 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c27 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c28 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c29 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c30 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c31 Double TERMINATED BY "|" OPTIONALLY ENCLOSED BY '"',
         c32 Double 
       ) )
     LOCATION (DESKTOP:'AmortizationFile.txt')
  )
REJECT LIMIT Unlimited
;








SQL>  select c1 from a1;
 select c1 from a1
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "minussign": expecting one of: "column, exit, (,
missing, reject"
KUP-01007: at line 9 column 8
ORA-06512: at "SYS.ORACLE_LOADER", line 19
ORA-06512: at line 1


SQL>  

Tom,
thank you for the insight into the external tables.
I'm stuck with the load of the timestamp.
Do you mind to point me into the right direction please:
i'm on 10.2.04 and RH 5.3
$ more ./1.csv
2009-09-09 05:06:04.066162|2009-09-09 05:06:04.066162

create table t_1
(
  CREATED        timestamp ,
  MODIFIED       timestamp 
)
organization external ( 
directory rmbswloan_dir 
      access parameters (
            records delimited by newline
            skip 1
            string sizes are in characters
            nobadfile 
            nodiscardfile 
            logfile rmbswloan_dir_log:'1_%p.log'
              fields terminated by '|' missing field values are null
              (
                 CREATED        DATE_FORMAT  timestamp mask 'YYYY-MM-DD HH24:MI:SSXFF',
                 MODIFIED       DATE_FORMAT timestamp mask 'YYYY-MM-DD HH24:MI:SSXFF'  
              )
          )
location ('1.csv')
) ;

and

select * from t_1
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "date_format": expecting one of: "binary_double, binary_float, comma, char, date, defaultif, decimal, double, float, integer, (, nullif, oracle_date, oracle_number, position, raw, recnum, ), unsigned, varrawc, varchar, varraw, varcharc, zoned"
KUP-01007: at line 9 column 33
ORA-06512: at "SYS.ORACLE_LOADER", line 19

thank you

$ cat t.ctl
load data
INFILE *
INTO TABLE t
replace
FIELDS TERMINATED BY '|' trailing nullcols
(created timestamp "YYYY-MM-DD HH24:MI:SSXFF",
modified timestamp "YYYY-MM-DD HH24:MI:SSXFF")
begindata
2009-09-09 05:06:04.066162|2009-09-09 05:06:04.066162

CREATE TABLE "SYS_SQLLDR_X_EXT_T"
(
  "CREATED" TIMESTAMP(6),
  "MODIFIED" TIMESTAMP(6)
)
ORGANIZATION external
(
  TYPE oracle_loader
  DEFAULT DIRECTORY TEST
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII
    BADFILE 'DATA':'t.bad'
    LOGFILE 't.log_xt'
    READSIZE 1048576
    SKIP 8
    FIELDS TERMINATED BY "|" LDRTRIM
    MISSING FIELD VALUES ARE NULL
    REJECT ROWS WITH ALL NULL FIELDS
    (
      "CREATED" CHAR(255)
        TERMINATED BY "|"
        DATE_FORMAT TIMESTAMP MASK "YYYY-MM-DD HH24:MI:SSXFF",
      "MODIFIED" CHAR(255)
        TERMINATED BY "|"
        DATE_FORMAT TIMESTAMP MASK "YYYY-MM-DD HH24:MI:SSXFF"
    )
  )
  location
  (
    't.ctl'
  )
)REJECT LIMIT UNLIMITED

I just posted the question about timestamp in external table.
It appears to be a bug 3898572 opened for .0.3 and i can't see any fixes for it... Opened in 2003 and said to be a "defect".
lame :%

hmm. that's pretty interesting.

but select returns 0 rows...

i'll take it back. it does return the data. i have to look into this in more details.
thank you Tom!