1 Reply Latest reply on Apr 20, 2020 9:41 AM by Gaz in Oz

    Connect sequentially to multiple schemas

    user13117585

      Hello everyone,

       

      I have a question about sqlcl. I'm testing and comparing to SQL plus. And I was wondering if it is possible to loop through multiple schemas and execute a command? Something like

      sql /nolog 
      FOR s IN (urls)
      LOOP
        connect to url 
        execute a command
      END LOOP;
      

       

      Usually, I do this using bash scripts... But I'm thinking Since I see sqlcl allows scripting, maybe it has a solution for this?

       

       

      Regards,

        • 1. Re: Connect sequentially to multiple schemas
          Gaz in Oz

          Here's one way, using javascript:

          SQL> script
            2  conns = ['XE', 'XE', 'XE'];
            3  for (i=0; i<conns.length; i++) {
            4     // ctx.write(conns[i] + "\n");
            5     sqlcl.setStmt("connect gaz/gaz@" + conns[i] + "\n");
            6     sqlcl.run();
            7     sqlcl.setStmt("select instance_name from v$instance" + "\n");
            8     sqlcl.run();
            9  }
           10  /
          Connected.
          
          INSTANCE_NAME
          ----------------
          xe
          
          1 row selected.
          
          Connected.
          
          INSTANCE_NAME
          ----------------
          xe
          
          1 row selected.
          
          Connected.
          
          INSTANCE_NAME
          ----------------
          xe
          
          1 row selected.
          
          SQL>
          

          In the above example I connect using the same TNS alias.

          Just change your connection string with your credentials and TNS alias or what ever.