Обсуждение: insert data with invalid input syntax for integer:"42P01"
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.
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
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, orhave 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?
On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <pepevo@yahoo.com> 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.
> On Feb 11, 2019, at 12:25 PM, Pepe TD Vo <pepevo@yahoo.com> 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, andthere's no info here that would help figure out why there's "42P01" where an integer is required. But I can tell you thatPG's nearest equivalent of Oracle's sys_context is untyped, always a string, so there would have to be a cast to an integertype. But of course you can't cast 42P01 to an integer, so that has to be figured out first.
David's right about the source of the error, ignore my last post
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.
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 <david.g.johnston@gmail.com> wrote:
On Mon, Feb 11, 2019 at 12:25 PM Pepe TD Vo <pepevo@yahoo.com> 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.
> 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.
> On Feb 11, 2019, at 2:02 PM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > for the 'E, I did learn it encode binary data into a textual representation and converts zero bytes and high bit set bytesto 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???
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. From Oracle, the trigger and procedure were declaredas 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.
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.
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. 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.
> 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.
On Tue, Feb 12, 2019 at 9:25 AM Pepe TD Vo <pepevo@yahoo.com> wrote: > > I just test out to remove " E' " to see it work because in the near future the syntax convert the data insert into Postgreswill be without " E' " > > I gave exact code converted from Oracle via Ora2pg without changing any. I just worked around to change the data typeto see it work unfortunately, it didn't work :( . The insert is working with function but as soon as I put the triggerin, 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.
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.
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 <david.g.johnston@gmail.com> wrote:
On Tue, Feb 12, 2019 at 9:25 AM Pepe TD Vo <pepevo@yahoo.com> 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.
>
> 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.
> On Feb 12, 2019, at 9:25 AM, Pepe TD Vo <pepevo@yahoo.com> wrote: > > ...in the near future the syntax convert the data insert into Postgres will be without " E' " ???