Depends on what you want to do...you can also use user_tab_columns as
follows:
- First load the data as is from whatever source you have into the
table (say T1).
- Then try the following SQL in sqlplus...
SELECT 'UPDATE T1 '||CHR(10) FROM dual
UNION
SELECT 'SET '||column_name||'=||UPPER('||column_name||'),'||
CHR(10)
FROM user_tab_columns
WHERE DATA_TYPE='VARCHAR2'
AND table_name = ' T1'
AND column_id =1
UNION
SELECT 'column_name||'=||UPPER('||column_name||'),'||CHR(10)
FROM user_tab_columns
WHERE DATA_TYPE='VARCHAR2'
AND table_name = ' T1'
AND column_id between 2 and (SELECT max(column_id) FROM
user_tab_columns WHERE table_name = 'T1'))
UNION
SELECT 'SET '||column_name||'=||UPPER('||column_name||')'
FROM user_tab_columns
WHERE DATA_TYPE='VARCHAR2'
AND table_name = ' T1'
AND column_id = (SELECT max(column_id) FROM user_tab_columns
WHERE table_name = 'T1'))
- This SQL should produce something like
UPDATE T1
SET column1 = UPPER(column1),
column2 = UPPER(column2),
.......
........
.......
column174= UPPER(column174),
column175= UPPER(column175)
The CHR(10) produces a linefeed. You can set heading off, set pagesize
0, set feedback off etc and spool this output to a file.
Edit the file for any minor issues and simply run the file from inside
sqlplus.
I have assumed a Unix O/S here. Hope this helps.
Regards,
z1hou1
Received on Sun Oct 07 2007 - 22:11:39 CDT
