Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtIic-00018I-6b for pgsql-admin@arkaria.postgresql.org; Mon, 11 Feb 2019 21:02:22 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gtIia-0007wS-Ek for pgsql-admin@arkaria.postgresql.org; Mon, 11 Feb 2019 21:02:20 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtIiZ-0007wK-La for pgsql-admin@lists.postgresql.org; Mon, 11 Feb 2019 21:02:20 +0000 Received: from sonic305-4.consmr.mail.bf2.yahoo.com ([74.6.133.43]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gtIiW-0004Pp-9l for pgsql-admin@lists.postgresql.org; Mon, 11 Feb 2019 21:02:18 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1549918934; bh=JoPmD61tihCdT6kopyzwpWCXwn4cYuOw3m+79fcLDUI=; h=Date:From:Reply-To:To:Cc:In-Reply-To:References:Subject:From:Subject; b=M5PAFmZczSrUwTb4K793Dt93llCnzbvSCe/JnqR8Qf94IIG1d+E3VS8wBZ+MgYtd7vOsXsUbEfTFY1cNx8b7Ptz3CzG/tCootJ/ohzc2fah0t1cLmGPDap/YsvPfmJUJf7YLLVM18rZekckIluKsTQ+QsTyddpz3yKHwi4Aiim+FY+F7MCvS7u1QeiQa3SeBxAPf1gmHrIKfR4E4s2P1OnGg5s2DIga72so1IVPr8wJCbtXzQOcVirMP/HWNRnriOoMHodELl1SwLFrIZfDZpr4DwTd9QkzO42s00hEi1/amd/Sw7KGOL0cbX5SjrDoAaNs8jL/INA6A92vr6wFvDA== X-YMail-OSG: Fz9biXcVM1nMZd_sSDs1EfI63wJoLxI4a3xvlcXtO0DXeRUUEXqYcNtfTNK2vZx E1MBy9W5bRVaJkZqjeHpuBOTtrTwx9JppIy0WtPHJJRl6y3XikTI4P5A7QgKEUwl8U2Z17HHBOU1 sUkWWBI5gv7vvT9jIoMCQMS88lqGP_Fb6Tbo49QXUDBPOeTaLXRw4A8ZNdnoV9bTM.JmvSw1OXGv qe6RTYpWJM9meunLotagkza.dxhnhQJYg.dOwUOo3ZA3_.YdAt04tWPDnq5WzcjppFX_eYgsouHx mLYBy6VLwFQOqK3Ghn9rEJ9lqiYYLlbLdzXVA.ulSVpsk5o8EbRonh8.c09100oR4FGLNMGbzWDm B6mim6cqZLEWcqRGQTqGQ1anxggZyr3M48cLjWT2e_ilK9nRFHMmW_qhTfKcO1.3xJ1BE847ZvLX pp9WOW47aLMk69URaTfHc_7fmfRKhJWdQoAq6PpuH7z6JbQw5AxVoDJuwI0sHaxa0vzIZS.NHwEM f_Sx6SWdHXH.ANZ4Rk9TkKeGr4ahU0qAbSVyhb5LWSFqHoG40L2iI9weJhnD2upXQc45s2kpi5db 5lJP8g.kiPmihNioLMC7KqL1tM1NLKBpT3LnL1PcvTaXuyDrgeoOtiCRwjqULmiK5VjJb6ZPjrzK tMQZUCCT2DRhfMzTSWzbj8XwPKndL9hBNx7CtFdvRewGlTjUtwIze18UjL52z.qTGAML3tnzhRuJ vuqBRdxNbdU0FCTvqKEtz7xJQeq_EPs1KS3SRX0kBWTRtAkHcSVSRPRVjSZy9muoe.qnL6WuTPuA Bh2zl24E42KtazQBbwY6yKI7lgff.nX.h6Lq.W0UEAe6HaRfaaJm1neQ2D4jNslocl6bBYZ8OUJf Kcty4bWGtksm6Jzvy3PAKnz9Yaeyw8mmKF7mX1lCSntZ7K1VlfpvYhBv9yKK2RKW3.Ntkt_4dEYM oNXiXJESB7.qHk3WL9z0nl4ntZ8znjgKd3sykgHIc5CVBeCk4d8djNWgdzX3UKlQi7Ca.a9WQH8z F3l4Y4TOoDxN0ySHeRqbTYlra7nhlZMO3cHUBvyA_ViF1nQsvKGmKAiPH0VZUvqq1tR8- Received: from sonic.gate.mail.ne1.yahoo.com by sonic305.consmr.mail.bf2.yahoo.com with HTTP; Mon, 11 Feb 2019 21:02:14 +0000 Date: Mon, 11 Feb 2019 21:02:13 +0000 (UTC) From: Pepe TD Vo Reply-To: Pepe TD Vo To: "David G. Johnston" Cc: Pgsql-admin Message-ID: <2069243125.1915245.1549918933268@mail.yahoo.com> In-Reply-To: References: <2112371589.1866229.1549913142122.ref@mail.yahoo.com> <2112371589.1866229.1549913142122@mail.yahoo.com> Subject: Re: insert data with invalid input syntax for integer:"42P01" MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_1915244_532065563.1549918933266" X-Mailer: WebService/1.1.13027 YahooMailNeo Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/71.0.3578.98 Safari/537.36 Content-Length: 10940 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk ------=_Part_1915244_532065563.1549918933266 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable the script was transferred/decoded from Oracle objects via Ora2pg.=C2=A0 Fr= om Oracle, the trigger and procedure were declared as number and once it de= coded into Postgres, it changed to bigint.=C2=A0 I have tried to change it = to "int" or "varchar" to test out and still not working. If the function cidr_staging.trigger_fct_gtr_stg_adjudicative_status withou= t create trigger "tr_stg_adjudicative_status_ins" before insert On cidr_sta= ging.stg_adjudicative_status for each row executive procedure=C2=A0idr_stag= ing.trigger_fct_gtr_stg_adjudicative_status()=C2=A0then the insert data is = fine. for the 'E, I did learn it encode binary data into a textual representation= and converts zero bytes and high bit set bytes to octal and doubles backsl= ashes. I have found a lot of scripts transferred from Oracle to Postgres using Ora= 2pg need to re-modified. And the reason, I added the schema_name on to matc= h the information/data ingest from many procedures information come of West= source databases.=C2=A0=C2=A0 v/r, 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 2:59 PM, David G. Johnston wrote: =20 On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo wrote: > I am still getting ERROR: invalid input syntax for integer:"42P01" when I= am insert a data into the table. Experience tells me "42P01" this is an SQL Error Code value.=C2=A0 The fact that the value doesn't appear in your input data means that something in the function is generating that value as opposed to it coming from the input.=C2=A0 Looking up that value in Appendix A informs us that it means: 42P01 undefined_table.=C2=A0 There is insufficient data supplied to pinpoint your overall problem more closely but the error of attempting to assign this specific text literal to an integer is easy to find. > declare > v_ErrorCode bigint; You assumed error codes were integers; you were wrong. > if NEW.mig_seg is null then >=C2=A0 =C2=A0 NEW.mig_seq:=3Dv_seq; > enf if; This is a straight-up syntax error if the code ever got this far so probably your runtime error is before this (unless you are not providing exact code) > if NEW.mig_filename is null then >=C2=A0 =C2=A0 NEW.mig_filename :=3D'Unknown'; > end if; > exception > when others then >=C2=A0 =C2=A0 v_ErrorCode :=3D SQLSTATE' Again, not even sure how this executes as written...but this is apparently the actual assignment location throwing the exception > even I take the "E" values out the error is still occurred That just indicates you still need to learn what the E'' string stuff is all about. > but if I dropped the function and trigger, the insert is fine Which means the trigger function has issues since a trigger is just a catalog entry David J. =20 ------=_Part_1915244_532065563.1549918933266 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
the script was transferred/decoded from Oracle objects= via Ora2pg.  From Oracle, the trigger and procedure were declared as = number and once it decoded into Postgres, it changed to bigint.  I hav= e tried to change it to "int" or "varchar" to test out and still not workin= g.

If the funct= ion cidr_staging.trigger_fct_gtr_stg_adjudicative_status without create tri= gger "tr_stg_adjudicative_status_ins" before insert On cidr_staging.stg_adj= udicative_status for each row executive procedure idr_staging.t= rigger_fct_gtr_stg_adjudicative_status() 
then the insert data is fine.

=
for the 'E, I= did learn it encode binary data into a textual representation and converts= zero bytes and high bit set bytes to octal and doubles backslashes.
<= div dir=3D"ltr" id=3D"yui_3_16_0_ym19_1_1549909664966_85528">
I have= found a lot of scripts transferred from Oracle to Postgres using Ora2pg ne= ed to re-modified. And the reason, I added the schema_name on to match the = information/data ingest from many procedures information come of West sourc= e databases.  

v/r,
=

Bach-Nga

No on= e in this world is pure and perfect.  If you avoid people for their mi= stakes 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 outwa= rd 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, Februa= ry 11, 2019 2:59 PM, David G. Johnston <david.g.johnston@gmail.com> w= rote:


On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <pepevo@ya= hoo.com> wrote:
> I am still getting ERROR: inv= alid input syntax for integer:"42P01" when I am insert a data into the tabl= e.

Experience tells me "42P01" this is= an SQL Error Code value.  The fact
that the value d= oesn't appear in your input data means that something
in = the function is generating that value as opposed to it coming from
the input.  Looking up that value in Appendix A informs us t= hat it
means: 42P01 undefined_table.  There is insuf= ficient data supplied to
pinpoint your overall problem mo= re closely but the error of attempting
to assign this spe= cific text literal to an integer is easy to find.

> declare
> v_ErrorCode bigint;

You assumed error codes were integers; you we= re wrong.

> if NEW.mig_seg is null = then
>    NEW.mig_seq:=3Dv_seq;
> enf if;

This is a straight-= up syntax error if the code ever got this far so
probably= your runtime error is before this (unless you are not
pr= oviding exact code)

> if NEW.mig_fi= lename is null then
>    NEW.mig_filename := =3D'Unknown';
> end if;
> excepti= on
> when others then
>  &nb= sp; v_ErrorCode :=3D SQLSTATE'

Again, = not even sure how this executes as written...but this is
= apparently the actual assignment location throwing the exception

> even I take the "E" values out the error = is still occurred

That just indicates = you still need to learn what the E'' string stuff
is all = about.

> but if I dropped the function and trigger, the insert = is fine


Which means the trigger = function has issues since a trigger is just a
catalog ent= ry

David J.



<= br>
------=_Part_1915244_532065563.1549918933266--