Is there any PL/SQL script to modify column value of each table in each schema of Oracle Database

Question

I have written pl/sql code to modify column values in a table of one schema. But I need to execute the same script in each table of each schema. I will take the list of the table name, column name from spreadsheet.

define schema_name = 'D';
define hours_offset = '(-3/24)';
set echo off;
set timi off;
set heading off;
set feedback off;
set linesize 250;
spool convert_01.sql
select 'set echo on;' from dual;
select 'set timi on;' from dual;
select 'set heading on;' from dual;
select 'set feedback on;' from dual;
select 'set linesize 250;' from dual;
select 'spool convert_01.log;' from dual;
select 'update '||owner||'.'||table_name||' set '||column_name||' 
='||column_name||' +    &hours_offset;' 
from dba_tab_columns where owner = '&schema_name' and 
(
data_type = 'DATE' or 
data_type like 'TIMESTAMP%'
) and
data_type not like 'TIMESTAMP%WITH TIME ZONE'
order by owner, table_name, column_name;
select 'spool off;' from dual;
spool off;

You want to take every `DATE` and `TIMESTAMP` typed column in your database and add a number to it? That seems odd, why are you trying to do that? — eaolson, Jul 18 '21 at 13:36
Please share your PL/SQL code and explain how it relates to the script above. — William Robertson, Jul 18 '21 at 21:28
By the way, you don't need to select from dual in SQL\*Plus scripts, because SQL\*Plus has `prompt`. — William Robertson, Jul 19 '21 at 20:56

score 0 · Answer 1 · answered Jul 18 '21 at 13:24

I need to execute the same script in each table of each schema

That's most probably not true. There are users (schemas) you shouldn't modify at all, e.g. SYS, SYSTEM, CTXSYS, APEX180200 and such.

Therefore, you'd rather collect list of users you want to skip (or the ones you want to affect; it's up to you). That list would then be used instead of '&schema_name'.

A simple option is to store list of users into a table and use its contents as a subquery in your current script.

Unrelated, but:

I have written pl/sql code ...

Nope; what you wrote is a pure SQL script readable by SQL*Plus (and, possibly, some other tools which understand commands you used).

Is there any PL/SQL script to modify column value of each table in each schema of Oracle Database

1 Answers1