Обсуждение: A cronjob for copying a table from Oracle

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

Alexander Farber, 10.12.2010 12:02:
> 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?

Oracle's DATE includes a time part as well.

So simply use a timestamp in PostgreSQL and everything should be fine.

Regards
Thomas

On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer <spam_eater@gmx.net> wrote:
>> And I'm not sure how to copy the Oracle's strange DATE
>> column best into PostgreSQL, without losing precision?
>
> Oracle's DATE includes a time part as well.
>
> So simply use a timestamp in PostgreSQL and everything should be fine.


Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp?

(I realize that this more an Oracle question, sorry)

What format string should I take for Oracle's to_date() function,
I don't see a format string to get epoch seconds there

Regards
Alex

Alexander Farber, 10.12.2010 12:53:
> On Fri, Dec 10, 2010 at 12:33 PM, Thomas Kellerer<spam_eater@gmx.net>  wrote:
>>> And I'm not sure how to copy the Oracle's strange DATE
>>> column best into PostgreSQL, without losing precision?
>>
>> Oracle's DATE includes a time part as well.
>>
>> So simply use a timestamp in PostgreSQL and everything should be fine.
>
>
> Yes, but how can I copy Oracle's DATE into PostgreSQL's timestamp?
>
> (I realize that this more an Oracle question, sorry)
>
> What format string should I take for Oracle's to_date() function,
> I don't see a format string to get epoch seconds there

I have no idea what you are doing in PHP, but why don't you simply generate a valid date/time literal for Postgres
usingthe to_char() function? 

Something like

SELECT 'TIMESTAMP '''||to_char(QDATETIME, 'YYYY-MM-DD HH24:MI:SS')||''''
FROM qtrack;

That literal can directly be used in an INSERT statement for PostgreSQL

Regards
Thomas

Please help, struggling since hours with this :-(

I've created the following table (columns here and in the proc
sorted alphabetically) to acquire data copied from Oracle:

# \d qtrack
                Table "public.qtrack"
   Column    |            Type             |   Modifiers
-------------+-----------------------------+---------------
 appsversion | character varying(30)       |
 beta_prog   | character varying(20)       |
 category    | character varying(120)      |
 catinfo     | character varying(120)      |
 details     | character varying(50)       |
 devinfo     | character varying(4000)     |
 emailid     | character varying(16)       |
 email       | character varying(320)      |
 formfactor  | character varying(10)       |
 id          | character varying(20)       | not null
 imei        | character varying(25)       |
 name        | character varying(20)       |
 osversion   | character varying(30)       |
 pin         | character varying(12)       |
 qdatetime   | timestamp without time zone |
 copied      | timestamp without time zone | default now()
Indexes:
    "qtrack_pkey" PRIMARY KEY, btree (id)

And for my "upsert" procedure I get the error:

SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too
long for type character varying(16)

CONTEXT:  SQL statement "update qtrack set APPSVERSION =  $1 ,
BETA_PROG =  $2 , CATEGORY =  $3 , CATINFO =  $4 , DETAILS =  $5 ,
DEVINFO =  $6 , EMAIL =  $7 , EMAILID =  $8 , FORMFACTOR =  $9 , ID =
$10 , IMEI =  $11 , NAME =  $12 , OSVERSION =  $13 , PIN =  $14 ,
QDATETIME =  $15 , COPIED = current_timestamp where ID =  $10 "
PL/pgSQL function "qtrack_upsert" line 2 at SQL statement

My "upsert" procedure is:

create or replace function qtrack_upsert(
        _APPSVERSION varchar,
        _BETA_PROG   varchar,
        _CATEGORY    varchar,
        _CATINFO     varchar,
        _DETAILS     varchar,
        _DEVINFO     varchar,
        _EMAILID     varchar,
        _EMAIL       varchar,
        _FORMFACTOR  varchar,
        _ID          varchar,
        _IMEI        varchar,
        _NAME        varchar,
        _OSVERSION   varchar,
        _PIN         varchar,
        _QDATETIME   timestamp
        ) returns void as $BODY$
        begin
                update qtrack set
                        APPSVERSION = _APPSVERSION,
                        BETA_PROG   = _BETA_PROG,
                        CATEGORY    = _CATEGORY,
                        CATINFO     = _CATINFO,
                        DETAILS     = _DETAILS,
                        DEVINFO     = _DEVINFO,
                        EMAIL       = _EMAIL,
                        EMAILID     = _EMAILID,
                        FORMFACTOR  = _FORMFACTOR,
                        ID          = _ID,
                        IMEI        = _IMEI,
                        NAME        = _NAME,
                        OSVERSION   = _OSVERSION,
                        PIN         = _PIN,
                        QDATETIME   = _QDATETIME,
                        COPIED      = current_timestamp
                where ID = _ID;

                if not found then
                        insert into qtrack (
                                APPSVERSION,
                                BETA_PROG,
                                CATEGORY,
                                CATINFO,
                                DETAILS,
                                DEVINFO,
                                EMAIL,
                                EMAILID,
                                FORMFACTOR,
                                ID,
                                IMEI,
                                NAME,
                                OSVERSION,
                                PIN,
                                QDATETIME
                        ) values (
                                _APPSVERSION,
                                _BETA_PROG,
                                _CATEGORY,
                                _CATINFO,
                                _DETAILS,
                                _DEVINFO,
                                _EMAIL,
                                _EMAILID,
                                _FORMFACTOR,
                                _ID,
                                _IMEI,
                                _NAME,
                                _OSVERSION,
                                _PIN,
                                _QDATETIME
                        );
                end if;
        end;
$BODY$ language plpgsql;

The weird thing is when I omit the 7th param
in my PHP code as shown below, then it works:

       $sth = $pg->prepare(SQL_UPSERT);
       while (($row = oci_fetch_array($stid,
OCI_NUM+OCI_RETURN_NULLS)) != false) {
               $sth->execute(array(
                $row[0],
                $row[1],
                $row[2],
                $row[3],
                $row[4],
                $row[5],
                null, #$row[6],
                $row[7],
                $row[8],
                $row[9],
                $row[10],
                $row[11],
                $row[12],
                $row[13],
                $row[14])
                );
      }

And I'm very confused why it says varying(16)  in the error message.
It should say varying(4000) instead.

Isn't this a bug? The 6th overflows somehow and gets into 7th

Please save me, I want to go home for weekend
Alex

On Friday 10 December 2010 8:51:19 am Alexander Farber wrote:
> Please help, struggling since hours with this :-(
>
> I've created the following table (columns here and in the proc
> sorted alphabetically) to acquire data copied from Oracle:
>
> # \d qtrack
>                 Table "public.qtrack"
>    Column    |            Type             |   Modifiers
> -------------+-----------------------------+---------------
>  appsversion | character varying(30)       |
>  beta_prog   | character varying(20)       |
>  category    | character varying(120)      |
>  catinfo     | character varying(120)      |
>  details     | character varying(50)       |
>  devinfo     | character varying(4000)     |
>  emailid     | character varying(16)       |
>  email       | character varying(320)      |
>  formfactor  | character varying(10)       |
>  id          | character varying(20)       | not null
>  imei        | character varying(25)       |
>  name        | character varying(20)       |
>  osversion   | character varying(30)       |
>  pin         | character varying(12)       |
>  qdatetime   | timestamp without time zone |
>  copied      | timestamp without time zone | default now()
> Indexes:
>     "qtrack_pkey" PRIMARY KEY, btree (id)
>
> And for my "upsert" procedure I get the error:
>
> SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too
> long for type character varying(16)
>
> CONTEXT:  SQL statement "update qtrack set APPSVERSION =  $1 ,
> BETA_PROG =  $2 , CATEGORY =  $3 , CATINFO =  $4 , DETAILS =  $5 ,
> DEVINFO =  $6 , EMAIL =  $7 , EMAILID =  $8 , FORMFACTOR =  $9 , ID =
> $10 , IMEI =  $11 , NAME =  $12 , OSVERSION =  $13 , PIN =  $14 ,
> QDATETIME =  $15 , COPIED = current_timestamp where ID =  $10 "
> PL/pgSQL function "qtrack_upsert" line 2 at SQL statement


Looks like you got your EMAIL and EMAILID reversed. In your argument list
EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL.

>
> My "upsert" procedure is:
>
> create or replace function qtrack_upsert(
>         _APPSVERSION varchar,
>         _BETA_PROG   varchar,
>         _CATEGORY    varchar,
>         _CATINFO     varchar,
>         _DETAILS     varchar,
>         _DEVINFO     varchar,
>         _EMAILID     varchar,
>         _EMAIL       varchar,
>         _FORMFACTOR  varchar,
>         _ID          varchar,
>         _IMEI        varchar,
>         _NAME        varchar,
>         _OSVERSION   varchar,
>         _PIN         varchar,
>         _QDATETIME   timestamp
>         ) returns void as $BODY$
>         begin
>                 update qtrack set
>                         APPSVERSION = _APPSVERSION,
>                         BETA_PROG   = _BETA_PROG,
>                         CATEGORY    = _CATEGORY,
>                         CATINFO     = _CATINFO,
>                         DETAILS     = _DETAILS,
>                         DEVINFO     = _DEVINFO,
>                         EMAIL       = _EMAIL,
>                         EMAILID     = _EMAILID,
>                         FORMFACTOR  = _FORMFACTOR,
>                         ID          = _ID,
>                         IMEI        = _IMEI,
>                         NAME        = _NAME,
>                         OSVERSION   = _OSVERSION,
>                         PIN         = _PIN,
>                         QDATETIME   = _QDATETIME,
>                         COPIED      = current_timestamp
>                 where ID = _ID;
>
>                 if not found then
>                         insert into qtrack (
>                                 APPSVERSION,
>                                 BETA_PROG,
>                                 CATEGORY,
>                                 CATINFO,
>                                 DETAILS,
>                                 DEVINFO,
>                                 EMAIL,
>                                 EMAILID,
>                                 FORMFACTOR,
>                                 ID,
>                                 IMEI,
>                                 NAME,
>                                 OSVERSION,
>                                 PIN,
>                                 QDATETIME
>                         ) values (
>                                 _APPSVERSION,
>                                 _BETA_PROG,
>                                 _CATEGORY,
>                                 _CATINFO,
>                                 _DETAILS,
>                                 _DEVINFO,
>                                 _EMAIL,
>                                 _EMAILID,
>                                 _FORMFACTOR,
>                                 _ID,
>                                 _IMEI,
>                                 _NAME,
>                                 _OSVERSION,
>                                 _PIN,
>                                 _QDATETIME
>                         );
>                 end if;
>         end;
> $BODY$ language plpgsql;
>
> The weird thing is when I omit the 7th param
> in my PHP code as shown below, then it works:
>
>        $sth = $pg->prepare(SQL_UPSERT);
>        while (($row = oci_fetch_array($stid,
> OCI_NUM+OCI_RETURN_NULLS)) != false) {
>                $sth->execute(array(
>                 $row[0],
>                 $row[1],
>                 $row[2],
>                 $row[3],
>                 $row[4],
>                 $row[5],
>                 null, #$row[6],
>                 $row[7],
>                 $row[8],
>                 $row[9],
>                 $row[10],
>                 $row[11],
>                 $row[12],
>                 $row[13],
>                 $row[14])
>                 );
>       }
>
> And I'm very confused why it says varying(16)  in the error message.
> It should say varying(4000) instead.
>
> Isn't this a bug? The 6th overflows somehow and gets into 7th
>
> Please save me, I want to go home for weekend
> Alex



--
Adrian Klaver
adrian.klaver@gmail.com

On Friday 10 December 2010 9:20:19 am Dmitriy Igrishin wrote:
> Hey Adrian,
>
> 2010/12/10 Adrian Klaver <adrian.klaver@gmail.com>
>
> > On Friday 10 December 2010 8:51:19 am Alexander Farber wrote:
> > > Please help, struggling since hours with this :-(
> > >
> > > I've created the following table (columns here and in the proc
> > > sorted alphabetically) to acquire data copied from Oracle:
> > >
> > > # \d qtrack
> > >                 Table "public.qtrack"
> > >    Column    |            Type             |   Modifiers
> > > -------------+-----------------------------+---------------
> > >  appsversion | character varying(30)       |
> > >  beta_prog   | character varying(20)       |
> > >  category    | character varying(120)      |
> > >  catinfo     | character varying(120)      |
> > >  details     | character varying(50)       |
> > >  devinfo     | character varying(4000)     |
> > >  emailid     | character varying(16)       |
> > >  email       | character varying(320)      |
> > >  formfactor  | character varying(10)       |
> > >  id          | character varying(20)       | not null
> > >  imei        | character varying(25)       |
> > >  name        | character varying(20)       |
> > >  osversion   | character varying(30)       |
> > >  pin         | character varying(12)       |
> > >  qdatetime   | timestamp without time zone |
> > >  copied      | timestamp without time zone | default now()
> > > Indexes:
> > >     "qtrack_pkey" PRIMARY KEY, btree (id)
> > >
> > > And for my "upsert" procedure I get the error:
> > >
> > > SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too
> > > long for type character varying(16)
> > >
> > > CONTEXT:  SQL statement "update qtrack set APPSVERSION =  $1 ,
> > > BETA_PROG =  $2 , CATEGORY =  $3 , CATINFO =  $4 , DETAILS =  $5 ,
> > > DEVINFO =  $6 , EMAIL =  $7 , EMAILID =  $8 , FORMFACTOR =  $9 , ID =
> > > $10 , IMEI =  $11 , NAME =  $12 , OSVERSION =  $13 , PIN =  $14 ,
> > > QDATETIME =  $15 , COPIED = current_timestamp where ID =  $10 "
> > > PL/pgSQL function "qtrack_upsert" line 2 at SQL statement
> >
> > Looks like you got your EMAIL and EMAILID reversed. In your argument list
> > EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL.
>
> Yes, but he refers arguments by name, rather than number. UPDATE statement
> seems to be correct in the function definition.

I am just looking at the CONTEXT message above and it showing EMAIL being
assigned the $7 variable, which according to his argument list is _EMAILID.
EMAIL and EMAILID are the only two fields where the variable number does not
match the variable/argument numbers and are in fact reversed. So something is
happening there and would explain the problem because you that would mean you
are trying to stuff a 320 char field into a 16 char slot :)

>
> > > My "upsert" procedure is:
> > >
> > > create or replace function qtrack_upsert(
> > >         _APPSVERSION varchar,
> > >         _BETA_PROG   varchar,
> > >         _CATEGORY    varchar,
> > >         _CATINFO     varchar,
> > >         _DETAILS     varchar,
> > >         _DEVINFO     varchar,
> > >         _EMAILID     varchar,
> > >         _EMAIL       varchar,
> > >         _FORMFACTOR  varchar,
> > >         _ID          varchar,
> > >         _IMEI        varchar,
> > >         _NAME        varchar,
> > >         _OSVERSION   varchar,
> > >         _PIN         varchar,
> > >         _QDATETIME   timestamp
> > >         ) returns void as $BODY$
> > >         begin
> > >                 update qtrack set
> > >                         APPSVERSION = _APPSVERSION,
> > >                         BETA_PROG   = _BETA_PROG,
> > >                         CATEGORY    = _CATEGORY,
> > >                         CATINFO     = _CATINFO,
> > >                         DETAILS     = _DETAILS,
> > >                         DEVINFO     = _DEVINFO,
> > >                         EMAIL       = _EMAIL,
> > >                         EMAILID     = _EMAILID,
> > >                         FORMFACTOR  = _FORMFACTOR,
> > >                         ID          = _ID,
> > >                         IMEI        = _IMEI,
> > >                         NAME        = _NAME,
> > >                         OSVERSION   = _OSVERSION,
> > >                         PIN         = _PIN,
> > >                         QDATETIME   = _QDATETIME,
> > >                         COPIED      = current_timestamp
> > >                 where ID = _ID;
> > >
> > >                 if not found then
> > >                         insert into qtrack (
> > >                                 APPSVERSION,
> > >                                 BETA_PROG,
> > >                                 CATEGORY,
> > >                                 CATINFO,
> > >                                 DETAILS,
> > >                                 DEVINFO,
> > >                                 EMAIL,
> > >                                 EMAILID,
> > >                                 FORMFACTOR,
> > >                                 ID,
> > >                                 IMEI,
> > >                                 NAME,
> > >                                 OSVERSION,
> > >                                 PIN,
> > >                                 QDATETIME
> > >                         ) values (
> > >                                 _APPSVERSION,
> > >                                 _BETA_PROG,
> > >                                 _CATEGORY,
> > >                                 _CATINFO,
> > >                                 _DETAILS,
> > >                                 _DEVINFO,
> > >                                 _EMAIL,
> > >                                 _EMAILID,
> > >                                 _FORMFACTOR,
> > >                                 _ID,
> > >                                 _IMEI,
> > >                                 _NAME,
> > >                                 _OSVERSION,
> > >                                 _PIN,
> > >                                 _QDATETIME
> > >                         );
> > >                 end if;
> > >         end;
> > > $BODY$ language plpgsql;




--
Adrian Klaver
adrian.klaver@gmail.com

On 12/10/2010 09:45 AM, Dmitriy Igrishin wrote:
> Huh! Yes, indeed ! But how is it possible ?! I see
>                         EMAIL       = _EMAIL,
>                         EMAILID     = _EMAILID,
>
> rather than
>
>                         EMAIL       = $7,
>                         EMAILID     = $8,
>
> in the function definition...

My guess the reversal is taking place in the PHP code. The table
definition and the argument list to the Pg function have one order for
emailid,email and the update and insert statements have another;
email,emailid.  I would guess that the PHP is building the row variables
using the SQL statement order and than passing that to the Pg function
which has a different order.


--
Adrian Klaver
adrian.klaver@gmail.com

On Fri, Dec 10, 2010 at 6:15 PM, Adrian Klaver <adrian.klaver@gmail.com> wrote:
> On Friday 10 December 2010 8:51:19 am Alexander Farber wrote:
>> SQLSTATE[22001]: String data, right truncated: 7 ERROR:  value too
>> long for type character varying(16)
>>
>> CONTEXT:  SQL statement "update qtrack set APPSVERSION =  $1 ,
>> BETA_PROG =  $2 , CATEGORY =  $3 , CATINFO =  $4 , DETAILS =  $5 ,
>> DEVINFO =  $6 , EMAIL =  $7 , EMAILID =  $8 , FORMFACTOR =  $9 , ID =
>> $10 , IMEI =  $11 , NAME =  $12 , OSVERSION =  $13 , PIN =  $14 ,
>> QDATETIME =  $15 , COPIED = current_timestamp where ID =  $10 "
>> PL/pgSQL function "qtrack_upsert" line 2 at SQL statement
>
>
> Looks like you got your EMAIL and EMAILID reversed. In your argument list
> EMAILID is 7th but it is getting the 8th variable, the reverse for EMAIL.

That was it Adrian, thank you so much! It was reversed in my
Oracle's select and thus the PostgreSQL's upsert was failing.

I was looking too many times at that spot, so I stopped really reading it.

Dmitiry, $7 and $8 etc. is probably what plpgsql
substitutes for _EMAIL and _EMAILID internally

Regards
Alex