Migrate Oracle to PostgreSQL

 
	CREATE TYPE employee AS OBJECT (
		id NUMBER,
		Name VARCHAR(300)
		);
	CREATE TYPE employees_tab IS TABLE OF employee;
 
	CREATE OR REPLACE PROCEDURE hire(EMPLOYEES IN OUT employees_tab, id NUMBER, Name VARCHAR) AS
	NEW_EMPLOYEES employees_tab := employees_tab();
	BEGIN
		EMPLOYEES.Extend(1);
		EMPLOYEES(EMPLOYEES.COUNT) := employee(id, Name);
		FOR i IN EMPLOYEES.FIRST..EMPLOYEES.LAST
		LOOP
			INSERT INTO emp_tab VALUES (EMPLOYEES(i).id, EMPLOYEES(i).Name);
		END LOOP;
		INSERT INTO EMP_TAB SELECT * FROM TABLE(EMPLOYEES);
		NEW_EMPLOYEES.Extend(EMPLOYEES.COUNT);
		NEW_EMPLOYEES.DELETE;
	END;  			 
 

 
  CREATE TYPE employee AS(id NUMERIC,Name VARCHAR(300));
	-- CREATE TYPE employees_tab IS TABLE OF employee;
 
	CREATE OR REPLACE PROCEDURE hire(INOUT EMPLOYEES employee[] , id NUMERIC, Name VARCHAR)
	LANGUAGE plpgsql
		AS $$
		DECLARE
		NEW_EMPLOYEES  employee[] default array[]:: employee[];
	BEGIN
		EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)+1] := null;
		EMPLOYEES[coalesce(array_length(EMPLOYEES,1),0)] := row(id,Name);
		FOR i IN array_lower(EMPLOYEES,1) .. array_upper(EMPLOYEES,1)
		LOOP
			INSERT INTO emp_tab  values(EMPLOYEES[i].id, EMPLOYEES[i].Name);
		END LOOP;
		INSERT INTO EMP_TAB  SELECT * FROM UNNEST(EMPLOYEES);
		for i in 1 .. coalesce(array_length(EMPLOYEES,1),0) loop
			NEW_EMPLOYEES[coalesce(array_length(NEW_EMPLOYEES,1),0)+1] := null;
		end loop;
		NEW_EMPLOYEES := array[]:: employee[];
	END; $$;
 

 
  	CREATE OR REPLACE EDITIONABLE PACKAGE BODY "COMMON_PKG" AS 
 
	CUSTOMER_ID NUMBER(10,0);
	TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
	ADDRES_LST VARCHAR2_AARAY;
 
	PROCEDURE PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR2, CUSTTYPE_ID VARCHAR2)
	IS
	CUR_ADDR VARCHAR2(500);
	CUSTM_ID NUMBER;
		v_int NUMBER; 
		V_RES  DBMS_UTILITY.lname_array; 
	l_clob clob;
	CURSOR cus_addr(cur_id NUMBER) IS SELECT address FROM customer WHERE cust_id =  cur_id OR cust_id <5;  
	BEGIN
	l_clob := '';
		SELECT CUST_ID INTO CUSTM_ID 
			FROM customer 
			WHERE CITY = CITY_NAME AND CUST_TYPE_CD = CUSTTYPE_ID AND ROWNUM <= 1;
	OPEN cus_addr(CUSTM_ID);
	FETCH  cus_addr INTO CUR_ADDR;
	WHILE(cus_addr%found) 
	LOOP
	l_clob := l_clob || CUR_ADDR||',';
	FETCH  cus_addr INTO CUR_ADDR;
	END LOOP;
	IF cus_addr%isopen THEN
	CLOSE cus_addr;
	END IF;
	DBMS_OUTPUT.put_line( RTRIM(l_clob,',') );
 
	END;  
 
 
	PROCEDURE GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE DATE) IS
 
	EMPLOYEE_REC EMPLOYEE%ROWTYPE;
	CURSOR cur_start_date IS SELECT * FROM EMPLOYEE;  
	BEGIN
		OPEN cur_start_date;
	FETCH cur_start_date INTO EMPLOYEE_REC;
	WHILE(cur_start_date%found) 
	LOOP
	IF EMPLOYEE_REC.START_DATE < STRT_DATE THEN
	GOTO loop_again;
	END IF;
	DBMS_OUTPUT.PUT_LINE(EMPLOYEE_REC.FIRST_NAME || '  ' ||EMPLOYEE_REC.LAST_NAME );
	<<loop_again>>
	FETCH cur_start_date INTO EMPLOYEE_REC;
	END LOOP;
	IF cur_start_date%isopen THEN
	CLOSE cur_start_date;
	END IF;
	END;  
 
	END COMMON_PKG;
 

 
  CREATE SCHEMA  IF NOT EXISTS COMMON_PKG; 			  
 
	-- TYPE VARCHAR2_AARAY IS TABLE OF VARCHAR2(50) INDEX BY PLS_INTEGER;
 
	DROP TYPE  IF EXISTS COMMON_PKG.GL_VAR_TYPE CASCADE;
	CREATE type COMMON_PKG.GL_VAR_TYPE
	as(CUSTOMER_ID BIGINT,
	ADDRES_LST VARCHAR(50)[]);
	CREATE OR REPLACE FUNCTION COMMON_PKG.INIT_GL_VAR()
	RETURNS VOID LANGUAGE plpgsql
		AS $$
	BEGIN
 
		CREATE TEMPORARY TABLE  COMMON_PKG_GL_VAR AS SELECT(array[]:: VARCHAR(50)[]):: COMMON_PKG.GL_VAR_TYPE AS SWV_GL_VAR_VAL;
		RETURN;
		EXCEPTION
		WHEN SQLSTATE '42P07' THEN
			NULL;
	END; $$;
	CREATE OR REPLACE FUNCTION COMMON_PKG.GET_GL_VAR()
	RETURNS COMMON_PKG.GL_VAR_TYPE LANGUAGE plpgsql
		AS $$
		DECLARE
		SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE;
	BEGIN
 
		RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
		EXCEPTION
		WHEN OTHERS THEN
			PERFORM COMMON_PKG.INIT_GL_VAR();
			RETURN(select SWV_GL_VAR_VAL:: COMMON_PKG.GL_VAR_TYPE from COMMON_PKG_GL_VAR);
	END; $$;
	CREATE OR REPLACE PROCEDURE COMMON_PKG.SET_GL_VAR(SWP_GLVAR COMMON_PKG.GL_VAR_TYPE)
	LANGUAGE plpgsql
		AS $$
	BEGIN
 
		UPDATE COMMON_PKG_GL_VAR SET SWV_GL_VAR_VAL = SWP_GLVAR;
	END; $$;
	CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_CUSTID_BY_PARAMS(CITY_NAME				VARCHAR,
			CUST_ID_CD				VARCHAR,
			OUT CUST_ID NUMERIC)
	LANGUAGE plpgsql
		AS $$
		DECLARE
		SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
		IN_ID  BIGINT;
	BEGIN
 
		select CUST_ID into STRICT IN_ID
		from CUSTOMER
		where CITY = CITY_NAME and CUST_TYPE_CD = CUST_ID_CD LIMIT 1;
		CUST_ID := IN_ID;
	END; $$;
	CREATE OR REPLACE PROCEDURE COMMON_PKG.PREPARE_CUSTOMER_ADRESSS_BY_ID(CITY_NAME VARCHAR, CUSTTYPE_ID VARCHAR)
	LANGUAGE plpgsql
		AS $$
		DECLARE
		SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
		CUR_ADDR  VARCHAR(500);
		CUSTM_ID  NUMERIC;
		v_int  NUMERIC; 
		V_RES  VARCHAR(4000)[] default array[]:: VARCHAR(4000)[]; 
		l_clob  TEXT;
		CUS_ADDR cursor(cur_id NUMERIC) FOR select ADDRESS from CUSTOMER where cust_id =  cur_id or cust_id < 5;
	BEGIN
 
		l_clob := '';
		select CUST_ID into STRICT CUSTM_ID
		from CUSTOMER
		where CITY = CITY_NAME and CUST_TYPE_CD = CUSTTYPE_ID LIMIT 1;
		open CUS_ADDR(CUSTM_ID);
		fetch  CUS_ADDR into CUR_ADDR;
		WHILE FOUND
		loop
			l_clob := CONCAT(l_clob,CUR_ADDR,',');
			fetch  CUS_ADDR into CUR_ADDR;
		end loop;
		if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUS_ADDR') then
			close CUS_ADDR;
		end if;
		RAISE NOTICE '%',NULLIF(rtrim(l_clob,','),'');
	END; $$;
	CREATE OR REPLACE PROCEDURE COMMON_PKG.GET_FIST_NAME_OF_EMPLOYEE_BY_START_DATE(STRT_DATE TIMESTAMP)
	LANGUAGE plpgsql
		AS $$
		DECLARE
		SWV_GL_VAR  COMMON_PKG.GL_VAR_TYPE DEFAULT COMMON_PKG.GET_GL_VAR();
		EMPLOYEE_REC  EMPLOYEE%ROWTYPE;
		CUR_START_DATE cursor FOR select * from EMPLOYEE;
	BEGIN
 
		open CUR_START_DATE;
		fetch CUR_START_DATE into EMPLOYEE_REC;
		WHILE FOUND
		loop
			<< loop_again >>
			BEGIN
				if EMPLOYEE_REC.START_DATE < STRT_DATE THEN
					EXIT loop_again;
				end if;
				RAISE NOTICE '%',CONCAT(EMPLOYEE_REC.FIRST_NAME,'  ',EMPLOYEE_REC.LAST_NAME);
			END;
			fetch CUR_START_DATE into EMPLOYEE_REC;
		end loop;
		if EXISTS(SELECT 1 FROM pg_cursors WHERE NAME ilike 'CUR_START_DATE') then
			  close CUR_START_DATE;
		end if;
	END; $$;
 

 
	CREATE OR REPLACE PROCEDURE sp_hier_with_cte_and_rownum (configList IN VARCHAR2)
	IS
	refcur sys_refcursor;
	BEGIN
		FOR refcur IN (
			WITH test AS   
			(SELECT configList FROM dual)
			SELECT regexp_substr(configList, '[^;]+', 1, ROWNUM) config
			FROM test
			CONNECT BY LEVEL <= LENGTH (regexp_replace(configList, '[^;]+'))  +1
		)
		LOOP
			DBMS_OUTPUT.put_line('config= '||refcur.config);
		END LOOP;
	END;
 

 
	create or replace Procedure sp_hier_with_cte_and_rownum(IN configList VARCHAR)
	LANGUAGE plpgsql
	AS $$
	DECLARE
		refcur  REFCURSOR;
		SWV_REFCUR_rec RECORD;
	BEGIN
		FOR SWV_REFCUR_rec IN(
		WITH RECURSIVE
		test as(select configList),
		TabAl_cte AS(SELECT 1 AS LEVEL
		UNION ALL
		SELECT TabAl_cte.LEVEL+1 AS LEVEL				
		FROM TabAl_cte, test
		WHERE(TabAl_cte.LEVEL+1) <= length(regexp_replace(test.configList,'[^;]+','','g'))+1)
		SELECT  SWF_REGEXP_SUBSTR(test.configList,'[^;]+',1,row_number() over()) AS config
		FROM TabAl_cte,test)
		LOOP
			RAISE NOTICE '%',CONCAT('config= ',SWV_REFCUR_rec.config);
		END LOOP;
	END; $$;
 

 
  	CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE
	AS
	TAB_NAME VARCHAR2(15):='FOR_TYPE';
	TAB_COL VARCHAR2(12):='COL1';
	TAB_COL2 VARCHAR2(12):='COL2';
	COL_VALUE INTEGER:=2000;
	SQL_DELETE VARCHAR2(200);
	BEGIN
	SQL_DELETE:='DELETE '||TAB_NAME||' WHERE ' || TAB_COL2 || '=SYSDATE+5 AND ' ||TAB_COL||' < :value ';
	EXECUTE IMMEDIATE SQL_DELETE USING COL_VALUE;
 
	EXECUTE IMMEDIATE 'begin EXAMPLE_PROC; end;';
	END;
 

 
  	CREATE OR REPLACE PROCEDURE DYNAMIC_DELETE()
	LANGUAGE plpgsql
		AS $$
		DECLARE
		TAB_NAME  VARCHAR(15) DEFAULT 'FOR_TYPE';
		TAB_COL  VARCHAR(12) DEFAULT 'COL1';			   
		TAB_COL2  VARCHAR(12) DEFAULT 'COL2';
		COL_VALUE  INTEGER DEFAULT 2000;
		SQL_DELETE  VARCHAR(200);
	BEGIN
		SQL_DELETE := 'DELETE FROM ' || TAB_NAME || ' WHERE ' || TAB_COL2 || '= LOCALTIMESTAMP+INTERVAL ''5 day'' AND ' || TAB_COL || ' <%1$L ';
		EXECUTE format(SQL_DELETE,COL_VALUE);
 
		EXECUTE  'DO LANGUAGE plpgsql $anonymous_block$ BEGIN    CALL EXAMPLE_PROC(); END; $anonymous_block$';
	END; $$;
 

 
  	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 EXTENSION IF NOT EXISTS dblink;
 
	--use superuser to run this script 
	CREATE SERVER SWL_targetDBname_link FOREIGN DATA WRAPPER dblink_fdw 
	OPTIONS (hostaddr '127.0.0.1', dbname 'targetDBname');
 
	CREATE USER MAPPING FOR targetUser SERVER SWL_targetDBname_link 
		OPTIONS (user 'targetUser', password 'pass');
 
	GRANT USAGE ON FOREIGN ERVER SWL_targetDBname_link TO targetUser;
 
	-- procedure code
	CREATE OR REPLACE PROCEDURE AUTO_TEST(id_value DOUBLE PRECISION, 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_targetDBname_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 
		--procedure body
			INSERT INTO AUTONOMOUS_EVENT(ID, VALUE)
		VALUES(id_value, text_value);
 
			COMMIT;
		END IF;
	END; $$;
 

 
  	WITH demo1 AS(
		SELECT XMLType(
		'<hello-world>
		<word seq="1">Hello</word>
		<word seq="2">world</word>
	</hello-world>
	') XML
	FROM dual
	)
	SELECT 
		t.xml.EXTRACT('//word[@seq=1]/text()').getStringVal() col1
		, DECODE(t.xml.EXTRACT('//word[@seq=1]/text()').getStringVal(), 'Hello', 'Hell', 'END') col2
	FROM demo1 t
 

 
 	with  demo1 as(select '<hello-world>
		<word seq="1">Hello</word>
		<word seq="2">world</word>
	</hello-world>
	':: xml AS XML)
	select(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text AS COL1
		, CASE(trim(replace(xpath('//word[@seq=1]/text()',T.XML):: text,',',''),'{}'):: xml):: text WHEN 'Hello' THEN 'Hell' ELSE 'END' END AS COL2
	from demo1 T;