Oracle concatenate String null values

Question

I have this procedure:

DECLARE
    r_template_row template_version_row%rowtype;
    r_tag_row  tag%rowtype;
    string_insert varchar2(5000);
    v_rownumber number;
BEGIN
    string_insert := 'insert into template_version(id, name, created_by, created, deleted, rew, filename) values (2, ''new_template'', 1, SYSDATE, ''N'', 1, ''new_template.xls'')';
    DBMS_OUTPUT.PUT_LINE(string_insert);

    for r_template_row in (select * from template_version_row)
    loop
        v_rownumber := ID_Generator.nextval;
        string_insert := 'insert into template_version_row(id, template_version_id, row_number, name, view_type_id, product_id, created_by, created, deleted, rev, sheet_leg_type) values ('||v_rownumber ||', 2, '|| r_template_row.ROW_NUMBER ||', '||r_template_row.NAME ||', '|| r_template_row.VIEW_TYPE_ID ||', '|| r_template_row.PRODUCT_ID ||', 1, Sysdate,''N'', 1, '||r_template_row.SHEET_LEG_TYPE_ID ||')';
        DBMS_OUTPUT.PUT_LINE(string_insert);
        for r_tag_row in (select * from tag where TEMPLATE_VERSION_ROW_ID = r_template_row.ID)
        loop
            string_insert := 'insert into tag(id, template_version_row_id, name, tag, tag_value_position_id, data_format, tag_type_id, segment, ipt_table_id, column_name, tag_order, separator, parameter_tag, leg_type_id, segment_template_vers_row_id, created_by, created, deleted, rev, is_mandatory, is_value_mandatory, is_returned_by_get_quote) values (ID_Generator.nextval,'|| v_rownumber ||', '||
                   r_tag_row.NAME ||', '
                || r_tag_row.TAG ||', '
                || r_tag_row.DATA_FORMAT ||', '
                || r_tag_row.TAG_TYPE_ID ||','
                || r_tag_row.SEGMENT||', '
                || r_tag_row.IPT_TABLE_ID ||', '
                || r_tag_row.COLUMN_NAME ||', '
                || r_tag_row.TAG_ORDER||', '
                || r_tag_row.SEPARATOR ||', '
                || r_tag_row.PARAMETER_TAG ||', '
                || r_tag_row.LEG_TYPE_ID || ','
                || r_tag_row.LEG_TYPE_ID||', '
                || r_tag_row.SEGMENT_TEMPLATE_VERS_ROW_ID
                || ', 1 , Sysdate, ''N'', 1, '
                || r_tag_row.IS_MANDATORY || ', '
                || r_tag_row.IS_VALUE_MANDATORY||', '
                || r_tag_row.IS_RETURNED_BY_GET_QUOTE||' )';
            DBMS_OUTPUT.PUT_LINE(string_insert);
        end loop;
    end loop;
END;
/

I need in insert null values, if cursor.value is empty. Do someone know how to solve this issue. In present code if cursor is empty, then empty string are concatenate in result. Thanks for help. PS: I tried colaesce function, and it complains that I use varchar column.

Answer 1

You can use COALESCE. For columns that are not strings, use TO_CHAR to convert them to strings explicitly, so COALESCE has to deal with strings only.

string_insert := 'insert into tag(id, template_version_row_id, name, tag, tag_value_position_id, data_format, tag_type_id, segment, ipt_table_id, column_name, tag_order, separator, parameter_tag, leg_type_id, segment_template_vers_row_id, created_by, created, deleted, rev, is_mandatory, is_value_mandatory, is_returned_by_get_quote) values (ID_Generator.nextval,'|| v_rownumber ||', '||
     COALESCE(r_tag_row.name, 'NULL') || ', ' ||
     COALESCE(r_tag_row.tag, 'NULL') || ', ' ||
     COALESCE(r_tag_row.data_format, 'NULL') || ', ' ||
     COALESCE(r_tag_row.tag_type_id, 'NULL') || ', ' ||
     COALESCE(r_tag_row.segment, 'NULL') || ', ' ||
     COALESCE(TO_CHAR(r_tag_row.ipt_table_id), 'NULL') || ', ' ||
     COALESCE(r_tag_row.column_name, 'NULL') || ', ' ||
     COALESCE(TO_CHAR(r_tag_row.tag_order), 'NULL') || ', ' ||
     COALESCE(r_tag_row.separator, 'NULL') || ', ' ||
     COALESCE(r_tag_row.parameter_tag, 'NULL') || ', ' ||
     COALESCE(TO_CHAR(r_tag_row.leg_type_id), 'NULL') || ', ' ||
     COALESCE(TO_CHAR(r_tag_row.segment_template_vers_row_id), 'NULL') || ', ' ||
     '1 , sysdate, ''n'', 1, ' ||
     COALESCE(r_tag_row.is_mandatory, 'NULL') || ', ' ||
     COALESCE(r_tag_row.is_value_mandatory, 'NULL') || ', ' ||
     COALESCE(r_tag_row.is_returned_by_get_quote, 'NULL') || ' )';