Usage examples of the sqlways_db.ini options

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

[Usage example]: AUTONOMOUS_TRANSACTION for section [Postgre]

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

[Usage example]: COMMENT_TRANSACTION

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

[Usage example]: PACKAGE_VAR_CONVERSION for section [Postgre]

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

[Usage example]: AUTONOMOUS_TRANSACTION for section [PostgresPRO]

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

[Usage example]: PACKAGE_VAR_CONVERSION for section [PostgresPro]

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

Usage examples of the sqlways_db.ini options

[Usage example]: GENERATE_DROP_STATEMENTS_WZD

[Usage example]: DROP_TABLE_CASCADE_CONSTRAINTS

[Usage example]: CONVERT_PACKAGE_TO_SCHEMA

[Usage example]: CONVERT_SP_RETSTATUS_OUTPARAM

[Usage example]: PARAM_PREFIX

[Usage example]: VAR_PREFIX

[Usage example]: SQLWAYS_FRAMEWORK_SCHEMA

[Usage example]: SYNONYM_TO_VIEW

[Usage example]: CONVERT_DATABASE_TO_SCHEMA

[Usage example]: CONVERT_DBLINK_TO_SCHEMA

[Usage example]: SCHEMA_TO_OBJ_NAME

[Usage example]: AUTONOMOUS_TRANSACTION for section [Postgre]

[Usage example]: COMMENT_TRANSACTION

[Usage example]: CASE_INSENS_DATA

[Usage example]: CONV_ALL_PROC_TO_FUNC

[Usage example]: CONVERT_TYPE_TO_SCHEMA

[Usage example]: CONVERT_PACKAGE_TO_SCHEMA

[Usage example]: PACKAGE_VAR_CONVERSION for section [Postgre]

[Usage example]: RETURN_RESULT_FROM_SP_AND_FN

[Usage example]: SECURITY_DEFINER

[Usage example]: AUTONOMOUS_TRANSACTION for section [PostgresPRO]

[Usage example]: PACKAGE_VAR_CONVERSION for section [PostgresPro]

[Usage example]: IDENTITY_TO_SERIAL

[Usage example]: MAP_ERR_CODES

Knowledge Base