Geek asks and answers

How to iterate over different schemes and execute some sql on each?

I have a case where I have 70 oracle circuits and I have to execute the same script on each

what would be the best way to achieve this.

Is this possible with CURSOR?

Now i do it with

ALTER SESSION SET current_schema = SCHEMA_1;
====
ALTER SESSION SET current_schema = SCHEMA_2;
====
ALTER SESSION SET current_schema = SCHEMA_3;
====

And I replace "====" with my script, I + m does it with Notepad ++, but I need to prepare the script manually, and if the script is long, I need to break it into several pieces without new lines and make a replacement for each fragment

I would like to automate this a bit more.

+3
source share
1 answer

I propose the following semi-automatic method, which does not automate your task, but shortens the search and replaces.

SQL * Plus, :

@myscriptfile.sql

, , :

select 'ALTER SESSION SET current_schema = ' || owner || ';
       @myscriptfile.sql'
  from dba_users
 where <your filter>;

/ sqlplus. , , .

+4

More articles:


All Articles