String aggr
http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php
SELECT
'SELECT '
||
COLUMNLISTS
||' FROM ' || TBL.TABLE_NAME || ';'AS SELECTSQL,
'SELECT COUNT(*) AS CNT FROM ' || TBL.TABLE_NAME || ';' COUNTSQL
FROM user_tables TBL
JOIN
(
SELECT
TABLE_NAME,
LISTAGG(column_name,',') within group(order by column_id) as columnlists
FROM
USER_TAB_COLUMNS
WHERE TABLE_NAME LIKE 'TMM_S_SH%'
AND TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'
GROUP BY TABLE_NAME
) TC
ON TBL.TABLE_NAME=TC.TABLE_NAME
WHERE (
TBL.TABLE_NAME LIKE 'TMM_S_SH%'
AND TBL.TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'
)
;
SELECT
TABLE_NAME,
LISTAGG(column_name,',') within group(order by column_id) as columnlists
FROM
USER_TAB_COLUMNS
WHERE TABLE_NAME LIKE 'TMM_S_SH%'
AND TABLE_NAME NOT LIKE 'TMM_S_SH%_BAK'
GROUP BY TABLE_NAME;
---check the oracle version with sql
select * from v$version;
SELECT * FROM USER_TABLES WHERE ROWNUM<10;
select * from user_tab_columns where rownum<10;
SELECT
chr(34) || COLUMN_NAME || chr(34) || ','
FROM
USER_TAB_COLUMNS
WHERE TABLE_NAME='TMM_S_SH_VENDOR_SUPPORT';
INSERT INTO TABLENAME
SELECT 1,
test
q'[sqlwithsinglequote]'
FROM DUAL;
SET SERVEROUTPUT ON;
DECLARE
V_ROWS_COUNT INT :=0;
V_ERRORMSG_OUT VARCHAR2(2000) :='';
CURSOR TMM_SH_TABLES
IS
SELECT TABLE_NAME FROM USER_TABLES WHERE TABLE_Name LIKE 'T_%_T_%';
BEGIN
FOR TABLE_REC IN TMM_SH_TABLES
LOOP
EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || TABLE_REC.TABLE_NAME INTO V_ROWS_COUNT;
DBMS_OUTPUT.put_line ('SELECT COUNT(*) AS CNT FROM ' || TABLE_REC.TABLE_NAME || ';---' || V_ROWS_COUNT);
--EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || TABLE_REC.TABLE_NAME ;
--SP_NAME(PARA_NAME => TABLE_REC.TABLE_NAME ,BATCH_NUMBER => 1,ERRMSG => V_ERRORMSG_OUT);
END LOOP;
END;