Automatically generate SQL statements
Select ' Update ' | | T.table_name | | ' A set ' | |
(Select Wm_concat (' A. ' | | a.column_name | | ' = ' | | CHR (39) | | ' {' | | |
Abs (Rownum-1) | | '} ' | | Chr (39) | | '---' | |
a.comments | | CHR (13) | | CHR (10))
From User_col_comments A
where a.table_name = T.table_name)
From User_col_comments t
where t.table_name = Upper (' Com_employee ')
and rownum = 1;
--INSERT statement
Select ' INSERT INTO ' | | T.table_name | | ' (' | |
(select Wm_concat (A.column_name | | '---' | | a.comments | | CHR (13) | |
CHR (10))
From User_col_comments A
where a.table_name = t.table_name) | | ') VALUES (' | | |
(Select Wm_concat (CHR (39) | | ' {' | | | Abs (Rownum-1) | | '} ' | | CHR (39) | |
'---' | | a.comments | | CHR (13) | | CHR (10))
From User_col_comments A
where a.table_name = t.table_name) | | ‘)‘
From User_col_comments t
where t.table_name = Upper (' Com_employee ')
and rownum = 1;
--Query statements
Select (SELECT ' Select ' | |
Wmsys.wm_concat (' A. ' | | T.column_name | | '---' | | t.comments | |
CHR (13) | | CHR (10))
From Sys.user_col_comments t
Where t.table_name = a.table_name) | | ' From ' | | A.table_name | |
A
From User_col_comments A
Where a.table_name = Upper (' Com_employee ')
and Rownum = 1;
--Query statement, Memo field
Select (SELECT ' Select ' | |
Wm_concat (' A. ' | | t.column_name | | ' As ' | |
substr (NVL (Trim (t.comments), ' no Notes '), 0, 15) | | ' "' | |
CHR (13) | | CHR (10))
From Sys.user_col_comments t
where t.table_name = a.table_name) | | ' From ' | | A.table_name | |
A
From User_col_comments A
where a.table_name = Upper (' Com_employee ')
and rownum = 1;
This article from "The Son of the Sea" blog, declined reprint!
Automatically generate SQL query, delete, UPDATE, insert statement