Date: Tue, 12 Feb 2019 16:25:48 +0000 (UTC)
From: Pepe TD Vo <pepevo@yahoo.com>
Reply-To: Pepe TD Vo <pepevo@yahoo.com>
To: "David G. Johnston" <david.g.johnston@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Message-ID: <1990155251.2422933.1549988748913@mail.yahoo.com>
Subject: Re: insert data with invalid input syntax for integer:"42P01"
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_2422932_1118950482.1549988748910"
References: <1990155251.2422933.1549988748913.ref@mail.yahoo.com>
Content-Length: 17958
Precedence: bulk

------=_Part_2422932_1118950482.1549988748910
Content-Type: text/plain; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

I just test out to remove " E' " to see it work because in the near future =
the syntax convert the data insert into Postgres will be without " E' "
I gave exact code converted from Oracle via Ora2pg without changing any.=C2=
=A0 I just worked around to change the data type to see it work unfortunate=
ly, it didn't work :( .=C2=A0 The insert is working with function but as so=
on as I put the trigger in, I get an error.=C2=A0=C2=A0
the original from Oracle is:

CREATE OR REPLACE EDITIONABLE TRIGGER "CIDR_STAGING"."TR_STG_ADJUDICATIVE_S=
TATUS_INS"before inserton cidr_staging.stg_Adjudicative_Statusreferencing n=
ew as new old as oldfor each rowdeclare=C2=A0 =C2=A0v_seq=C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 number :=3D0;=C2=A0 =C2=A0v_ErrorCode=C2=A0 =C2=A0=
 =C2=A0 number;=C2=A0 =C2=A0v_ErrorMsg=C2=A0 =C2=A0 =C2=A0 varchar2(512);=
=C2=A0 =C2=A0v_Module=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0varchar2(32) :=3D 'T=
R_STG_ADJUDICATIVE_STATUS_INS';begin=C2=A0 =C2=A0select sq_staging.nextval =
into v_seq from dual;=C2=A0 =C2=A0----=C2=A0 =C2=A0-- this is used to contr=
ol the insert process into the database=C2=A0 =C2=A0----=C2=A0 =C2=A0if :ne=
w.mig_seq is null then=C2=A0 =C2=A0 =C2=A0 :new.mig_seq :=3D v_seq;=C2=A0 =
=C2=A0end if;
=C2=A0 =C2=A0----=C2=A0 =C2=A0-- This just ensures that the filename is not=
 null=C2=A0 =C2=A0----=C2=A0 =C2=A0if :new.mig_filename is null then=C2=A0 =
=C2=A0 =C2=A0 :new.mig_filename :=3D 'Unknown';=C2=A0 =C2=A0end if;
------ Exception error handler----exception=C2=A0 =C2=A0when others then=C2=
=A0 =C2=A0 =C2=A0 v_ErrorCode :=3D SQLCODE;=C2=A0 =C2=A0 =C2=A0 v_ErrorMsg=
=C2=A0 :=3D SQLERRM;
=C2=A0 =C2=A0 =C2=A0 insert into cidrmgmt.errorlog(=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0tstamp, os_user,host,module,errorcode,errormsg)=C2=A0 =C2=A0 =C2=
=A0 values=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0(systimestamp, sys_context('use=
renv','session_user'),=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0sys_context('useren=
v','host'), v_Module, v_ErrorCode, v_ErrorMsg );end;/ALTER TRIGGER "CIDR_ST=
AGING"."TR_STG_ADJUDICATIVE_STATUS_INS" ENABLE;
v/r,=C2=A0
Bach-Nga

No one in this world is pure and perfect.=C2=A0 If you avoid people for the=
ir mistakes you will be alone. So judge less, love and forgive more.To call=
 him a dog hardly seems to do him justice though in as much as he had four =
legs, a tail, and barked, I admit he was, to all outward appearances. But t=
o those who knew him well, he was a perfect gentleman (Hermione Gingold)
**Live simply **Love generously **Care deeply **Speak kindly.*** Genuinely =
rich *** Faithful talent *** Sharing success=20

    On Monday, February 11, 2019 4:29 PM, David G. Johnston <david.g.johnst=
on@gmail.com> wrote:
=20

 On Mon, Feb 11, 2019 at 2:02 PM Pepe TD Vo <pepevo@yahoo.com> wrote:
> the script was transferred/decoded from Oracle objects via Ora2pg.=C2=A0 =