insert data with invalid input syntax for integer:"42P01"

classic Classic list List threaded Threaded
12 messages Options
Reply | Threaded
Open this post in threaded view
|

insert data with invalid input syntax for integer:"42P01"

Pepe TD Vo
Hello,

I am still getting ERROR: invalid input syntax for integer:"42P01" when I am insert a data into the table.
CONTEXT: PL/pgSQL function cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins() line 29 at assignment.
Insert query:
insert into cidr_staging.stg_adjudicative_status (receipt_number, adjudicative_action_date, adjudicative_time_stamp, adjudicative_status, mig_filename, mig_insert_dt, mig_modified_dt, mig_seq) values (E'LIN1890030316', E'20180409',E'0752',E'Dennied',E'c3_20180626_adjudicativestatus_000.txt',NULL,NULL,172011);
the trigger is:
Create or replace function cidr_staging.trigger_fct_tr_stg_adjudicative_status_in() RETURNS trigger AS $BODY$
declare
v_seq bigint:=0;
v_ErrorCode bigint;
V_ErrorMsg varchar(512)'
v_Module varchar(32):= 'TR_STG_ADJUDICATIVE_STATUS_INS';
BEGIN
BEGIN
select nextval('sq_staging') into STRICT v_seq;
if NEW.mig_seg is null then
   NEW.mig_seq:=v_seq;
enf if;
if NEW.mig_filename is null then
   NEW.mig_filename :='Unknown';
end if;
exception
when others then
   v_ErrorCode := SQLSTATE'
   v_ErrorMsg := SQLERRM;
   insert into cidrmgmt.errorlog(stamp, os_user, host, module, errorcode, erromsg) values (CURRENT_TIMESTAMP, sys_context('userenv','session_user'), sys_context('userenv','host'),v_Module, v_ErrorCode, v_ErrorMsg);
END;
RETURN NEW;
end
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER tr_stg_adjudicative_status_ins BEFORE INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW
  EXECUTE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins();
 
the table definition is:
create table cidr_staging.stg_adjudicative_status (
receipt_number character varying (13) not null,
adjudicative_action_date character varying(8), 
adjudicative_time_stamp character varying(4),
adjudicative_status character varying(50,
mig_file_name character varying(80) not null,
mig_insert_dt timestamp without time zone,
mig_modified_dt timestamp without time zone,
mig_seg bigint not null);

Triggers:"tr_stg_adjudicative_status_ins" before INSERT ON cidr_staging.stg_adjudicative_status FOR EACH ROW EXECUTIVE PROCEDURE cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins()


even I take the "E" values out the error is still occurred but if I dropped the function and trigger, the insert is fine


thank you so much for looking into this error.
 
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
Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Scott Ribe-2
your trigger is defined to call cidr_staging.trigger_fct_tr_stg_adjudicative_status_ins, the error is report from there, but you give a definition for cidr_staging.trigger_fct_tr_stg_adjudicative_status_in, is that a copy/paste error, or have you actually posted the definition of a different procedure than the one in question?

> even I take the "E" values out the error is still occurred but if I dropped the function and trigger, the insert is fine

One more time: ***WHY*** are doing that?


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

David G Johnston
In reply to this post by Pepe TD Vo
On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <[hidden email]> 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.  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.  Looking up that value in Appendix A informs us that it
means: 42P01 undefined_table.  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
>    NEW.mig_seq:=v_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
>    NEW.mig_filename :='Unknown';
> end if;
> exception
> when others then
>    v_ErrorCode := 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.

Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Scott Ribe-2
In reply to this post by Pepe TD Vo
> On Feb 11, 2019, at 12:25 PM, Pepe TD Vo <[hidden email]> wrote:
>
> insert into cidrmgmt.errorlog(...)

For what it's worth, it seems likely the error is coming from that line (assuming you posted the correct procedure definition), thus that's the table whose definition matters. Further, the bad value is likely coming from the context, and there's no info here that would help figure out why there's "42P01" where an integer is required. But I can tell you that PG's nearest equivalent of Oracle's sys_context is untyped, always a string, so there would have to be a cast to an integer type. But of course you can't cast 42P01 to an integer, so that has to be figured out first.


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Scott Ribe-2
In reply to this post by David G Johnston
David's right about the source of the error, ignore my last post


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Pepe TD Vo
In reply to this post by David G Johnston
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 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 without create trigger "tr_stg_adjudicative_status_ins" before insert On cidr_staging.stg_adjudicative_status for each row executive procedure idr_staging.trigger_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.

I have found a lot of scripts transferred from Oracle to Postgres using Ora2pg need 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 source databases.  

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 2:59 PM, David G. Johnston <[hidden email]> wrote:


On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <[hidden email]> 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.  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.  Looking up that value in Appendix A informs us that it
means: 42P01 undefined_table.  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
>    NEW.mig_seq:=v_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
>    NEW.mig_filename :='Unknown';
> end if;
> exception
> when others then
>    v_ErrorCode := 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.




Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Scott Ribe-2
> On Feb 11, 2019, at 2:02 PM, Pepe TD Vo <[hidden email]> wrote:
>
> 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.

Once again, why are you removing it? What do you think happens to octal sequences and doubled backslashes when you do that???


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

David G Johnston
In reply to this post by Pepe TD Vo
On Mon, Feb 11, 2019 at 2:02 PM Pepe TD Vo <[hidden email]> wrote:
> 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 have tried to change it to "int" or "varchar" to test out and still not working.

I'm not sure what your point is here - though changing it to varchar
should have at least gotten rid of the invalid data for type integer
error (though the error message doesn't make sense given that bigint
was the actual type...).  I don't know how intelligent the Ora2pg
program is supposed to be when faced with function bodies.

You still seem way over your head and I'm still generally disinclined
to provide much assistance for continually incomplete and
not-proof-read requests for help.  Lack of any timely responses to
future requests likely means others are feeling the same.

Its great that you want to learn but generally very young children
don't engage in marathons as part of learning to walk.  An Oracle to
PostgreSQL is a marathon.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Pepe TD Vo
In reply to this post by Pepe TD Vo
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 insert
on cidr_staging.stg_Adjudicative_Status
referencing new as new old as old
for each row
declare
   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 <[hidden email]> wrote:


On Mon, Feb 11, 2019 at 2:02 PM Pepe TD Vo <[hidden email]> wrote:
> 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 have tried to change it to "int" or "varchar" to test out and still not working.

I'm not sure what your point is here - though changing it to varchar
should have at least gotten rid of the invalid data for type integer
error (though the error message doesn't make sense given that bigint
was the actual type...).  I don't know how intelligent the Ora2pg
program is supposed to be when faced with function bodies.

You still seem way over your head and I'm still generally disinclined
to provide much assistance for continually incomplete and
not-proof-read requests for help.  Lack of any timely responses to
future requests likely means others are feeling the same.

Its great that you want to learn but generally very young children
don't engage in marathons as part of learning to walk.  An Oracle to
PostgreSQL is a marathon.


David J.


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

David G Johnston
On Tue, Feb 12, 2019 at 9:25 AM Pepe TD Vo <[hidden email]> wrote:
>
> 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.

Its possible my diagnosis was incorrect (or incomplete, is it the
exact same error?) but I don't intend to go back and look at the
original email and dive deeper.  You have too many moving parts at the
moment and you need to simplify the code involved by removing
components until you don't get an error and then add them back one at
a time until you get an error - then figure out why the last added
piece of code is wrong.

David J.

Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Pepe TD Vo
I did and first I changed the data type around as I mention yesterday.  Today I removed the exception to test out.  Nothing wrong with the exception but I find out the problem is "relation "sq_staging" does not exit, but the sequence does exist from information_schema.sequences/ I
dropped and recreated sequence successful but when I do the simple select nextval('sq_staging'); 
ERROR: relation "sq_staging" does not exist.  I dropped all sequences and make sure they existed before rebuild the function and trigger.  The insert is fine now.

Thank you you all.
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 Tuesday, February 12, 2019 11:31 AM, David G. Johnston <[hidden email]> wrote:


On Tue, Feb 12, 2019 at 9:25 AM Pepe TD Vo <[hidden email]> wrote:
>
> 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.

Its possible my diagnosis was incorrect (or incomplete, is it the
exact same error?) but I don't intend to go back and look at the
original email and dive deeper.  You have too many moving parts at the
moment and you need to simplify the code involved by removing
components until you don't get an error and then add them back one at
a time until you get an error - then figure out why the last added
piece of code is wrong.


David J.


Reply | Threaded
Open this post in threaded view
|

Re: insert data with invalid input syntax for integer:"42P01"

Scott Ribe-2
In reply to this post by Pepe TD Vo
> On Feb 12, 2019, at 9:25 AM, Pepe TD Vo <[hidden email]> wrote:
>
> ...in the near future the syntax convert the data insert into Postgres will be without " E' "

???