Oracle – How to Access TNS Connect Identifier Passed to SQL*Plus from Run Script

oraclesqlplus

I wonder how the TNS identifier can be used within a SQL script. In this example

$ sqlplus MY_USER/pswd@TNSID @script.sql par1 par2

I'd like to get TNSID as a string in order to use it from script.sql but I have no idea where to find it.

The goal is to reconnect as a different user on the same instance.

Best Answer

You can define the environment (TNS_ALIAS) before launching sqlplus.

First let's create two users (a and b)

$sqlplus system/oracle@XEPDB1

SQL*Plus: Release 18.0.0.0.0 Production on Wed Oct 9 13:59:04 2019
Version 18.1.0.0.0

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

Last Successful login time: Wed Oct 09 2019 13:56:31 +02:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

system@XEPDB1> create user a identified by a;

User created.

Elapsed: 00:00:00.43
system@XEPDB1> create user b identified by b;

User created.

Elapsed: 00:00:00.14
system@XEPDB1> grant create session to a;

Grant succeeded.

Elapsed: 00:00:00.05
system@XEPDB1> grant create session to b;

Grant succeeded.

Elapsed: 00:00:00.01
system@XEPDB1> exit

On Linux set the TWO_TASK environment variable like this:

$export TWO_TASK=XEPDB1
$sqlplus system/oracle

SQL*Plus: Release 18.0.0.0.0 Production on Wed Oct 9 14:07:25 2019
Version 18.1.0.0.0

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

Last Successful login time: Wed Oct 09 2019 13:59:04 +02:00

Connected to:
Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

system@XEPDB1> connect a/a
Connected.
a@XEPDB1> connect b/b
Connected.
b@XEPDB1> exit
Disconnected from Oracle Database 18c Express Edition Release 18.0.0.0.0 - Production
Version 18.4.0.0.0

You will see from the sqlplus prompt the user you are logged in as.

As a side note, defining your connection with a TNS_ALIAS inside a sql-script is going to bite you hard at one point when you realize that your TNS_ALIAS is pointing to a PROD database instead of TEST database.

TWO_TASK equivalent on Windows is LOCAL ala PS>$env:LOCAL = "XEPDB1"

Related Question