Hello,
I'd like to have a local PostgreSQL copy of a table
stored (and growing) at the remote Oracle database:
SQL> desc qtrack;
Name
Null? Type
-----------------------------------------------------------------------------------------------------------------
-------- ----------------------------------------------------------------------------
ID
NOT NULL VARCHAR2(20)
EMAIL
VARCHAR2(320)
OSVERSION
VARCHAR2(30)
APPSVERSION
VARCHAR2(30)
QDATETIME
DATE
CATEGORY
VARCHAR2(120)
BETA_PROG
VARCHAR2(20)
CATINFO
VARCHAR2(120)
DEVINFO
VARCHAR2(4000)
NAME
VARCHAR2(20)
FORMFACTOR
VARCHAR2(10)
DETAILS
VARCHAR2(50)
EMAILID
VARCHAR2(16)
SQL> select id, qdatetime, dump(qdatetime) from qtrack where qdatetime
> sysdate - 1 order by qdatetime
ID QDATETIME
-------------------- ---------
DUMP(QDATETIME)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
20021211162813691 27-SEP-34
Typ=12 Len=7: 142,134,9,27,22,20,13
2002121202429070 28-SEP-34
Typ=12 Len=7: 142,134,9,28,8,34,20
20021212052520472 28-SEP-34
Typ=12 Len=7: 142,134,9,28,8,60,32
2002121310073187 28-SEP-34
Typ=12 Len=7: 142,134,9,28,16,20,48
......
10106 rows selected.
The ID is a string "20101210_some_random_numbers"
(not growing :-(
and they should have made it a primary key probably?)
I'm preparing a PHP-script to be run as a nightly cronjob
and will first find the latest qdatetime stored in my local
PostgreSQL database and then just "select" in remote Oracle,
"insert" into the local PostgreSQL database in a loop.
But I wonder if there is maybe a cleverer way to do this?
And I'm not sure how to copy the Oracle's strange DATE
column best into PostgreSQL, without losing precision?
Regards
Alex