From: Pepe TD Vo <pepevo@yahoo.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Pgsql-admin <pgsql-admin@lists.postgresql.org>
Subject: Re: insert data with invalid input syntax for integer:"42P01"
Date: Tue, 12 Feb 2019 16:25:48 +0000 (UTC)
Message-ID: <1990155251.2422933.1549988748913@mail.yahoo.com> (raw)
References: <1990155251.2422933.1549988748913.ref@mail.yahoo.com>
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. I just worked around to change the data type to see it work unfortunately, it didn't work :( . The insert is working with function but as soon as I put the trigger in, I get an error.
the original from Oracle is:
CREATE OR REPLACE EDITIONABLE TRIGGER "CIDR_STAGING"."TR_STG_ADJUDICATIVE_STATUS_INS"before inserton cidr_staging.stg_Adjudicative_Statusreferencing new as new old as oldfor each rowdeclare v_seq number :=0; v_ErrorCode number; v_ErrorMsg varchar2(512); v_Module varchar2(32) := 'TR_STG_ADJUDICATIVE_STATUS_INS';begin select sq_staging.nextval into v_seq from dual; ---- -- this is used to control the insert process into the database ---- if :new.mig_seq is null then :new.mig_seq := v_seq; end if;
---- -- This just ensures that the filename is not null ---- if :new.mig_filename is null then :new.mig_filename := 'Unknown'; end if;
------ Exception error handler----exception when others then v_ErrorCode := SQLCODE; v_ErrorMsg := SQLERRM;
insert into cidrmgmt.errorlog( tstamp, os_user,host,module,errorcode,errormsg) values (systimestamp, sys_context('userenv','session_user'), sys_context('userenv','host'), v_Module, v_ErrorCode, v_ErrorMsg );end;/ALTER TRIGGER "CIDR_STAGING"."TR_STG_ADJUDICATIVE_STATUS_INS" ENABLE;
v/r,
Bach-Nga
No one in this world is pure and perfect. If you avoid people for their 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 to 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
On Monday, February 11, 2019 4:29 PM, David G. Johnston <david.g.johnston@gmail.com> wrote:
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. =
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: pepevo@yahoo.com, david.g.johnston@gmail.com, pgsql-admin@lists.postgresql.org
Subject: Re: insert data with invalid input syntax for integer:"42P01"
In-Reply-To: <1990155251.2422933.1549988748913@mail.yahoo.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox