Script to create comma separated list and insert statements

[whip]

Hi all,

I put together a test script which can generate
a SQL script which in turn selects:
- a comma separated list of values
- insert statements
- a lot of other things
based on
- an object name (table, view, synonym etc.)
- an arbitrary SQL query.

What it generates is determined by the templates in the script parameters. It can handle numbers, dates and (most?) character types. The generated query escapes apostrophes in strings and can also escape CHR(10) characters if you want (see also the topic: Sessions window grid copy changed).

A known bug:
If you generate a comma separated list (see the example included), the first line will begin with a comma.

How to use it:
- Save this script to a file.
- Open the script in a Test window.
- Change any parameters according to your needs.
- Run the script.
- Click on the '...' icon to the right of the result_set parameter.
- Copy the results.

The script is included as a new message below.
Any feedback is appreciated.

Tom

 

[whip]

PL/SQL Developer Test script 3.0
102
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_boolreplace_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_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;
END IF;
--
v_result := v_result || REPLACE(REPLACE(REPLACE(v_column_template,
'%COLUMN_SEPARATOR%', CASE WHEN i > 1 THEN v_column_separator END),
'%COLUMN_NAME%', v_desc_tab(i).col_name),
'%COLUMN%',
CASE WHEN v_desc_tab(i).col_type = 12 THEN
'to_date(''''''||to_char(' || v_desc_tab(i).col_name || ',''' ||
v_date_format_mask || ''')||'''''',''''' ||
v_date_format_mask || ''''')'
WHEN v_desc_tab(i).col_type = 2 THEN
'''||decode(' || v_desc_tab(i).col_name ||
',NULL,''NULL'',to_char(' || v_desc_tab(i).col_name ||
'))||'''
ELSE
'''''''||REPLACE(REPLACE(' || v_desc_tab(i).col_name ||
','''''''',''''''''''''),CHR(10),''''' ||
CASE WHEN v_replace_chr10_flag THEN
'''||chr(10)||'''
ELSE
'||chr(10)||'
END || ''''')||'''''''
END
);
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
dual
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
0
5
result_set
1

116
0

 

[whip]

How can I paste text here with indentation preserved?

 

[Robert_G]

Using code tags
[ code ] some
code [ / code ]
without spaces in tag:

some
     code

 

[whip]

Thanks!

PL/SQL Developer Test script 3.0
102
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_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;
        END IF;
--
        v_result := v_result || REPLACE(REPLACE(REPLACE(v_column_template,
            '%COLUMN_SEPARATOR%', CASE WHEN i > 1 THEN v_column_separator END),
            '%COLUMN_NAME%', v_desc_tab(i).col_name),
            '%COLUMN%',
                CASE WHEN v_desc_tab(i).col_type = 12 THEN
                    'to_date(''''''||to_char(' || v_desc_tab(i).col_name || ',''' ||
                        v_date_format_mask || ''')||'''''',''''' ||
                        v_date_format_mask || ''''')'
                WHEN v_desc_tab(i).col_type = 2 THEN
                    '''||decode(' || v_desc_tab(i).col_name ||
                        ',NULL,''NULL'',to_char(' || v_desc_tab(i).col_name ||
                        '))||'''
                ELSE
                    '''''''||REPLACE(REPLACE(' || v_desc_tab(i).col_name ||
                        ','''''''',''''''''''''),CHR(10),''''' ||
                        CASE WHEN v_replace_chr10_flag THEN
                            '''||chr(10)||'''
                        ELSE
                            '||chr(10)||'
                        END || ''''')||'''''''
                END
        );
    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
dual
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
0
5
result_set
1
<Cursor>
116
0

 

[Marco Kalter]

Can you upload the script somewhere in its original form? This text cannot easily be copied and pasted (you get a one-liner).

 

[sumsar]

Try pasting into wordpad, save as text.

/Rasmus

 

[whip]

I can't . Does it help if I send it to you in an e-mail? (Anyone else?)
Tom

 

[Daniel Thei]

There is one work-arround:
copy the script to the clipboard and then insert it into MS-Word as HTML-Format (There are some ways you can use software of Microsoft ).
Et Voil

 

[Daniel Thei]

Hi whip,
I get an error, if I executed in a Test-Window.
It seems, that you can't execute case...end in pl/sql. Or I use the wrong Oracle-Version. My Server runs Oracle 8.1.7.

Greetings
Daniel

 

[Stew Stryker]

Pardon my stupidity today (still short on caffeine), but I don't understand what this generates. I'll guess I'll have to copy it and run it.

Can someone give a clearer (to me) description of what the purpose is?

Thanks,

Stew

 

[Stew Stryker]

Hmm, trying to run it didn't help. :-(

I checked out the parameters and thought I entered reasonable values, but...

Got an error on the call to dbms_sql.parse, saying Table or View doesn't exist. It also didn't like the dbms_sql.native parameter.

I'm running Oracle 9i. Does this require 10g?

 

[mike]

This is a nice idea. I've never thought of using the test window for anything but debugging.

I think it may have uncovered a bug with PL/SQL Developer too. I've noticed that if I use this script on a large table, and then open the result set cursor, it runs, returns about 8k of rows and then my whole PC freezes up and I have to unplug it to re-start it. I'm running version 6.0.3.893.

Mike

 

[whip]

Originally posted by Stew Stryker:
Can someone give a clearer (to me) description of what the purpose is?

Let's say you need data from a table in the form of insert statements. Of course you can get these insert statements if you export the table in PLSQL Developer. This test script can do something similar but the statements are shown in a grid and not saved to a file so you can immediately copy and paste it to another window.
However, you are not limited to "exporting" tables, you can generate similar insert statements based on an arbitrary query. By changing the template parameters you can also generate a comma separated list of values which you can e.g. use in the IN predicate of another query.

What were the parameter values when you got that error?

The script does not need 10g, I also use it in a 9i database. Daniel, I can send you an 8i version if you are interested which does not use CASE so in theory this should work for you (I can't test it).

Tom