I like this one!
Tom Kyte once wrote a procedure to output table contents one column at a time. For some history and a newer solution, see “Print a table with one column name + value per row”.
Using SQL macros, we can get a solution that is even better in my view. My previous solution was a SELECT statement, which I like, but as such it does not hide the code inside a function or procedure, and it is not really configurable.
To stick different column values into one column, we generally have to convert non-string values to VARCHAR2. For fun, I’ll create a little table with several datatypes:
create table datatypes (
C_NUMBER NUMBER,
C_VARCHAR2 VARCHAR2(16),
C_DATE DATE,
C_RAW RAW(16),
C_BINARY_FLOAT BINARY_FLOAT,
C_BINARY_DOUBLE BINARY_DOUBLE,
C_CLOB CLOB,
C_BFILE BFILE,
C_TIMESTAMP TIMESTAMP,
C_TIMESTAMP_WITH_TIME_ZONE TIMESTAMP WITH TIME ZONE,
C_INTERVAL_YEAR_TO_MONTH INTERVAL YEAR TO MONTH,
C_INTERVAL_DAY_TO_SECOND INTERVAL DAY TO SECOND
);
insert into datatypes
select 3-level,'varchar2',sysdate,hextoraw('FF0102030405'),
1.1,2.2,'clob',bfilename('DOWNLOAD_DIR', 'x.txt'),
localtimestamp, localtimestamp,
interval '1' year, interval '2' day
from dual
connect by level <= 2;
All these datatypes should work from version 19.6 on. If we have access to the Autonomous Cloud 19c version, or to version 21c, we can add some extra datatypes:
create or replace view v_datatypes as
select d.*,
sys.odcinumberlist(1) c_varray,
sys.FI_CATEGORICALS('A') c_nested_table,
SYS.AWRRPT_TEXT_TYPE('a') c_object,
JSON_object('sysdate':sysdate) j_object
from datatypes d;
Before showing the code of my PRINT_TABLE_MACRO, I’ll show what it does to the view V_DATATYPES. Again, if you try this on a non-cloud 19c version, use the table DATATYPES instead.
select * from print_table_macro(
p_table => v_datatypes
);
| RN | COLUMN_NAME | COLUMN_VALUE |
|---|---|---|
| 1 | C_NUMBER | 2 |
| 1 | C_VARCHAR2 | varchar2 |
| 1 | C_DATE | 2021-12-16T13:40:08 |
| 1 | C_RAW | FF0102030405 |
| 1 | C_BINARY_FLOAT | 1,10000002 |
| 1 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 1 | C_CLOB | clob |
| 1 | C_BFILE | bfilename(‘DOWNLOAD_DIR’, ‘x.txt’) |
| 1 | C_TIMESTAMP | 2021-12-16T14:40:8.003345 |
| 1 | C_TIMESTAMP_WITH_TIME_ZONE | 2021-12-16T14:40:8.003345 Europe/Paris |
| 1 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 1 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 1 | C_VARRAY | [1] |
| 1 | C_NESTED_TABLE | [“A”] |
| 1 | C_OBJECT | {“OUTPUT”:”a”} |
| 1 | J_OBJECT | {“sysdate”:”2021-12-16T13:40:14″} |
| 2 | C_NUMBER | 1 |
| 2 | C_VARCHAR2 | varchar2 |
| 2 | C_DATE | 2021-12-16T13:40:08 |
| 2 | C_RAW | FF0102030405 |
| 2 | C_BINARY_FLOAT | 1,10000002 |
| 2 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 2 | C_CLOB | clob |
| 2 | C_BFILE | bfilename(‘DOWNLOAD_DIR’, ‘x.txt’) |
| 2 | C_TIMESTAMP | 2021-12-16T14:40:8.003345 |
| 2 | C_TIMESTAMP_WITH_TIME_ZONE | 2021-12-16T14:40:8.003345 Europe/Paris |
| 2 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 2 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 2 | C_VARRAY | [1] |
| 2 | C_NESTED_TABLE | [“A”] |
| 2 | C_OBJECT | {“OUTPUT”:”a”} |
| 2 | J_OBJECT | {“sysdate”:”2021-12-16T13:40:14″} |
Notice that the input rows are not sorted: I just use ROWNUM to identify each row. Also, each schema-level “User Defined Type” structure is converted to a string in JSON format. Now suppose I want to identify each row by one more columns, and sort by those columns.
select * from print_table_macro(
p_table => v_datatypes,
p_key_cols => columns(c_number)
);
| C_NUMBER | COLUMN_NAME | COLUMN_VALUE |
|---|---|---|
| 1 | C_VARCHAR2 | varchar2 |
| 1 | C_DATE | 2021-12-16T13:40:08 |
| 1 | C_RAW | FF0102030405 |
| 1 | C_BINARY_FLOAT | 1,10000002 |
| 1 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 1 | C_CLOB | clob |
| 1 | C_BFILE | bfilename(‘DOWNLOAD_DIR’, ‘x.txt’) |
| 1 | C_TIMESTAMP | 2021-12-16T14:40:8.003345 |
| 1 | C_TIMESTAMP_WITH_TIME_ZONE | 2021-12-16T14:40:8.003345 Europe/Paris |
| 1 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 1 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 1 | C_VARRAY | [1] |
| 1 | C_NESTED_TABLE | [“A”] |
| 1 | C_OBJECT | {“OUTPUT”:”a”} |
| 1 | J_OBJECT | {“sysdate”:”2021-12-16T13:45:50″} |
| 2 | C_VARCHAR2 | varchar2 |
| 2 | C_DATE | 2021-12-16T13:40:08 |
| 2 | C_RAW | FF0102030405 |
| 2 | C_BINARY_FLOAT | 1,10000002 |
| 2 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 2 | C_CLOB | clob |
| 2 | C_BFILE | bfilename(‘DOWNLOAD_DIR’, ‘x.txt’) |
| 2 | C_TIMESTAMP | 2021-12-16T14:40:8.003345 |
| 2 | C_TIMESTAMP_WITH_TIME_ZONE | 2021-12-16T14:40:8.003345 Europe/Paris |
| 2 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 2 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 2 | C_VARRAY | [1] |
| 2 | C_NESTED_TABLE | [“A”] |
| 2 | C_OBJECT | {“OUTPUT”:”a”} |
| 2 | J_OBJECT | {“sysdate”:”2021-12-16T13:45:50″} |
Finally, I can exclude columns if I want.
select * from print_table_macro(
p_table => v_datatypes,
p_key_cols => columns(c_number),
p_exclude_cols => columns(C_BFILE,C_TIMESTAMP,C_TIMESTAMP_WITH_TIME_ZONE)
);
| C_NUMBER | COLUMN_NAME | COLUMN_VALUE |
|---|---|---|
| 1 | C_VARCHAR2 | varchar2 |
| 1 | C_DATE | 2021-12-16T13:40:08 |
| 1 | C_RAW | FF0102030405 |
| 1 | C_BINARY_FLOAT | 1,10000002 |
| 1 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 1 | C_CLOB | clob |
| 1 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 1 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 1 | C_VARRAY | [1] |
| 1 | C_NESTED_TABLE | [“A”] |
| 1 | C_OBJECT | {“OUTPUT”:”a”} |
| 1 | J_OBJECT | {“sysdate”:”2021-12-16T13:47:36″} |
| 2 | C_VARCHAR2 | varchar2 |
| 2 | C_DATE | 2021-12-16T13:40:08 |
| 2 | C_RAW | FF0102030405 |
| 2 | C_BINARY_FLOAT | 1,10000002 |
| 2 | C_BINARY_DOUBLE | 2,2000000000000002 |
| 2 | C_CLOB | clob |
| 2 | C_INTERVAL_YEAR_TO_MONTH | +01-00 |
| 2 | C_INTERVAL_DAY_TO_SECOND | +02 00:00:00.000000 |
| 2 | C_VARRAY | [1] |
| 2 | C_NESTED_TABLE | [“A”] |
| 2 | C_OBJECT | {“OUTPUT”:”a”} |
| 2 | J_OBJECT | {“sysdate”:”2021-12-16T13:47:36″} |
The code (depends on package SQM_UTIL)
create or replace function print_table_macro(
p_table in dbms_tf.table_t,
p_key_cols in dbms_tf.columns_t default null,
p_exclude_cols in dbms_tf.columns_t default null
) return clob sql_macro is
l_col_to_strings long;
l_col_column_names long;
l_sql long;
l_key_list varchar2(4000);
l_order_by varchar2(4000);
l_exclude_cols dbms_tf.columns_t;
begin
l_exclude_cols := p_key_cols;
if l_exclude_cols is null then
l_exclude_cols := p_exclude_cols;
else
if p_exclude_cols is not null then
l_exclude_cols := l_exclude_cols multiset union p_exclude_cols;
end if;
end if;
sqm_util.col_to_strings(p_table, l_col_to_strings, l_exclude_cols);
sqm_util.col_column_names(p_table, l_col_column_names, l_exclude_cols);
if p_key_cols is null then
l_key_list := 'rownum rn';
l_order_by := null;
else
sqm_util.list_columns(p_key_cols, l_key_list);
l_order_by := 'order by ' || l_key_list;
end if;
l_sql := '
select * from (
select ' || l_key_list || ',' || l_col_to_strings || ' from p_table
' || l_order_by || '
)
unpivot include nulls (column_value for column_name in (' || l_col_column_names || '))
';
dbms_output.put_line(l_sql);
return l_sql;
end print_table_macro;
/
Posts on SQL table macros
- SQL table macros 1: a moving target?
- SQL table macros 2: “polymorphic” is not “dynamic”
- SQL table macros 3: parameter strangeness
- SQL table macros 4: distribute into equal parts
- SQL table macros 5: dynamic but dangerous!
- SQL table macros 6: SQM_UTIL package
- SQL table macros 7: Select excluding
- SQL table macros 8: Print table
- SQL table macros 9: just pivot!
- SQL table macros 10: JUST_PIVOT examples
- SQL table macros 11: JUST_PIVOT for 21c (and 19c)
- SQL table macros 12: compare tables or queries
- SQL table macros 13: compare with primary key
An Oracle Programmer 