rem Name:    forcols.sql
rem Purpose: Performs "command" on each column
rem Usage:   @forcols <%owner.table%> <%column%> <%datatype%>
rem Subject: misc
rem Attrib:  sql gen
rem Notes:   Use with caution
rem          Executes <command> for each object matched--substitutions to
rem          <command> are made as follows:
rem
rem  {o} owner
rem  {n} object_name
rem             {c}     column_name
rem  {y} data_type
rem
rem SeeAlso: @forobj

@setup1
set heading off

define cl="upper('&&2')"
define ty="upper('&&3')"

spool forcols.tmp
select
  replace(replace(replace(replace(&&cm, '{o}', o.owner), '{n}', o.object_name),
    '{y}', o.data_type), '{c}', o.column_name) text
from all_tab_columns o
where o.owner like &&o1
and o.table_name like &&n1
and o.data_type like &&ty
and o.column_name like &&cl
order by o.owner, o.table_name, o.column_name
;
spool off

set feedback on
@doit
@forcols.tmp

undef cl ty
@setdefs

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips