0

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.

1

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"

  • Thumb up to the side note! Yes, you're definitely right. Reconnecting from a script is not safe. I just adopted a bad approach, – Massimo Pasquini Oct 13 at 10:28
1

Sure, pass it as a parameter too.

sqlplus MY_USER/pswd@TNSID @script.sql TNSID par2 par3

Then you can use the below in the script:

connect MY_USER2/pswd@&1
  • Well... this will work for sure, but I'll take it as a workaround... Hope there could be a different approach – Massimo Pasquini Oct 9 at 9:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.