Обсуждение: Request for resolution || Support
Dear Team,
<<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit">>
Good evening!
I am migrating Oracle DB into PostgreSQL. However I am facing some issue in postgreSQL as listed below.
Error-1)
ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 486 at SQL statement
SQL state: 0A000
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 486 at SQL statement
SQL state: 0A000
Error-2)
ERROR: schema "utl_http" does not exist
I need urgent help.
Thanks,
Jay Chauhan
Please don’t pick random places to send emails. This is neither a bug nor a security issue so neither of these lists apply. The -general list is most appropriate.
<<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit">>
Use a newer version
Good evening!I am migrating Oracle DB into PostgreSQL. However I am facing some issue in postgreSQL as listed below.Error-1)ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text, text,text,text) line 486 at SQL statement
SQL state: 0A000
Rewrite your code as instructed
Error-2)ERROR: schema "utl_http" does not exist
Insufficient data provided to even guess
I need urgent help.
Maybe hire an experienced migration company then.
David J.
On Mon, Jul 22, 2019 at 8:49 AM David G. Johnston <david.g.johnston@gmail.com> wrote: > On Sunday, July 21, 2019, jay chauhan <jaychauhan.src@gmail.com> wrote: >> <<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat4.8.5-4), 64-bit">> > > Use a newer version Yeah, you should use release 11 for a new project. https://www.postgresql.org/support/versioning/ >> Error-2) >> ERROR: schema "utl_http" does not exist > > Insufficient data provided to even guess That's an Oracle thing for doing HTTP requests from PL/SQL. To do that from plpgsql you could try an extension like this one: https://github.com/pramsey/pgsql-http Or you could write your own function in Python or <insert your favourite PL>. That's what I'd probably do. https://www.postgresql.org/docs/11/plpython-funcs.html Google should help you find examples of people using plpython to access the various Python HTTP modules. -- Thomas Munro https://enterprisedb.com
El dom., 21 de jul. de 2019 3:48 p. m., David G. Johnston <david.g.johnston@gmail.com> escribió:
Please don’t pick random places to send emails. This is neither a bug nor a security issue so neither of these lists apply. The -general list is most appropriate.On Sunday, July 21, 2019, jay chauhan <jaychauhan.src@gmail.com> wrote:<<My PostgreSQL version : "PostgreSQL 9.3.4 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit">>Use a newer versionGood evening!I am migrating Oracle DB into PostgreSQL. However I am facing some issue in postgreSQL as listed below.Error-1)ERROR: cannot begin/end transactions in PL/pgSQL
HINT: Use a BEGIN block with an EXCEPTION clause instead.
CONTEXT: PL/pgSQL function icmsuatnew.eload_commission_payout_active(text,text,text,text,text,text) line 486 at SQL statement
SQL state: 0A000Rewrite your code as instructed
Actually in postgres 11 you can begin/end transactions inside a plpgsql stored procedure (not in a function)
--
Jaime Casanova
2ndQuadrant
Su diligente equipo de expertos en PostgreSQL
2ndQuadrant
Su diligente equipo de expertos en PostgreSQL
On Wed, 24 Jul 2019 at 07:58, jay chauhan <jaychauhan.src@gmail.com> wrote:
Hi Thomas, David/Team,Thanks you for your response. However we need your confirmation whether my Error/issue as mentioned below will be resolved if we upgrade our PostgreSQL Version.
No, the "issues" that you mentioned will not be automatically resolved by upgrading to a modern version of PostgreSQL.
You will need to modify your application to use PostgreSQL facilities properly, such as...
- Not attempting to BEGIN TRANSACTION / COMMIT WORK inside a stored function.
Per the Fine Documentation: https://www.postgresql.org/docs/11/plpgsql-structure.html
"Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in."
- ERROR: schema "utl_http" does not exist
This is a reference to some Oracle-specific thing that you would need to reimplement in some fashion for PostgreSQL. A newer version of PostgreSQL will not somehow draw in Oracle-proprietary features.
If you created a namespace via
create namespace utl_http;
then that particular error message would go away, but it would presumably be replaced by some other error message as whatever it is that is missing would still be missing.
--
When confronted by a difficult problem, solve it by reducing it to the
question, "How would the Lone Ranger handle this?"
question, "How would the Lone Ranger handle this?"