Drop sequence USER_SEQ

DROP SEQUENCE user_seq

Create sequence USER_SEQ

CREATE SEQUENCE user_seq START WITH 1 INCREMENT BY 1

Drop table USER_TEMP

DROP TABLE user_temp

Create table USER_TEMP

CREATE TABLE user_temp    (user_id NUMBER, 
					   user_name VARCHAR2(4000), 					  
					   user_login VARCHAR2(4000),
					   user_address1 VARCHAR2(4000),
					   user_address2 VARCHAR2(4000),
					   user_address3 VARCHAR2(4000),
					   user_address4 VARCHAR2(4000),
					   user_address5 VARCHAR2(4000),
					   user_phone_type1 VARCHAR2(50),
					   user_phone_number1 VARCHAR2(50),
					   user_phone_type2 VARCHAR2(50),
					   user_phone_number2 VARCHAR2(50),
					   user_email VARCHAR2(100),
					   user_create_date TIMESTAMP
					   ) NOLOGGING

Bulk load random data into table USER_TEMP

BEGIN
	FOR i in 1..10000
	LOOP
			INSERT INTO user_temp
			VALUES (
					i,
					'DummyFName'||i||'UserLName'||i,
					'Dummy'||i||'User'||i,
					i||' Sporting Green',
					DECODE (ROUND (DBMS_RANDOM.VALUE (1, 3)), 1, 'South San Francisco', 2, 'North Florida', 3, 'New York'),
					DECODE (ROUND (DBMS_RANDOM.VALUE (1, 3)), 1, 'CA', 2, 'NY', 3, 'FL'),
					TRUNC(DBMS_RANDOM.VALUE (1, 100000)),
					'United States of America',
					'Office',
					TRUNC(DBMS_RANDOM.VALUE (100, 999))||'-'||TRUNC(DBMS_RANDOM.VALUE (100, 999))||'-'||TRUNC(DBMS_RANDOM.VALUE (100, 999)),
					'Mobile',
					TRUNC(DBMS_RANDOM.VALUE (100, 999))||'-'||TRUNC(DBMS_RANDOM.VALUE (100, 999))||'-'||TRUNC(DBMS_RANDOM.VALUE (100, 999)),
					'Dummy'||i||'User'||i||'@abc.com',
					SYSTIMESTAMP				
				);
	END LOOP;
	COMMIT;
END;

Check data loaded into table

SELECT COUNT(1) FROM user_temp

Drop table STAGE_USER

DROP TABLE stage_user

Create table STAGE_USER

CREATE TABLE stage_user (
							id VARCHAR2(32),
							create_date TIMESTAMP,
							user_json CLOB
						) NOLOGGING

Convert data in table USER_TEMP into JSON format and load into staging table STAGE_USER.USER_JSON CLOB column

DECLARE
	v_json_array	CLOB;
	TYPE t_usr_id_arr    IS TABLE OF NUMBER(19);
	v_usr_id_arr	t_usr_id_arr:=t_usr_id_arr();
	
	CURSOR bulkinsrt 
	IS
	SELECT user_id FROM user_temp ORDER BY 1;	
BEGIN
	OPEN bulkinsrt;
	LOOP
		FETCH bulkinsrt BULK COLLECT INTO v_usr_id_arr LIMIT 1000;

		FOR i IN v_usr_id_arr.FIRST .. v_usr_id_arr.LAST
		LOOP
			SELECT json_object
			(	
				'user_id'    		VALUE user_id,
				'user_name'   		VALUE user_name,
				'user_login'   		VALUE user_login,
				'user_address'   	VALUE json_object('street'  VALUE user_address1,
									  'city'    VALUE user_address2,
									  'state'   VALUE user_address3,
									  'zipCode' VALUE user_address4,
									  'country' VALUE user_address5),
				'user_phone' 		VALUE json_array (json_object ('type'	VALUE user_phone_type1,
									    						   'number'	VALUE user_phone_number1),
							       					  json_object ('type'	VALUE user_phone_type2,
									    						   'number'	VALUE user_phone_number2)
							      					),																   		   
				'user_email'   		VALUE user_email,
				'user_create_date'	VALUE user_create_date              
			FORMAT JSON
			)
				INTO v_json_array
			FROM user_temp
			WHERE user_id=v_usr_id_arr(i);

			INSERT INTO stage_user VALUES (SYS_GUID(), SYSTIMESTAMP, v_json_array);
		END LOOP;
		COMMIT;
		
		EXIT WHEN v_usr_id_arr.COUNT=0;
	END LOOP;
	CLOSE bulkinsrt;
EXCEPTION
WHEN OTHERS THEN
	dbms_output.put_line(SQLERRM);
END;

Check data loaded into staging table

SELECT COUNT(1) FROM stage_user

Drop sequence ROLE_SEQ

DROP SEQUENCE role_seq

Create sequence ROLE_SEQ

CREATE SEQUENCE role_seq START WITH 1 INCREMENT BY 1

Drop table ROLE_TEMP

DROP TABLE role_temp

Create table ROLE_TEMP

CREATE TABLE role_temp (role_id NUMBER, 
		   role_name VARCHAR2(1000)
		   ) NOLOGGING

Bulk load random data into table ROLE_TEMP

BEGIN
	FOR i in 1..500
	LOOP
			INSERT INTO role_temp 
			VALUES (
					i,
					'DummyRole'||i			
				);
	END LOOP;
	COMMIT;
END;

Check data loaded into table

SELECT COUNT(1) FROM role_temp

Drop table STAGE_ROLE

DROP TABLE stage_role

Create table STAGE_ROLE

CREATE TABLE stage_role
						(
							id VARCHAR2(32),
							create_date TIMESTAMP,
							role_json CLOB
						) NOLOGGING

Convert data in table ROLE into JSON format and load into staging table STAGE_ROLE.ROLE_JSON CLOB column

DECLARE
	v_json_array	CLOB;
	TYPE t_role_id_arr   IS TABLE OF NUMBER(19);
	v_role_id_arr	t_role_id_arr:=t_role_id_arr();
	
	CURSOR bulkinsrt 
	IS
	SELECT role_id FROM role_temp ORDER BY 1;	
BEGIN
	OPEN bulkinsrt;
	LOOP
		FETCH bulkinsrt BULK COLLECT INTO v_role_id_arr LIMIT 100;

		FOR i IN v_role_id_arr.FIRST .. v_role_id_arr.LAST
		LOOP
			SELECT json_object
			(	
				'role_id'    			VALUE role_id,
				'role_name'   			VALUE role_name        
			FORMAT JSON
			)
				INTO v_json_array
			FROM role_temp
			WHERE role_id=v_role_id_arr(i);

			INSERT INTO stage_role VALUES (SYS_GUID(), SYSTIMESTAMP, v_json_array);
		END LOOP;
		COMMIT;
		
		EXIT WHEN v_role_id_arr.COUNT=0;
	END LOOP;
	CLOSE bulkinsrt;
EXCEPTION
WHEN OTHERS THEN
	dbms_output.put_line(SQLERRM);
END;

Check data loaded into staging table

SELECT COUNT(1) FROM stage_role

Drop sequence USERROLEMEM_SEQ

DROP SEQUENCE userrolemem_seq

Create sequence USERROLEMEM_SEQ

CREATE SEQUENCE userrolemem_seq START WITH 1 INCREMENT BY 1

Drop table USERROLEMEM

DROP TABLE userrolemem

Create table USERROLEMEM

CREATE TABLE userrolemem (user_id NUMBER,
		   role_id NUMBER, 
		   role_name VARCHAR2(4000),
		   userrole_create_date TIMESTAMP
		   ) NOLOGGING

Bulk load data into table USERROLEMEM

BEGIN
	FOR i in 1..10000
	LOOP
		FOR j IN 1 .. 100
		LOOP
			INSERT INTO userrolemem 
			VALUES (
					i,
					j,	
					'DummyRole'||j,			
					SYSTIMESTAMP	
				);
		END LOOP;
	END LOOP;
	COMMIT;
END;

Check data loaded into table

SELECT COUNT(1) FROM userrolemem

Drop table STAGE_USERMEM

DROP TABLE stage_usermem

Create table STAGE_USERMEM

CREATE TABLE stage_usermem (
							id VARCHAR2(32),
							create_date TIMESTAMP,
							userrolemem_json CLOB
						) NOLOGGING

Convert data in table USERROLEMEM into JSON format and load into staging table STAGE_USERMEM.USERROLEMEM_JSON CLOB column

DECLARE
	TYPE t_role_id_arr   IS TABLE OF NUMBER(19);
	v_role_id_arr	t_role_id_arr:=t_role_id_arr();
	TYPE t_usr_id_arr    IS TABLE OF NUMBER(19);
	v_user_id_arr	t_usr_id_arr:=t_usr_id_arr();
	TYPE t_role_name_arr   IS TABLE OF VARCHAR2(10000);
	v_role_name_arr	t_role_name_arr:=t_role_name_arr();
	
	v_role_sql 	    CLOB;
	v_role_main_sql	    CLOB;
	v_main_sql	    CLOB;
	v_sql		    VARCHAR2(32767):=NULL;
	
	CURSOR bulkinsrt 
	IS
	SELECT DISTINCT user_id FROM userrolemem;	
BEGIN
	OPEN bulkinsrt;
	LOOP
		-- Fetch user keys
		FETCH bulkinsrt BULK COLLECT INTO v_user_id_arr LIMIT 1000;
		EXIT WHEN v_user_id_arr.COUNT=0;

		FOR i IN 1 .. v_user_id_arr.COUNT
		LOOP
			-- Fetch Role information for user
			EXECUTE IMMEDIATE 'SELECT role_id, role_name FROM userrolemem WHERE user_id=:1' BULK COLLECT INTO v_role_id_arr, v_role_name_arr USING v_user_id_arr(i);

			-- Build array for user-role mem data
			IF (v_role_id_arr.COUNT>0) THEN
				FOR j IN v_role_id_arr.FIRST .. v_role_id_arr.LAST
				LOOP
					v_role_sql := v_role_sql || TO_CLOB('{"role_id":'|| v_role_id_arr(j) ||',"role_name":"'|| v_role_name_arr(j) ||'","userrole_create_date":"16-APR-17"},');
				END LOOP;
			END IF;
			
			-- Remove extra comma from array for user-role mem data
			v_role_main_sql := SUBSTR(v_role_sql, 0, LENGTH(v_role_sql) - 1);
			
			-- Build json for user with its role mem data
			v_main_sql := v_main_sql || '{"user_id":'|| v_user_id_arr(i) ||',"user_mem_dtls":{"user_role_mem":['|| v_role_main_sql ||'],"user_other_mem":"null"}}';

			-- Insert json for user with its role mem data into staging table
			v_sql := 'INSERT INTO stage_usermem VALUES (:1,:2,:3)';
			EXECUTE IMMEDIATE v_sql USING SYS_GUID(), SYSTIMESTAMP, v_main_sql;
			
			-- Set variables to NULL
			v_sql:=NULL;
			v_main_sql:=NULL;
			v_role_main_sql:=NULL;
			v_role_sql:=NULL;

			-- Purge collection variables data
			v_role_id_arr.DELETE;
			v_role_name_arr.DELETE;
		END LOOP;
		COMMIT;
	END LOOP;
	CLOSE bulkinsrt;
EXCEPTION
WHEN OTHERS THEN
	RAISE_APPLICATION_ERROR(-20100,
				'ORA Error Code =>'|| SQLERRM
				 || CHR(10)
				 ||'ORA Error Stack => '
				 || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_STACK,1,500)
				 || CHR(10)
				 ||'ORA Error Backtrace => '
				 || SUBSTR (DBMS_UTILITY.FORMAT_ERROR_BACKTRACE,1,500)
				);
END;

Check data loaded into staging table

SELECT COUNT(1) FROM stage_usermem