Search code examples
oracle-databasesqlcl

start oracle .sql script through SQLcl on a remote windows host

On an Oracle database server, I have been able to schedule a nightly job that runs oracle scripts initiated from a powershell script which has this line:

sqlplus accountID/password @C:\scripts\run_scripts.sql

Now I need to achieve the same on another windows machine where the only thing of Oracle is the SQLcl client. I am able to invoke the initial_script.sql SQL script, but only interactively so far through three steps:

PS C:\sql_scripts> sql /nolog
SQL> connect id_maint/password@111.22.33.44:1521/sid
Connected.
SQL> @initial_script.sql

What I need is to have the oracle initial_script.sql script started from powershell. I have two hurdles.

  1. To connect to the remote database, I have to first get the SQLcl's SQL> prompt via the PS> sql /nolog command at the powershell command prompt, and then run the SQL> connect id_maint/password@111.22.33.44:1521/sid command at the SQL> prompt.
  2. SQL> connect id_maint/password@111.22.33.44:1521/sid works fine. But I need to invoke the oracle script initial_script.sql at the same time. However,
SQL> connect id_maint/password@111.22.33.44:1521/sid @C:\sql_scripts\initial_script.sql

incurs an error.

So what I am seeking is to get three things done (get the SQL> prompt -> connect to the remote database -> invoke the oracle script), all initiated by a power script at the PS> prompt.

Appreciated it very much if someone can help me out.

Solution

  • SQLcl is 99% compatible with SQLPlus so for almost any sqlplus command you should be able to just use sql.

    Launch the script using your existing sqlplus command but using sql instead

    sql id_maint/password@111.22.33.44:1521/sid @C:\sql_scripts\initial_script.sql