Oops Sorry - I forgot that was in there. I need to remember to look before I share outside of our company.
I saw Tom Kyte use it once and liked it (I copy everything, right?) Basically, it's an IF/THEN/ELSE the code is:
FUNCTION ITE(i BOOLEAN
,t VARCHAR2
,e VARCHAR2) RETURN VARCHAR2 IS
BEGIN
IF i
THEN
RETURN t;
ELSE
RETURN e;
END IF;
END ite;
I have it overloaded with a date version, number version etc.
Anyway, here is a version of what I posted earlier without that.
PL/SQL Developer Test script 3.0
141
DECLARE
-- This script generates a query based on either an object (e.g. table) name
-- or a SQL query given in the parameter query_text. Fill in either one.
-- If both are filled in, table name takes precedence.
--
-- The generated SQL statement can be found in the result_query parameter.
-- Actual result can be found in the value of the result_set cursor parameter.
--
-- Table to query.
v_table_name VARCHAR2(255) := :table_name;
-- Full SELECT statement to execute. Do not include a trailing semicolon.
-- Use alias names for expressions in the select list if necessary.
v_query_text VARCHAR2(32767) := :query_text;
--
-- Whether you want to replace chr(10) in column values with
-- an equivalent SQL expression. Defaults to FALSE.
v_replace_chr10_flag BOOLEAN := sys.diutil.int_to_bool
replace_chr10_flag);
--
-- Templates used for creating rows and concatenating column values. See
-- examples below.
v_row_template VARCHAR2(32767) := :row_template;
v_column_template VARCHAR2(32767) := :column_template;
v_column_separator VARCHAR2(32767) := :column_separator;
--
-- Change this if you like
v_date_format_mask VARCHAR2(255) := 'YYYYMMDDHH24MISS';
--
/*
-- Example for comma separated list
v_row_template VARCHAR2(32767) := ',%ROW%';
v_column_template VARCHAR2(32767) := '%COLUMN_SEPARATOR%%COLUMN%';
v_column_separator VARCHAR2(32767) := ',';
--
-- Example for INSERT statements
v_row_template VARCHAR2(32767) := 'INSERT INTO %TABLE_NAME% (%COLUMN_LIST%) VALUES (%ROW%);';
v_column_template VARCHAR2(32767) := '%COLUMN_SEPARATOR%%COLUMN%';
v_column_separator VARCHAR2(32767) := ',';
--
-- Example UPDATE statements
v_row_template VARCHAR2(32767) := 'UPDATE %TABLE_NAME% SET %ROW%;';
v_column_template VARCHAR2(32767) := '%COLUMN_SEPARATOR%%COLUMN_NAME% = %COLUMN%';
v_column_separator VARCHAR2(32767) := ',';
*/
-- Nothing to change below.
--
v_result_query VARCHAR2(32767);
v_result VARCHAR2(32767);
v_temp_result VARCHAR2(32767);
v_column_list VARCHAR(32767);
v_cursor INTEGER;
v_desc_tab dbms_sql.desc_tab;
v_col_count INTEGER;
BEGIN
IF v_table_name IS NOT NULL
THEN
v_query_text := 'SELECT * FROM ' || v_table_name;
END IF;
--
v_cursor := dbms_sql.open_cursor;
dbms_sql.parse(v_cursor
,v_query_text
,dbms_sql.native);
dbms_sql.describe_columns(v_cursor
,v_col_count
,v_desc_tab);
dbms_sql.close_cursor(v_cursor);
--
FOR i IN 1 .. v_col_count
LOOP
IF i = 1
THEN
v_column_list := v_desc_tab(i).col_name;
ELSE
v_column_list := v_column_list || ',' || v_desc_tab(i).col_name;
dbms_output.put_line(v_column_list);
END IF;
--
-- v_temp_result := replace(v_column_template, '%COLUMN_SEPARATOR%', zper_util.ITE(i => i > 1
-- ,t => v_column_separator
-- ,e => NULL
-- ));
IF i > 1
THEN
v_temp_result := replace(v_column_template, '%COLUMN_SEPARATOR%', v_column_separator);
ELSE
v_temp_result := replace(v_column_template, '%COLUMN_SEPARATOR%', NULL);
END IF;
v_temp_result := REPLACE(v_temp_result, '%COLUMN_NAME%', v_desc_tab(i).col_name);
IF v_desc_tab(i).col_type = 12
THEN
v_temp_result := REPLACE(v_temp_result
, '%COLUMN%'
,'to_date(''''''||to_char(' ||
v_desc_tab(i).col_name ||
',''' || v_date_format_mask
|| ''')||'''''',''''' ||
v_date_format_mask || ''''')'
);
ELSIF v_desc_tab(i).col_type = 2
THEN
v_temp_result := REPLACE(v_temp_result
, '%COLUMN%'
, '''|| decode(' ||
v_desc_tab(i).col_name ||
',NULL,''NULL'',to_char(' || v_desc_tab(i).col_name || '))||'''
);
ELSE
v_temp_result := REPLACE(v_temp_result
, '%COLUMN%'
, '''''''||REPLACE(REPLACE(' ||
v_desc_tab(i).col_name ||
','''''''',''''''''''''),CHR(10),''''' ||
-- The chr10 replace is commented out! CASE
-- WHEN v_replace_chr10_flag
-- THEN
-- '''||chr(10)||'''
-- ELSE
'||chr(10)||'
-- END
|| ''''')||'''''''
);
END IF;
v_temp_result := REPLACE(v_temp_result,'%ROW', v_result);
v_result := v_result || v_temp_result;
END LOOP;
--
v_result_query := 'SELECT ''' || REPLACE(REPLACE(REPLACE(v_row_template
,'%TABLE_NAME%'
,v_table_name)
,'%COLUMN_LIST%'
,v_column_list)
,'%ROW%'
,v_result) || ''' FROM (' || v_query_text || ')';
--
:result_query := v_result_query;
OPEN :result_set FOR v_result_query;
END;
8
table_name
1
zper_degree_info
5
query_text
0
5
replace_chr10_flag
1
1
3
row_template
1
INSERT INTO %TABLE_NAME% (%COLUMN_LIST%) VALUES (%ROW%);
5
column_template
1
%COLUMN_SEPARATOR%%COLUMN%
5
column_separator
1
,
5
result_query
1
SELECT 'INSERT INTO zper_degree_info (DEGREE_CODE,DEGREE_ABBR,DEGREE_DESCRIPTION,DEGREE_RANK,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,CREATED_BY,CREATION_DATE) VALUES ('''||REPLACE(REPLACE(DEGREE_CODE,'''',''''''),CHR(10),''||chr(10)||'')||''','''||REPLACE(REPLACE(DEGREE_ABBR,'''',''''''),CHR(10),''||chr(10)||'')||''','''||REPLACE(REPLACE(DEGREE _DESCRIPTION,'''',''''''),CHR(10),''||chr(10)||'')||''','|| decode(DEGREE_RANK,NULL,'NULL',to_char(DEGREE_RANK))||',to_date('''||to_char(LAST_UPDATE_DATE,'YYYYMMDDHH24MISS')||''',''YYYYMMDDHH24MISS''),'|| decode(LAST_UPDATED_BY,NULL,'NULL',to_char(LAST_UPDATED_BY))||','|| decode(LAST_UPDATE_LOGIN,NULL,'NULL',to_char(LAST_UPDATE_LOGIN))||','|| decode(CREATED_BY,NULL,'NULL',to_char(CREATED_BY))||',to_date('''||to_char(CREATION_DATE,'YYYYMMDDHH24MISS')||''',''YYYYMMDDHH24MISS''));' FROM (SELECT * FROM zper_degree_info)
5
result_set
1
116
0
