Re: insert data with invalid input syntax for integer:"42P01"
| От | Pepe TD Vo |
|---|---|
| Тема | Re: insert data with invalid input syntax for integer:"42P01" |
| Дата | |
| Msg-id | 1990155251.2422933.1549988748913@mail.yahoo.com обсуждение исходный текст |
| Ответ на | insert data with invalid input syntax for integer:"42P01" (Pepe TD Vo <pepevo@yahoo.com>) |
| Ответы |
Re: insert data with invalid input syntax for integer:"42P01"
Re: insert data with invalid input syntax for integer:"42P01" |
| Список | pgsql-admin |
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.
В списке pgsql-admin по дате отправления:
Следующее
От: "David G. Johnston"Дата:
Сообщение: Re: insert data with invalid input syntax for integer:"42P01"