CODEWITHSUNDEEP
oracle-databasesqlplus
Rajesh
Rajesh

Reputation: 692

How to execute .sql script or query from Oracle SQL> prompt in multiple remote instances?

I' trying to execute .sql script or query from SQL> prompt into multiple instances.

But I'm unable to execute it in this way.

SQL> conn username/password@REMOTE @sqlscript.sql
OR
SQL> conn username/password@REMOTE @select name from v$database;

Definitely, I've some other ways to complete my task to execute like below:

M:\> echo select d.name, p.profile, p.limit from dba_profiles p, v$database d where resource_name = 'PASSWORD_VERIFY_FUNCTION' and profile in ('USERS','SERVICEACCOUNTS'); | sqlplus username/password#77@remotestring

But I'm looking in different way, Please find below my execution along with error from SQL> Prompt:

SQL>
SQL> conn username/Password@remotestring @tt.sql
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYS
KM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
    <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL>



SQL> conn username/Password@remotestring @select name from v$database;
SP2-0306: Invalid option.
Usage: CONN[ECT] [{logon|/|proxy} [AS {SYSDBA|SYSOPER|SYSASM|SYSBACKUP|SYSDG|SYS
KM}] [edition=value]]
where <logon> ::= <username>[/<password>][@<connect_identifier>]
    <proxy> ::= <proxyuser>[<username>][/<password>][@<connect_identifier>]
SQL>
SQL>

It should give a query output.

Upvotes: 1

Views: 714

Answers (1)

Dmitry Demin
Dmitry Demin

Reputation: 2113

create environment variable SQLPATH

for example. Windows

C:\sql>echo %SQLPATH%
c:\sql

create file c:\sql\login.sql 

set linesize 10000
select name from v$database;
@@init.sql

create file c:\sql\init.sql 

select sysdate from dual;

Test 

C:\sql>sqlplus  /nolog

SQL*Plus: Release 11.2.0.4.0 Production on Thu Apr 4 22:41:52 2019

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

SP2-0640: Not connected
SP2-0640: Not connected
SQL> conn system/manager
Connected.

NAME
---------
ORCL


SYSDATE
---------
04-APR-19

SQL> conn system/manager@ORCL
Connected.

NAME
---------
ORCL


SYSDATE
---------
04-APR-19

SQL>

Upvotes: 1

Related Questions