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 | 2069243125.1915245.1549918933268@mail.yahoo.com обсуждение исходный текст |
| Ответ на | Re: insert data with invalid input syntax for integer:"42P01" ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Ответы |
Re: insert data with invalid input syntax for integer:"42P01"
(Scott Ribe <scott_ribe@elevated-dev.com>)
Re: insert data with invalid input syntax for integer:"42P01" ("David G. Johnston" <david.g.johnston@gmail.com>) |
| Список | pgsql-admin |
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.
В списке pgsql-admin по дате отправления:
Предыдущее
От: Scott RibeДата:
Сообщение: Re: insert data with invalid input syntax for integer:"42P01"
Следующее
От: Scott RibeДата:
Сообщение: Re: insert data with invalid input syntax for integer:"42P01"