1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
| CREATE OR REPLACE FUNCTION col (PCname_table VARCHAR2, PCowner VARCHAR2) RETURN VARCHAR2 IS
LClist_columns VARCHAR2(1000);
LCcolumn_fetched VARCHAR2(50);
CURSOR LCuTable IS
SELECT COLUMN_NAME
FROM SYS.ALL_TAB_COLUMNS
WHERE UPPER(OWNER) = UPPER(PCowner)
AND UPPER(TABLE_NAME) = UPPER(PCname_table)
ORDER BY COLUMN_ID;
BEGIN
LClist_columns := '';
OPEN LCuTable;
LOOP
FETCH LCuTable INTO LCcolumn_fetched;
EXIT WHEN LCuTable%NOTFOUND;
LClist_columns := LClist_columns || LCcolumn_fetched || ', ';
IF MOD( LENGTH(LClist_columns), 50 ) = 0 THEN
LClist_columns := LClist_columns || CHR(10);
END IF;
END LOOP;
LClist_columns := '(' || SUBSTR( LClist_columns, 1, LENGTH(LClist_columns) - 2 ) || ')';
CLOSE LCuTable;
RETURN LClist_columns;
END; |
Partager