Generate Insert/Update SQL for DataStage Oracle Connector Automatically (Updated)

CodeProject
Last year I published a post for automatically generating insert/update SQL used in DataStage Oracle connectors. I used Oracle 11g's new function LISTAGG(), which resulted in 2 major limitations.

1. It only applies to Oracle 11g or later.
2. LISTAGG has 4000-character length limit. So it doesn't work on wide tables with more than 100 columns.

Here is an updated version without using LISTAGG(), hence removing the limitations.

Insert SQL:

	with col_info as (
	select
	c.owner,
	c.table_name,
	c.column_name,
	c.column_id,
	pc.column_name as PK_COL,
	max(c.column_id) over() as max_id
	from
	all_tab_cols c join all_constraints p on
	c.owner = p.owner and
	c.table_name = p.table_name and
	p.constraint_type = 'P'
	left join all_cons_columns pc on
	p.constraint_name = pc.constraint_name and
	p.owner = pc.owner and
	c.column_name = pc.column_name
	where
	c.owner = '#OWNER#' and
	c.table_name = '#TGT_TABLE#'
	order by c.column_id
	)
	select 'INSERT INTO #TGT_TABLE# (' as txt from dual
	union all
	select
	' ' || column_name || case when column_id <> max_id then ',' else '' end
	from col_info
	union all
	select ') VALUES (' from dual
	union all
	select
	' ORCHESTRATE.' || column_name || case when column_id <> max_id then ',' else '' end
	from col_info
	union all
	select ')' from dual
	;

view raw multiline_insert.sql hosted with ❤ by GitHub

Update SQL:

	with col_info as (
	select
	c.owner,
	c.table_name,
	c.column_name,
	c.column_id,
	pc.column_name as PK_COL
	from
	all_tab_cols c join all_constraints p on
	c.owner = p.owner and
	c.table_name = p.table_name and
	p.constraint_type = 'P'
	left join all_cons_columns pc on
	p.constraint_name = pc.constraint_name and
	p.owner = pc.owner and
	c.column_name = pc.column_name
	where
	c.owner = '#OWNER#' and
	c.table_name = '#TGT_TABLE#'
	order by c.column_id
	), col_clause as (
	select
	column_name || ' = ORCHESTRATE.' || column_name as txt,
	nvl2(pk_col, 'Y', 'N') as is_pk,
	row_number() over (partition by nvl2(pk_col, 'Y', 'N') order by column_id) as row#,
	count(*) over (partition by nvl2(pk_col, 'Y', 'N')) as tot_row
	from col_info
	)
	select 'UPDATE #TGT_TABLE# SET ' as txt from dual
	union all
	select
	' ' || txt || case when row# <> tot_row then ',' else '' end
	from col_clause
	where is_pk = 'N'
	union all
	select 'WHERE ' from dual
	union all
	select
	' ' || txt || case when row# <> tot_row then ' AND' else '' end
	from col_clause
	where is_pk = 'Y'

view raw multiline_update.sql hosted with ❤ by GitHub

DataStage Netezza Connector Sparse Lookup Error: Count field incorrect

CodeProject
Error Message:

Unexpected ODBC error occured. Reason: [SQLCODE=07002][Native=8] Count field incorrect (CC_NZStatement::executeSelect, file CC_NZStatement.cpp, line 137)

Cause:
 One or more fields in the data stream are used multiple times in the lookup SQL statement.

Work-around:
Duplicate the field(s) so that each is used exactly once.