
--/
create or replace script ORACLE_TO_EXASOL_PDEMO (
CONNECTION_NAME				 -- name of the database connection inside exasol -> e.g. mysql_db
,IDENTIFIER_CASE_INSENSITIVE -- TRUE if identifiers should be put uppercase
,SCHEMA_FILTER               -- filter for the schemas to generate and load, e.g. 'my_schema', 'my%', 'schema1, schema2', '%'
,TABLE_FILTER                -- filter for the tables to generate and load, e.g. 'my_table', 'my%', 'table1, table2', '%'
) RETURNS TABLE 
AS

-- Functions
function string.startsWith(String,word)
   return string.sub(String,1,string.len(word))==word
end


function get_connection_type_by_testing(CONNECTION_NAME)
	-- TEST OCI/ORA
	success, res = pquery([[
	
	select * from(
			import from ora at ::c
			statement 'select owner from ALL_TAB_COLUMNS'
					);]], {c=CONNECTION_NAME})
	if success then
		return 'ORA' 
	end

	-- TEST JDBC
	success, res = pquery([[
	
	select * from(
			import from jdbc at ::c
			statement 'select owner from ALL_TAB_COLUMNS'
					);]], {c=CONNECTION_NAME})
	if success then
		return 'JDBC' 
	end
	return 'unknown'
end


function get_connection_type(CONNECTION_NAME)
	CONNECTION_TYPE='unknown'
	-- check system table for connection type first
	success, res = pquery([[select CONNECTION_STRING from SYS.EXA_DBA_CONNECTIONS
		where CONNECTION_NAME = :c ]] , {c=CONNECTION_NAME})

	output(res.statement_text)
	
	if success then
		if #res == 0 then
			error([[The connection ]]..CONNECTION_NAME..[[ doesn't exist, please try again with a valid connection name]])
		end
		if string.startsWith(string.upper(res[1][1]), 'JDBC') then 
			CONNECTION_TYPE = 'JDBC'
		else 
			CONNECTION_TYPE = 'ORA'
		end

	else -- if user can't access this table --> try oci and jdbc
		output([[Can't access table SYS.EXA_DBA_CONNECTIONS ... will try to determine connection type by trying it out ]])
		CONNECTION_TYPE = get_connection_type_by_testing(CONNECTION_NAME)
	end
	
	output('Connection detected as '..CONNECTION_TYPE..' connection')
	-- error handling
	if CONNECTION_TYPE == 'unknown' then
		error([[The connection ]]..CONNECTION_NAME..[[ seems to fit neither an JDBC nor an OCI connection pattern, please verify that ]]..CONNECTION_NAME..[[ is a valid OCI/JDBC connection]])
	end
	return CONNECTION_TYPE
end

-- Actual script

-- check whether connection is OCI or JDBC Connection
CONNECTION_TYPE = get_connection_type(CONNECTION_NAME)



exa_upper_begin=''
exa_upper_end=''
if IDENTIFIER_CASE_INSENSITIVE == true then
	exa_upper_begin='upper('
	exa_upper_end=')'
end

if string.match(SCHEMA_FILTER, '%%') then	
	SCHEMA_STR = [[like ('']]..SCHEMA_FILTER..[['')]]		
else	
	SCHEMA_STR = [[in ('']]..SCHEMA_FILTER:gsub("^%s*(.-)%s*$", "%1"):gsub('%s*,%s*',"'',''")..[['')]]		
end

if string.match(TABLE_FILTER, '%%') then	
	TABLE_STR = [[like ('']]..TABLE_FILTER..[['')]]			
else
	TABLE_STR = [[in ('']]..TABLE_FILTER:gsub("^%s*(.-)%s*$", "%1"):gsub('%s*,%s*',"'',''")..[['')]]		
end

--check weather identity_column exists in all_tab_columns for this oracle version
success, res = pquery([[

select * from(
		import from ]]..CONNECTION_TYPE..[[ at ::c statement
			'
			select 
			COLUMN_NAME
			 from
			ALL_TAB_COLUMNS
			where
			TABLE_NAME = ''ALL_TAB_COLUMNS'' and 
			COLUMN_NAME = ''IDENTITY_COLUMN''
			'
)

]],{c=CONNECTION_NAME})

if not success then error(res.error_message) end

all_tab_cols = [[]]

if #res == 0 then --no identity column
	all_tab_cols = exa_upper_begin..[[ owner ]]..exa_upper_end..[[ as EXA_SCHEMA_NAME , owner , table_name, ]]..exa_upper_begin..[[ table_name ]]..exa_upper_end..[[ as EXA_TABLE_NAME , COLUMN_NAME, ]]..exa_upper_begin..[[column_name]]..exa_upper_end..[[  as EXA_COLUMN_NAME, data_type, cast(data_length as decimal(9,0)) data_length, cast(data_precision as decimal(9,0)) data_precision, cast(data_scale as decimal(9,0)) data_scale, cast(char_length as decimal(9,0)) char_length , nullable, cast(column_id as decimal(9,0)) column_id, null identity_column]]
else
  	all_tab_cols = exa_upper_begin..[[ owner ]]..exa_upper_end..[[ as EXA_SCHEMA_NAME , owner , table_name, ]]..exa_upper_begin..[[ table_name ]]..exa_upper_end..[[ as EXA_TABLE_NAME , COLUMN_NAME, ]]..exa_upper_begin..[[column_name]]..exa_upper_end..[[  as EXA_COLUMN_NAME, data_type, cast(data_length as decimal(9,0)) data_length, cast(data_precision as decimal(9,0)) data_precision, cast(data_scale as decimal(9,0)) data_scale, cast(char_length as decimal(9,0)) char_length , nullable, cast(column_id as decimal(9,0)) column_id, identity_column]]
end

success, res = pquery([[with ora_cols as( 

	select * from(
		import from ]]..CONNECTION_TYPE..[[ at ::c
		statement 'select ]]..all_tab_cols..[[  from all_tab_columns where owner ]]..SCHEMA_STR..[[ and table_name ]]..TABLE_STR..[['
				)
			),
	ora_base as ( --cast to correct types
		SELECT
			EXA_SCHEMA_NAME, 
			OWNER, 
			TABLE_NAME, 
			EXA_TABLE_NAME, 
			COLUMN_NAME, 
			EXA_COLUMN_NAME, 
			DATA_TYPE, 
			cast(DATA_LENGTH as integer) DATA_LENGTH, 
			cast(DATA_PRECISION as integer) DATA_PRECISION, 
			cast(DATA_SCALE as integer) DATA_SCALE, 
			cast(CHAR_LENGTH as integer) CHAR_LENGTH, 
			NULLABLE, 
			cast(COLUMN_ID as integer) COLUMN_ID, 
			IDENTITY_COLUMN
		FROM
			ora_cols
	),
	nls_format as (
		select * from (import from ]]..CONNECTION_TYPE..[[ at ::c statement 'select * from nls_database_parameters where parameter in (''NLS_TIMESTAMP_FORMAT'',''NLS_DATE_FORMAT'',''NLS_DATE_LANGUAGE'',''NLS_CHARACTERSET'')')
	),
	cr_schema as (
		with EXA_SCHEMAS as (select CURRENT_USER AS "CURRENT_SCHEMA" )
			select 'create schema if not exists "' ||  CURRENT_SCHEMA ||'";' as cr_schema from EXA_SCHEMAS
	),
	cr_tables as (
		select 'create or replace table "' || CURRENT_SCHEMA || '"."' || EXA_TABLE_NAME || '" (' || cols || '); ' || cols2 || ''
as tbls from 
(select EXA_SCHEMA_NAME, EXA_TABLE_NAME, 
		group_concat( 
		case 
			when data_type in ('CHAR', 'NCHAR') then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'char(' || char_length || ')'
			when data_type in ('VARCHAR','VARCHAR2', 'NVARCHAR2') then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'varchar(' || char_length || ')'
			when data_type = 'CLOB' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'varchar(2000000)'
			when data_type = 'XMLTYPE' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'varchar(2000000)'
			when data_type in ('DECIMAL') and (data_precision is not null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  case when data_scale > 36 then 'DECIMAL(' || 36 || ',' || 36 || ')' when data_precision > 36 and data_scale <= 36 then 'DECIMAL(' || 36 || ',' || data_scale || ')' when data_precision <= 36 and data_scale > data_precision then  'DECIMAL(' || data_scale || ',' || data_scale || ')' else 'DECIMAL(' || data_precision || ',' || data_scale || ')' end   
			when data_type = 'NUMBER' and (data_precision is not null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  case when data_scale > 36 then 'DECIMAL(' || 36 || ',' || 36 || ')' when data_precision > 36 and data_scale <= 36 then 'DECIMAL(' || 36 || ',' || data_scale || ')' when data_precision <= 36 and data_scale > data_precision then  'DECIMAL(' || data_scale || ',' || data_scale || ')' else 'DECIMAL(' || data_precision || ',' || data_scale || ')' end
			when data_type = 'NUMBER' and (data_length is not null and data_precision is null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'integer' 
			when data_type = 'NUMBER' and (data_precision is null and data_scale is null) then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'double precision'
			when data_type in ('DOUBLE PRECISION', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE') then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'double precision'
			when data_type = 'DATE' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'timestamp'
			when data_type like 'TIMESTAMP(%)%' or data_type like 'TIMESTAMP' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'timestamp'
			when data_type like 'TIMESTAMP%WITH%TIME%ZONE%' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'timestamp' 
			when data_type = 'BOOLEAN' then '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'boolean'
			-- Fallback for unsupported data types
			-- else '"' || EXA_COLUMN_NAME || '"' || ' ' ||  'varchar(2000000) /* UNSUPPORTED DATA TYPE : ' || data_type || ' */ '
			end
			
		|| case when identity_column='YES' then ' IDENTITY' end
		|| case when nullable='N' then ' NOT NULL' end
		
	order by column_id SEPARATOR ', ')
	as cols,
                group_concat( 
                        case 
                        when data_type not in ('CHAR', 'NCHAR', 'VARCHAR', 'VARCHAR2', 'NVARCHAR2', 'CLOB', 'XMLTYPE', 'DECIMAL', 'NUMBER', 'DOUBLE PRECISION', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE', 'DATE', 'BOOLEAN', 'TIMESTAMP') and data_type not like 'TIMESTAMP(%)%' and data_type not like 'TIMESTAMP%WITH%TIME%ZONE%'
                        then '--UNSUPPORTED DATA TYPE : "'|| EXA_COLUMN_NAME || '" ' || data_type || ''
                        end
                ) 
	as cols2 
        from ora_base group by EXA_SCHEMA_NAME, EXA_TABLE_NAME)
	),
	cr_import_stmts as (
		select 'import into "' || CURRENT_SCHEMA ||'"."' || EXA_TABLE_NAME || '"( ' || 
		group_concat( 
		case 
			when data_type in ('CHAR', 'NCHAR') then '"' || EXA_COLUMN_NAME || '"' 
			when data_type in ('VARCHAR','VARCHAR2', 'NVARCHAR2') then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'CLOB' then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'XMLTYPE' then '"' || EXA_COLUMN_NAME || '"' 
			when data_type in ('DECIMAL') and (data_precision is not null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'NUMBER' and (data_precision is not null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"'  
			when data_type = 'NUMBER' and (data_length is not null and data_precision is null and data_scale is not null) then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'NUMBER' and (data_precision is null and data_scale is null) then '"' || EXA_COLUMN_NAME || '"' 
			when data_type in ('DOUBLE PRECISION', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE') then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'DATE' then '"' || EXA_COLUMN_NAME || '"' 
			when data_type like 'TIMESTAMP(%)%' or data_type like 'TIMESTAMP' then '"' || EXA_COLUMN_NAME || '"' 
			when data_type like 'TIMESTAMP%WITH%TIME%ZONE%' then '"' || EXA_COLUMN_NAME || '"' 
			when data_type = 'BOOLEAN' then '"' || EXA_COLUMN_NAME || '"' 
			/* else '--UNSUPPORTED DATATYPE IN COLUMN ' || COLUMN_NAME || ' Oracle Datatype: ' || data_type  */
		end
				
	order by column_id SEPARATOR ', 
	'
	)
|| 

') from ]]..CONNECTION_TYPE..[[ at ]]..CONNECTION_NAME..[[ statement 
''select 
' || 
		group_concat(
		case 
			when data_type in ('CHAR', 'NCHAR') then '"' || column_name || '"' 
			when data_type in ('VARCHAR','VARCHAR2', 'NVARCHAR2') then '"' || column_name || '"' 
			when data_type = 'CLOB' then '"' || column_name || '"' 
			when data_type = 'XMLTYPE' then '"' || column_name || '"' 
			when data_type in ('DECIMAL') and (data_precision is not null and data_scale is not null) then '"' || column_name || '"' 
			when data_type = 'NUMBER' and (data_precision is not null and data_scale is not null) then '"' || column_name || '"'  
			when data_type = 'NUMBER' and (data_length is not null and data_precision is null and data_scale is not null) then '"' || column_name  || '"'
			when data_type = 'NUMBER' and (data_precision is null and data_scale is null) then '"' || column_name || '"' 
			when data_type in ('DOUBLE PRECISION', 'FLOAT', 'BINARY_FLOAT', 'BINARY_DOUBLE') then 'cast("' || column_name || '" as DOUBLE PRECISION)' 
			when data_type = 'DATE' then '"' || column_name || '"' 
			when data_type like 'TIMESTAMP(%)' or data_type like 'TIMESTAMP' then '"' || column_name || '"' 
			when data_type like 'TIMESTAMP%WITH%TIME%ZONE%' then 'cast("' || column_name || '" as TIMESTAMP)' 
			when data_type = 'BOOLEAN' then '"' || column_name || '"' 
			/* else '--UNSUPPORTED DATATYPE IN COLUMN ' || column_name || ' Oracle Datatype: ' || data_type  */
		end
			
	order by column_id SEPARATOR ', 
	'
	)

|| ' from ' ||  '"' || owner || '"."' || table_name || '"' || ''';'  as imp from ora_base group by owner, table_name, EXA_SCHEMA_NAME, EXA_TABLE_NAME
	)
select sql_text from (
select 1 as ord_hlp,'-- session parameter values are being taken from Oracle systemwide database_parameters and converted. However these should be confirmed before use.' as sql_text
union all
select 2, '-- Oracle DB''s NLS_CHARACTERSET is set to : ' || "VALUE" from nls_format where "PARAMETER"='NLS_CHARACTERSET'
union all
select 3,'-- ALTER SESSION SET NLS_DATE_LANGUAGE=''' || "VALUE" || ''';' from nls_format where "PARAMETER"='NLS_DATE_LANGUAGE'
union all
select 4,'-- ALTER SESSION SET NLS_DATE_FORMAT=''' || replace("VALUE",'R','Y') || ''';' from nls_format where "PARAMETER"='NLS_DATE_FORMAT'
union all
select 5,'-- ALTER SESSION SET NLS_TIMESTAMP_FORMAT=''' || replace(regexp_replace("VALUE",'XF+','.FF6'),'R','Y') || ''';' from nls_format where "PARAMETER"='NLS_TIMESTAMP_FORMAT'
union all
select 6,a.* from cr_schema a
union all
select 7,b.* from cr_tables b
where b.TBLS not like '%();%'
union all
select 8,c.* from cr_import_stmts c
where c.IMP not like '%( ) from%'
) order by ord_hlp
]],{c=CONNECTION_NAME, s=SCHEMA_FILTER, t=TABLE_FILTER})

if not success then error(res.error_message) end

return(res)
/

EXECUTE SCRIPT PUB2105.ORACLE_TO_EXASOL_PDEMO('OCI_ORACLE', true, 'RETAIL','ARTICLE', 'bla');
