About Ispirer Systems
Ispirer Home Page Database Migration Application Conversion Downloads
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (GENERATE_DROP_STATEMENTS_WZD=Yes ) | PostgreSQL (GENERATE_DROP_STATEMENTS_WZD=No) |
|---|---|---|
create table tab ( col1 number(12,0) not null, col2 number(4,0) ) | drop table tab;
create table tab
(
col1 bigint not null,
col2 smallint
);
| create table tab
(
col1 bigint not null,
col2 smallint
);
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (DROP_TABLE_CASCADE_CONSTRAINTS=Yes ) | PostgreSQL (DROP_TABLE_CASCADE_CONSTRAINTS=No) |
|---|---|---|
CREATE TABLE NAMES ( COL0 NUMBER, COL1 NUMBER ) | DROP TABLE IF EXISTS NAMES CASCADE;
CREATE TABLE NAMES
(
COL0 NUMERIC,
COL1 NUMERIC
);
| DROP TABLE IF EXISTS NAMES;
CREATE TABLE NAMES
(
COL0 NUMERIC,
COL1 NUMERIC
);
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (CONVERT_PACKAGE_TO_SCHEMA=Yes) | PostgreSQL (CONVERT_PACKAGE_TO_SCHEMA=No) |
|---|---|---|
create package pkg as
procedure proc1;
function func1 return number;
end pkg_for_rename;
/
create package body pkg as
procedure proc1 is
begin
dbms_output.put_line('proc1');
end proc1;
function func1 return number is
begin
return 1;
end func1;
end pkg;
| create schema if not exists pkg;
create or replace procedure pkg.proc1()
language plpgsql
as $$
begin
raise notice 'proc1';
end; $$;
create or replace function pkg.func1()
returns numeric
language plpgsql
as $$
begin
return 1;
end; $$;
| create or replace procedure pkg_proc1()
language plpgsql
as $$
begin
raise notice 'proc1';
end; $$;
create or replace function pkg_func1()
returns numeric
language plpgsql
as $$
begin
return 1;
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (CONVERT_SP_RETSTATUS_OUTPARAM=Yes) | PostgreSQL (CONVERT_SP_RETSTATUS_OUTPARAM=No) |
|---|---|---|
create procedure sp_status @n int as begin return 1 end; | create procedure sp_status(v_n integer, inout swp_ret_value integer default null) language plpgsql as $$ begin swp_ret_value := 1; return; end; $$; | create procedure sp_status(v_n integer) language plpgsql as $$ begin return; end; $$; |
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (PARAM_PREFIX=v_) | PostgreSQL (PARAM_PREFIX=_) |
|---|---|---|
create procedure [dbo].[sp] @id int, @name varchar(10)
AS
insert into t3 values (@id, @name)
| create or replace procedure sp(v_id integer, v_name varchar)
language plpgsql
as $$
begin
insert into t3 values(v_id, v_name);
end; $$;
| create or replace procedure sp(_id integer, _name varchar)
language plpgsql
as $$
begin
insert into t3 values(_id, _name);
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (VAR_PREFIX=v_) | PostgreSQL (VAR_PREFIX=www_) |
|---|---|---|
create procedure [dbo].[sp] as declare @id int = 1 declare @name varchar(10) = 'name' insert into t3 values (@id, @name) | create or replace procedure sp() language plpgsql as $$ declare v_id integer default 1; v_name varchar(10) default 'name'; begin insert into t3 values(v_id, v_name); end; $$; | сreate or replace procedure sp() language plpgsql as $$ declare www_id integer default 1; www_name varchar(10) default 'name'; begin insert into t3 values(www_id, www_name); end; $$; |
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (SQLWAYS_FRAMEWORK_SCHEMA=sqlways_utils) | PostgreSQL (SQLWAYS_FRAMEWORK_SCHEMA=) |
|---|---|---|
create procedure sp_substr is v_text varchar2(100) := 'Hello World'; v_result varchar2(100); begin v_result := substr(v_text, -3, 2); end; | create procedure sp_substr()
language plpgsql
as $$
declare
v_text varchar(100) default 'Hello World';
v_result varchar(100);
begin
v_result := sqlways_utils.swf_substr(v_text, - 3, 2);
end; $$;
| create procedure sp_substr()
language plpgsql
as $$
declare
v_text varchar(100) default 'Hello World';
v_result varchar(100);
begin
v_result := swf_substr(v_text, - 3, 2);
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (SYNONYM_TO_VIEW=No) | PostgreSQL (SYNONYM_TO_VIEW=Yes) |
|---|---|---|
create table tester.a_t_01 (col1 numeric, col2 numeric); / create public synonym t_01 for tester.a_t_01; / create view v as select col1, col2 from t_01; | create table tester.a_t_01 (col1 bigint, col2 bigint);
/* create synonym "public".t_01 for tester.a_t_01; */
create or replace view v as
select col1 as col1, col2 as col2 from tester.a_t_01;
| create table tester.a_t_01 (col1 bigint, col2 bigint);
create or replace view t_01 as select * from tester.a_t_01;
create or replace view v as
select col1 as col1, col2 as col2 from t_01;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (CONVERT_DATABASE_TO_SCHEMA=No/(Empty)) | PostgreSQL (CONVERT_DATABASE_TO_SCHEMA=Yes) |
|---|---|---|
-- somewhere in MY_DB database
create view dbo.v_tab1 as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from dbo.tab1
| create view dbo.v_tab1 as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from dbo.tab1
| create view MY_DB.v_tab1 as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from MY_DB.tab1
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (CONVERT_DBLINK_TO_SCHEMA=No/(Empty)) | PostgreSQL (CONVERT_DBLINK_TO_SCHEMA=Yes) |
|---|---|---|
create view dbo.v_tab1_dblink as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from dblnk.dbo.tab1
| create view v_tab1_dblink as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from dblink('dbname=dblnk user=user password=user_password','select col1, col2, col3 from dbo.tab1') as(col1 integer,col2 varchar(10),col3 varchar(255))
| create view v_tab1_dblink as
select
col1 as v_col1,
col2 as v_col2,
col3 as v_col3
from dblnk.tab1
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (SCHEMA_TO_OBJ_NAME=No/(Empty)) | PostgreSQL (SCHEMA_TO_OBJ_NAME=Yes) |
|---|---|---|
create view schm.v_tab1 as
select
col1 as v_col1,
col2 as v_col2
from schm.tab1
| create view schm.v_tab1 as
select
col1 as v_col1,
col2 as v_col2
from schm.tab1
| create view schm_v_tab1 as
select
col1 as v_col1,
col2 as v_col2
from schm_tab1
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (AUTONOMOUS_TRANSACTION=comment) | PostgreSQL (AUTONOMOUS_TRANSACTION=DBLINK) |
|---|---|---|
create procedure auto_test (id_value number, text_value varchar2)
is
pragma autonomous_transaction;
begin
insert into autonomous_event (id, value)
values(id_value, text_value);
commit;
end auto_test
| CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR)
LANGUAGE plpgsql
AS $$
/* PRAGMA AUTONOMOUS_TRANSACTION; */
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END; $$;
| CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR, IN is_recursive BOOLEAN DEFAULT FALSE)
LANGUAGE plpgsql
AS $$
DECLARE
v_sql TEXT;
BEGIN
IF is_recursive = FALSE THEN
BEGIN
IF NOT EXISTS(SELECT 1 FROM dblink_get_connections() WHERE dblink_get_connections @> '{myconn}') THEN
PERFORM dblink_connect('myconn', 'SWL__link'); END IF;
v_sql := format('CALL AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE )', id_value, text_value);
PERFORM dblink('myconn', v_sql);
END;
ELSE
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END;
END IF;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (COMMENT_TRANSACTION=Yes) | PostgreSQL (COMMENT_TRANSACTION=No) |
|---|---|---|
create procedure auto_test (id_value number, text_value varchar2)
is
pragma autonomous_transaction;
begin
insert into autonomous_event (id, value)
values(id_value, text_value);
commit;
end auto_test;
| CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR)
LANGUAGE plpgsql
AS $$
/* PRAGMA AUTONOMOUS_TRANSACTION; */
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END; $$;
| CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR, IN is_recursive BOOLEAN DEFAULT FALSE)
LANGUAGE plpgsql
AS $$
DECLARE
v_sql TEXT;
BEGIN
IF is_recursive = FALSE THEN
BEGIN
IF NOT EXISTS(SELECT 1 FROM dblink_get_connections() WHERE dblink_get_connections @> '{myconn}') THEN
PERFORM dblink_connect('myconn', 'SWL__link'); END IF;
v_sql := format('CALL AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE )', id_value, text_value);
PERFORM dblink('myconn', v_sql);
END;
ELSE
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END;
END IF;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting procedure in the DEFAULT, COLLATION and LOWER cases. Please compare:
| Type / Option Value | Code sample |
|---|---|
| MSSQL source | create table tb_collation ( first_name varchar(64), last_name varchar(64) ); create procedure pr_collation @title varchar(20) = 'ispirer' as BEGIN declare @title2 varchar(20) = 'ISPIRER' IF (@title = @title2) begin print 'equal' END IF (@title like @title2 ) begin print 'like' END END |
| PostgreSQL CASE_INSENS_DATA=Default | create table tb_collation ( first_name VARCHAR(64), last_name VARCHAR(64) ); create or replace PROCEDURE pr_collation (v_title VARCHAR DEFAULT 'ispirer')
LANGUAGE plpgsql
AS $$
DECLARE
v_title2 VARCHAR(20) DEFAULT 'ISPIRER';
BEGIN
IF (v_title = v_title2) then
RAISE NOTICE 'equal';
end if;
IF (v_title ilike v_title2) then
RAISE NOTICE 'like';
end if;
END; $$;
|
| PostgreSQL CASE_INSENS_DATA=Collation | CREATE COLLATION IF NOT EXISTS swcol_ci_nondet (provider = icu, locale = 'und-u-ks-level2', deterministic = false); CREATE COLLATION IF NOT EXISTS swcol_ci_det (provider = icu, locale = 'und-u-ks-level2', deterministic = true); create table tb_collation ( first_name VARCHAR(64) COLLATE swcol_ci_nondet, last_name VARCHAR(64) COLLATE swcol_ci_nondet ); create or replace PROCEDURE pr_collation(v_title VARCHAR DEFAULT 'ispirer')
LANGUAGE plpgsql
AS $$
DECLARE
v_title2 VARCHAR(20) COLLATE swcol_ci_nondet DEFAULT 'ISPIRER';
BEGIN
IF (v_title COLLATE swcol_ci_nondet = v_title2) then
RAISE NOTICE 'equal';
end if;
IF (v_title ilike v_title2 COLLATE swcol_ci_det) then
RAISE NOTICE 'like';
end if;
END; $$;
|
| PostgreSQL CASE_INSENS_DATA=Lower | create table tb_collation ( first_name VARCHAR(64), last_name VARCHAR(64) ); create or replace PROCEDURE pr_collation(v_title VARCHAR DEFAULT 'ispirer')
LANGUAGE plpgsql
AS $$
DECLARE
v_title2 VARCHAR(20) DEFAULT 'ISPIRER';
BEGIN
IF (LOWER(v_title) = LOWER(v_title2)) then
RAISE NOTICE 'equal';
end if;
IF (v_title ilike v_title2) then
RAISE NOTICE 'like';
end if;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (MSSQL) | PostgreSQL (Default) (CONV_ALL_PROC_TO_FUNC=No) | PostgreSQL (CONV_ALL_PROC_TO_FUNC=Yes) |
|---|---|---|
create procedure [dbo].[sp_tab_insert] @id int, @name varchar(10) as insert into t3 values (@id, @name) | create or replace procedure sp_tab_insert(v_id integer, v_name varchar)
language plpgsql
as $$
begin
insert into t3 values(v_id, v_name);
end; $$;
| create or replace function sp_tab_insert(v_id integer, v_name varchar)
returns void
language plpgsql
as $$
begin
insert into t3 values(v_id, v_name);
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (CONVERT_TYPE_TO_SCHEMA=No) | PostgreSQL (CONVERT_TYPE_TO_SCHEMA=Yes) |
|---|---|---|
create type ora.t_param as object
(
name varchar2(30),
type varchar2(30),
constructor function t_param return self as result,
member function as_text return varchar2
)
/
type body ora.t_param
is
constructor function t_param return self as result is
begin
self.name := 'type name';
self.type := 'text';
return;
end;
member function as_text return varchar2 is
begin
if self.type = 'text' then
return (self.name);
end if;
return '-';
end;
end;
| create type ora.t_param as
(
name varchar(30),
type varchar(30)
);
create or replace function ora.t_param()
returns ora.t_param
language plpgsql
as $$
declare
self ora.t_param;
begin
self.name := 'type name';
self.type := 'text';
return self;
end; $$;
create or replace function ora.t_param_as_text(in self ora.t_param)
returns varchar
language plpgsql
as $$
begin
if self.type = 'text' then
return (self.name);
end if;
return '-';
end; $$;
| create schema if not exists t_param;
create type t_param.t_param as
(
name varchar(30),
type varchar(30)
);
create or replace function t_param.t_param()
returns t_param.t_param
language plpgsql
as $$
declare
self t_param.t_param;
begin
self.name := 'type name';
self.type := 'text';
return self;
end; $$;
create or replace function t_param.as_text(in self t_param.t_param)
returns varchar
language plpgsql
as $$
begin
if self.type = 'text' then
return (self.name);
end if;
return '-';
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (CONVERT_PACKAGE_TO_SCHEMA=Yes) | PostgreSQL (CONVERT_PACKAGE_TO_SCHEMA=No) |
|---|---|---|
select
cust.customer_id as c_id,
cust.customer_name as c_name,
cust.city as c_city,
cursor
(
select
ord.order_id as o_id,
ord.order_date as o_date,
cust.customer_name as ext_c_name
from orders ord
where ord.customer_id = cust.customer_id
) as orders_cur
from customers cust;
| select
cust.customer_id as c_id,
cust.customer_name as c_name,
cust.city as c_city,
swf_ret_cur('select
ord.order_id as o_id,
ord.order_date as o_date,
cust.customer_name as ext_c_name
from orders ord
where ord.customer_id = cust.customer_id') as orders_cur
from customers cust;
| select
cust.customer_id as c_id,
cust.customer_name as c_name,
cust.city as c_city,
'orders_cur', coalesce((
select
jsonb_agg(jsonb_build_object(
'o_id', ord.order_id,
'o_date', ord.order_date,
'ext_c_name', cust.customer_name))
from orders ord
where ord.customer_id = cust.customer_id), '{}' :: jsonb)
from customers cust;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The purpose of this article is to demonstrate how the PACKAGE_VAR_CONVERSION option affects conversion results. The example below shows the difference in the resulting procedure in all cases. Please compare:
| Type / Option Value | Code sample |
|---|---|
| Oracle source | create or replace package test_pkg1 is
g_n1 number := 15;
procedure proc1;
end;
/
create or replace package body test_pkg1 is
g_n2 constant number := 2026;
g_s varchar2(65) := 'тест';
procedure print_global_vars is
begin
dbms_output.put_line('g_n1 = '||g_n1);
dbms_output.put_line('g_n2 = '||g_n2);
dbms_output.put_line('g_s = ' ||g_s);
end;
procedure proc1 is
v_v varchar2(65) := 'new value';
begin
g_s := v_v;
g_n1 := 1000;
end;
begin
dbms_output.put_line('body initialization block');
g_s := 'тест1';
end;
|
| PostgreSQL (Default) (PACKAGE_VAR_CONVERSION=temp_table) | create schema if not exists test_pkg1;
drop type if exists test_pkg1.gl_var_type cascade;
create type test_pkg1.gl_var_type
as
(
g_n1 numeric,
g_n2 numeric,
g_s varchar(65)
);
create or replace function test_pkg1.init_gl_var()
returns void
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type;
begin
create temporary table test_pkg1_gl_var as select row(15, 2026, 'тест') :: test_pkg1.gl_var_type as swv_gl_var_val;
-- begin initialization block
raise notice 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
perform test_pkg1.set_gl_var(swv_gl_var);
-- end initialization block
return;
exception when sqlstate '42P07' then
null;
end; $$;
create or replace function test_pkg1.get_gl_var()
returns test_pkg1.gl_var_type
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type;
begin
return(select swv_gl_var_val :: test_pkg1.gl_var_type from test_pkg1_gl_var);
exception when others then
perform test_pkg1.init_gl_var();
return(select swv_gl_var_val :: test_pkg1.gl_var_type from test_pkg1_gl_var);
end; $$;
create or replace function test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
returns void
language plpgsql
as $$
begin
update test_pkg1_gl_var set swv_gl_var_val = swp_glvar;
end; $$;
create or replace procedure test_pkg1.print_global_vars()
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type default test_pkg1.get_gl_var();
begin
raise notice '%', concat('g_n1 = ', swv_gl_var.g_n1);
raise notice '%', concat('g_n2 = ', swv_gl_var.g_n2);
raise notice '%', concat('g_s = ', swv_gl_var.g_s);
end; $$;
create or replace procedure test_pkg1.proc1()
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type default test_pkg1.get_gl_var();
v_v varchar(65) default 'new value';
begin
call test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
perform test_pkg1.set_gl_var(swv_gl_var);
call test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
end; $$;
|
| PostgreSQL (PACKAGE_VAR_CONVERSION=pg_variables) | CREATE SCHEMA IF NOT EXISTS test_pkg1;
DROP TYPE IF EXISTS test_pkg1.gl_var_type CASCADE;
CREATE TYPE test_pkg1.gl_var_type
AS
(
g_n1 NUMERIC,
g_n2 NUMERIC,
g_s VARCHAR(65)
);
CREATE OR REPLACE FUNCTION test_pkg1.get_gl_var()
RETURNS test_pkg1.gl_var_type
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE;
BEGIN
RETURN pgv_get('test_pkg1', 'gl_var_type', NULL :: TEST_PKG1.GL_VAR_TYPE);
EXCEPTION WHEN sqlstate '22023' THEN
IF pgv_exists('test_pkg1', 'gl_var_type') THEN
PERFORM pgv_remove('test_pkg1', 'gl_var_type');
END IF;
swv_gl_var.g_n1 := 15;
swv_gl_var.g_n2 := 2026;
swv_gl_var.g_s := 'тест';
PERFORM pgv_set('test_pkg1', 'gl_var_type', swv_gl_var);
-- begin Initialization Block
RAISE NOTICE 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
PERFORM test_pkg1.set_gl_var(swv_gl_var);
-- end Initialization Block
RETURN pgv_get('test_pkg1', 'gl_var_type', NULL :: TEST_PKG1.GL_VAR_TYPE);
END; $$;
CREATE OR REPLACE FUNCTION test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM pgv_set('test_pkg1', 'gl_var_type', swp_glvar);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.print_global_vars()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
BEGIN
RAISE NOTICE '%', CONCAT('g_n1 = ', swv_gl_var.g_n1);
RAISE NOTICE '%', CONCAT('g_n2 = ', swv_gl_var.g_n2);
RAISE NOTICE '%', CONCAT('g_s = ', swv_gl_var.g_s);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.proc1()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
v_v VARCHAR(65) DEFAULT 'new value';
BEGIN
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
PERFORM test_pkg1.set_gl_var(swv_gl_var);
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
END; $$;
|
| PostgreSQL (PACKAGE_VAR_CONVERSION=session_config_params) | CREATE SCHEMA IF NOT EXISTS test_pkg1;
DROP TYPE IF EXISTS test_pkg1.gl_var_type CASCADE;
CREATE TYPE test_pkg1.gl_var_type
AS
(
g_n1 NUMERIC,
g_n2 NUMERIC,
g_s VARCHAR(65)
);
CREATE OR REPLACE FUNCTION test_pkg1.get_gl_var()
RETURNS test_pkg1.gl_var_type
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE;
BEGIN
RETURN current_setting('sqlways.TEST_PKG1_GL_VAR') :: TEST_PKG1.GL_VAR_TYPE;
EXCEPTION WHEN SQLSTATE '42704' THEN
SET sqlways.test_pkg1_gl_var = DEFAULT;
PERFORM set_config('sqlways.TEST_PKG1_GL_VAR',(15, 2026, 'тест') :: TEST_PKG1.GL_VAR_TYPE :: TEXT, FALSE);
-- begin Initialization Block
RAISE NOTICE 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
PERFORM test_pkg1.set_gl_var(swv_gl_var);
-- end Initialization Block
RETURN current_setting('sqlways.TEST_PKG1_GL_VAR') :: TEST_PKG1.GL_VAR_TYPE;
END; $$;
CREATE OR REPLACE FUNCTION test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM set_config('sqlways.TEST_PKG1_GL_VAR', swp_glvar :: TEXT, FALSE);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.print_global_vars()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
BEGIN
RAISE NOTICE '%', CONCAT('g_n1 = ', swv_gl_var.g_n1);
RAISE NOTICE '%', CONCAT('g_n2 = ', swv_gl_var.g_n2);
RAISE NOTICE '%', CONCAT('g_s = ', swv_gl_var.g_s);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.proc1()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
v_v VARCHAR(65) DEFAULT 'new value';
BEGIN
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
PERFORM test_pkg1.set_gl_var(swv_gl_var);
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The purpose of this article is to demonstrate how the RETURN_RESULT_FROM_SP_AND_FN option affects conversion results. The examples below show the difference in the resulting procedure in all cases. Please compare:
| Type / Option Value | Code sample |
|---|---|
| MSSQL source | create table test_data(c1 int, c2 varchar(22)) create procedure result_set_pr @p1 Date as select @p1 as c0, c1, c2 from test_data |
| PostgreSQL (Default) (RETURN_RESULT_FROM_SP_AND_FN=TABLE) | CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
RETURNS table
(
c0 DATE,
c1 INTEGER,
c2 VARCHAR(22)
) LANGUAGE plpgsql
AS $$
BEGIN
return query select v_p1 as c0, c1, c2 from test_data;
END; $$;
|
| PostgreSQL (RETURN_RESULT_FROM_SP_AND_FN=SETOF) | CREATE TYPE result_set_pr_rs AS(c0 DATE, c1 INTEGER, c2 VARCHAR(22));
CREATE OR REPLACE FUNCTION result_set_pr(v_p1 DATE)
RETURNS SETOF result_set_pr_rs LANGUAGE plpgsql
AS $$
BEGIN
return query select v_p1 as c0, c1, c2 from test_data;
END; $$;
|
| PostgreSQL (RETURN_RESULT_FROM_SP_AND_FN= REFCURSOR) | CREATE OR REPLACE PROCEDURE result_set_pr(v_p1 DATE, INOUT SWV_RefCur refcursor)
LANGUAGE plpgsql
AS $$
BEGIN
open SWV_RefCur for
select v_p1 as c0, c1, c2 from test_data;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The example below shows the difference in the resulting function in both cases. Please compare and try:
| Source code (Oracle) | PostgreSQL (Default) (SECURITY_DEFINER=No) | PostgreSQL (SECURITY_DEFINER=Yes) |
|---|---|---|
create procedure pr_test (p1 int) is
begin
insert into tab4 (col1, col2)
select p1, sysdate from dual;
end;
| create or replace procedure pr_test(p1 integer)
language plpgsql
as $$
begin
insert into tab4(col1, col2)
select p1, clock_timestamp() :: timestamp(0);
end; $$;
| create or replace procedure pr_test(p1 integer)
language plpgsql security definer
as $$
begin
insert into tab4(col1, col2)
select p1, clock_timestamp() :: timestamp(0);
end; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The purpose of this article is to demonstrate how the AUTONOMOUS_TRANSACTION option affects conversion results. The examples below show the difference in the resulting procedure in all cases. Please compare:
| Type / Option Value | Code sample |
|---|---|
| Oracle source | create procedure auto_test (id_value number, text_value varchar2)
is
pragma autonomous_transaction;
begin
insert into autonomous_event (id, value)
values(id_value, text_value);
commit;
end auto_test;
|
| PostgresPro (Default) AUTONOMOUS_TRANSACTION=begin_autonomous | create or replace procedure auto_test(id_value numeric, text_value varchar)
language plpgsql
as $$
begin autonomous
insert into autonomous_event(id, value)
values(id_value, text_value);
end; $$;
|
| PostgresPro AUTONOMOUS_TRANSACTION=comment | CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR)
LANGUAGE plpgsql
AS $$
/* PRAGMA AUTONOMOUS_TRANSACTION; */
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END; $$;
|
| PostgresPro AUTONOMOUS_TRANSACTION=DBLINK | CREATE OR REPLACE PROCEDURE auto_test(id_value NUMERIC, text_value VARCHAR, IN is_recursive BOOLEAN DEFAULT FALSE)
LANGUAGE plpgsql
AS $$
DECLARE
v_sql TEXT;
BEGIN
IF is_recursive = FALSE THEN
BEGIN
IF NOT EXISTS(SELECT 1 FROM dblink_get_connections() WHERE dblink_get_connections @> '{myconn}') THEN
PERFORM dblink_connect('myconn', 'SWL__link'); END IF;
v_sql := format('CALL AUTO_TEST( id_value => %L, text_value => %L, is_recursive => TRUE )', id_value, text_value);
PERFORM dblink('myconn', v_sql);
END;
ELSE
BEGIN
INSERT INTO autonomous_event(id, value)
VALUES(id_value, text_value);
/* COMMIT; */
END;
END IF;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
The purpose of this article is to demonstrate how the PACKAGE_VAR_CONVERSION option affects conversion results. The example below shows the difference in the resulting procedure in all cases. Please compare:
| Type / Option Value | Code sample |
|---|---|
| Oracle source | create or replace package test_pkg1 is
g_n1 number := 15;
procedure proc1;
end;
/
create or replace package body test_pkg1 is
g_n2 constant number := 2026;
g_s varchar2(65) := 'тест';
procedure print_global_vars is
begin
dbms_output.put_line('g_n1 = '||g_n1);
dbms_output.put_line('g_n2 = '||g_n2);
dbms_output.put_line('g_s = ' ||g_s);
end;
procedure proc1 is
v_v varchar2(65) := 'new value';
begin
g_s := v_v;
g_n1 := 1000;
end;
begin
dbms_output.put_line('body initialization block');
g_s := 'тест1';
end;
|
| PostgresPro (Default) (PACKAGE_VAR_CONVERSION=package) | CREATE SCHEMA IF NOT EXISTS test_pkg1;
CREATE OR REPLACE FUNCTION test_pkg1.__init__()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
g_n1 NUMERIC DEFAULT 15;
g_n2 CONSTANT NUMERIC DEFAULT 2026;
g_s VARCHAR(65) DEFAULT 'тест';
BEGIN
RAISE NOTICE 'body initialization block';
g_s := 'тест1';
RETURN;
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.print_global_vars()
LANGUAGE plpgsql
AS $$
#package
BEGIN
RAISE NOTICE '%', CONCAT('g_n1 = ', g_n1);
RAISE NOTICE '%', CONCAT('g_n2 = ', g_n2);
RAISE NOTICE '%', CONCAT('g_s = ', g_s);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.proc1()
LANGUAGE plpgsql
AS $$
#package
DECLARE
v_v VARCHAR(65) DEFAULT 'new value';
BEGIN
CALL test_pkg1.print_global_vars();
g_s := v_v;
g_n1 := 1000;
CALL test_pkg1.print_global_vars();
END; $$;
|
| PostgresPro (PACKAGE_VAR_CONVERSION=temp_table) | create schema if not exists test_pkg1;
drop type if exists test_pkg1.gl_var_type cascade;
create type test_pkg1.gl_var_type
as
(
g_n1 numeric,
g_n2 numeric,
g_s varchar(65)
);
create or replace function test_pkg1.init_gl_var()
returns void
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type;
begin
create temporary table test_pkg1_gl_var as select row(15, 2026, 'тест') :: test_pkg1.gl_var_type as swv_gl_var_val;
-- begin initialization block
raise notice 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
perform test_pkg1.set_gl_var(swv_gl_var);
-- end initialization block
return;
exception when sqlstate '42P07' then
null;
end; $$;
create or replace function test_pkg1.get_gl_var()
returns test_pkg1.gl_var_type
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type;
begin
return(select swv_gl_var_val :: test_pkg1.gl_var_type from test_pkg1_gl_var);
exception when others then
perform test_pkg1.init_gl_var();
return(select swv_gl_var_val :: test_pkg1.gl_var_type from test_pkg1_gl_var);
end; $$;
create or replace function test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
returns void
language plpgsql
as $$
begin
update test_pkg1_gl_var set swv_gl_var_val = swp_glvar;
end; $$;
create or replace procedure test_pkg1.print_global_vars()
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type default test_pkg1.get_gl_var();
begin
raise notice '%', concat('g_n1 = ', swv_gl_var.g_n1);
raise notice '%', concat('g_n2 = ', swv_gl_var.g_n2);
raise notice '%', concat('g_s = ', swv_gl_var.g_s);
end; $$;
create or replace procedure test_pkg1.proc1()
language plpgsql
as $$
declare
swv_gl_var test_pkg1.gl_var_type default test_pkg1.get_gl_var();
v_v varchar(65) default 'new value';
begin
call test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
perform test_pkg1.set_gl_var(swv_gl_var);
call test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
end; $$;
|
| PostgresPro (PACKAGE_VAR_CONVERSION=pg_variables) | CREATE SCHEMA IF NOT EXISTS test_pkg1;
DROP TYPE IF EXISTS test_pkg1.gl_var_type CASCADE;
CREATE TYPE test_pkg1.gl_var_type
AS
(
g_n1 NUMERIC,
g_n2 NUMERIC,
g_s VARCHAR(65)
);
CREATE OR REPLACE FUNCTION test_pkg1.get_gl_var()
RETURNS test_pkg1.gl_var_type
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE;
BEGIN
RETURN pgv_get('test_pkg1', 'gl_var_type', NULL :: TEST_PKG1.GL_VAR_TYPE);
EXCEPTION WHEN sqlstate '22023' THEN
IF pgv_exists('test_pkg1', 'gl_var_type') THEN
PERFORM pgv_remove('test_pkg1', 'gl_var_type');
END IF;
swv_gl_var.g_n1 := 15;
swv_gl_var.g_n2 := 2026;
swv_gl_var.g_s := 'тест';
PERFORM pgv_set('test_pkg1', 'gl_var_type', swv_gl_var);
-- begin Initialization Block
RAISE NOTICE 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
PERFORM test_pkg1.set_gl_var(swv_gl_var);
-- end Initialization Block
RETURN pgv_get('test_pkg1', 'gl_var_type', NULL :: TEST_PKG1.GL_VAR_TYPE);
END; $$;
CREATE OR REPLACE FUNCTION test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM pgv_set('test_pkg1', 'gl_var_type', swp_glvar);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.print_global_vars()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
BEGIN
RAISE NOTICE '%', CONCAT('g_n1 = ', swv_gl_var.g_n1);
RAISE NOTICE '%', CONCAT('g_n2 = ', swv_gl_var.g_n2);
RAISE NOTICE '%', CONCAT('g_s = ', swv_gl_var.g_s);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.proc1()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
v_v VARCHAR(65) DEFAULT 'new value';
BEGIN
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
PERFORM test_pkg1.set_gl_var(swv_gl_var);
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
END; $$;
|
| PostgresPro (PACKAGE_VAR_CONVERSION=session_config_params) | CREATE SCHEMA IF NOT EXISTS test_pkg1;
DROP TYPE IF EXISTS test_pkg1.gl_var_type CASCADE;
CREATE TYPE test_pkg1.gl_var_type
AS
(
g_n1 NUMERIC,
g_n2 NUMERIC,
g_s VARCHAR(65)
);
CREATE OR REPLACE FUNCTION test_pkg1.get_gl_var()
RETURNS test_pkg1.gl_var_type
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE;
BEGIN
RETURN current_setting('sqlways.TEST_PKG1_GL_VAR') :: TEST_PKG1.GL_VAR_TYPE;
EXCEPTION WHEN SQLSTATE '42704' THEN
SET sqlways.test_pkg1_gl_var = DEFAULT;
PERFORM set_config('sqlways.TEST_PKG1_GL_VAR',(15, 2026, 'тест') :: TEST_PKG1.GL_VAR_TYPE :: TEXT, FALSE);
-- begin Initialization Block
RAISE NOTICE 'body initialization block';
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := 'тест1';
PERFORM test_pkg1.set_gl_var(swv_gl_var);
-- end Initialization Block
RETURN current_setting('sqlways.TEST_PKG1_GL_VAR') :: TEST_PKG1.GL_VAR_TYPE;
END; $$;
CREATE OR REPLACE FUNCTION test_pkg1.set_gl_var(swp_glvar test_pkg1.gl_var_type)
RETURNS VOID
LANGUAGE plpgsql
AS $$
BEGIN
PERFORM set_config('sqlways.TEST_PKG1_GL_VAR', swp_glvar :: TEXT, FALSE);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.print_global_vars()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
BEGIN
RAISE NOTICE '%', CONCAT('g_n1 = ', swv_gl_var.g_n1);
RAISE NOTICE '%', CONCAT('g_n2 = ', swv_gl_var.g_n2);
RAISE NOTICE '%', CONCAT('g_s = ', swv_gl_var.g_s);
END; $$;
CREATE OR REPLACE PROCEDURE test_pkg1.proc1()
LANGUAGE plpgsql
AS $$
DECLARE
swv_gl_var TEST_PKG1.GL_VAR_TYPE DEFAULT TEST_PKG1.GET_GL_VAR();
v_v VARCHAR(65) DEFAULT 'new value';
BEGIN
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
swv_gl_var.g_s := v_v;
swv_gl_var.g_n1 := 1000;
PERFORM test_pkg1.set_gl_var(swv_gl_var);
CALL test_pkg1.print_global_vars();
swv_gl_var := test_pkg1.get_gl_var();
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
PostgreSQL allows to use 2 ways of automatic integer numbers generation: by using the IDENTITY property or using the SERIAL pseudo-data type. The result of using the option with values No and Yes displayed in the table below. The data type of the source IDENTITY column will be converted into appropriate SERIAL column: SMALLINT will become SMALLSERIAL, INTEGER goes into SERIAL, and BIGINT into BIGSERIAL accordingly.
| Source code (DB2 LUW) | PostgreSQL (Default) (IDENTITY_TO_SERIAL=No) | PostgreSQL (IDENTITY_TO_SERIAL=Yes) |
|---|---|---|
CREATE TABLE TABIDENTCOLUMN ( ID INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1) NOT NULL, NAME CHAR(5) ); CREATE TABLE TABIDENTCOLUMN_2 ( ID SMALLINT GENERATED ALWAYS AS IDENTITY (START WITH 3, INCREMENT BY 1) NOT NULL, NAME CHAR(5) ) | CREATE TABLE TABIDENTCOLUMN
(
ID INTEGER GENERATED ALWAYS AS IDENTITY(START 1 INCREMENT 1) NOT NULL,
NAME CHAR(5)
);
CREATE TABLE TABIDENTCOLUMN_2
(
ID SMALLINT GENERATED ALWAYS AS IDENTITY(START 3 INCREMENT 1) NOT NULL,
NAME CHAR(5)
);
| CREATE TABLE TABIDENTCOLUMN
(
ID SERIAL,
NAME CHAR(5)
);
CREATE TABLE TABIDENTCOLUMN_2
(
ID SMALLSERIAL,
NAME CHAR(5)
);
ALTER SEQUENCE TABIDENTCOLUMN_2_ID_seq RESTART WITH 3 INCREMENT BY 1;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com
Errors with the -20 prefix are converted to SQLSTATE using the U0 prefix, and RAISE_APPLICATION_ERROR or a named exception raise is replaced with RAISE EXCEPTION … USING ERRCODE. The corresponding exception handlers use the converted code (SQLSTATE 'error_code'). The PRAGMA EXCEPTION_INIT declaration and the named exception itself are removed, and all references to the exception are replaced with its SQLSTATE code.
Errors for which no match is found (including external package and system errors without matching) are converted to a single format: RAISE EXCEPTION 'error_name'. In the EXCEPTION block, the handler is replaced with WHEN SQLSTATE 'error_name' THEN and requires manual editing
| Source code (Oracle) | PostgreSQL (Default) (MAP_ERR_CODES=No) | PostgreSQL (MAP_ERR_CODES=Yes) |
|---|---|---|
CREATE OR REPLACE PROCEDURE proc_user_error (p_variant IN NUMBER) IS
e_my_named_error EXCEPTION;
PRAGMA EXCEPTION_INIT(e_my_named_error, -20002);
BEGIN
IF p_variant = 1 THEN
RAISE_APPLICATION_ERROR(-20001, 'Variant 1: Basic custom error.');
END IF;
IF p_variant = 2 THEN
RAISE_APPLICATION_ERROR(-20002, 'Variant 2: My named error message.');
END IF;
EXCEPTION
WHEN e_my_named_error THEN
DBMS_OUTPUT.PUT_LINE('Caught by NAME: ' || SQLERRM);
RAISE;
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Caught OTHERS: ' || SQLERRM);
RAISE;
END proc_user_error;
| CREATE OR REPLACE PROCEDURE proc_user_error(IN p_variant NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
swv_exception_detail TEXT;
BEGIN
IF p_variant = 1 THEN
RAISE EXCEPTION '%', 'Variant 1: Basic custom error.' USING DETAIL = '-20001';
END IF;
IF p_variant = 2 THEN
RAISE EXCEPTION '%', 'Variant 2: My named error message.' USING DETAIL = '-20002';
END IF;
EXCEPTION
WHEN SQLSTATE 'P0001' THEN
GET STACKED DIAGNOSTICS swv_exception_detail = pg_exception_detail;
IF(sqlerrm = 'e_my_named_error' OR swv_exception_detail = '-20002') THEN
RAISE NOTICE '%', CONCAT('Caught by NAME: ', SQLERRM);
RAISE;
ELSE
RAISE NOTICE '%', CONCAT('Caught OTHERS: ', SQLERRM);
RAISE;
END IF;
WHEN OTHERS THEN
RAISE NOTICE '%', CONCAT('Caught OTHERS: ', SQLERRM);
RAISE;
END; $$;
| CREATE OR REPLACE PROCEDURE proc_user_error(IN p_variant NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
IF p_variant = 1 THEN
RAISE EXCEPTION 'Variant 1: Basic custom error.' USING ERRCODE = 'U0001';
END IF;
IF p_variant = 2 THEN
RAISE EXCEPTION 'Variant 2: My named error message.' USING ERRCODE = 'U0002';
END IF;
EXCEPTION
WHEN SQLSTATE 'U0002' THEN
RAISE NOTICE '%', CONCAT('Caught by NAME: ', SQLERRM);
RAISE;
WHEN OTHERS THEN
RAISE NOTICE '%', CONCAT('Caught OTHERS: ', SQLERRM);
RAISE;
END; $$;
|
If this option does not work as expected, please contact our technical team: support@ispirer.com