CODEWITHSUNDEEP
oracle-databaseenvironment-variables
tkyass
tkyass

Reputation: 3186

setting environment varibles in .ora file

is it possible to use environment variables in .ora file for oracle connection? e.g.:

my_connection= (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=$MY_HOST)
      (PORT=$MY_PORT)
    )
    (CONNECT_DATA=
      (SERVER=dedicated)
      (SERVICE_NAME=$MY_SERVICE_NAME)
    )
  )

I tried setting them like above and it failed to connect. I'm wondering if there is another way to make it work?

Upvotes: 0

Views: 994

Answers (2)

Dmitry Demin
Dmitry Demin

Reputation: 2113

Configure the config file sqlnet.ora for an easy connection.

NAMES.DIRECTORY_PATH= (TNSNAMES,ezconnect)

create test script test_echo.sh

 #!/bin/sh

username=\"Scott\"
password=\"@T!ger\"
#ezconnect=10.89.251.205:1521/esmd
host=10.89.251.205
port=1521
service=esmd
ezconnect=$host:$port/$service

echo username:  $username
echo password:  $password
echo host:      $host
echo port:      $port
echo service:   $service
echo ezconnect  $ezconnect

echo -e 'show user \n  select 1 from dual;\nexit;' |  sqlplus -s  $username/$password@$ezconnect

oracle@esmd:~> ./test_echo.sh
username: "Scott"
password: "@T!ger"
host: 10.89.251.205
port: 1521
service: esmd
ezconnect 10.89.251.205:1521/esmd
USER is "Scott"

         1
----------
         1

Upvotes: 0

Kris Rice
Kris Rice

Reputation: 3410

No, the tnsnames.ora can't have env references. It's a static file.

An option is most the details are in the db in views like v$instance,v$LISTENER_NETWORK ,UTL_INADDR.get_host_address , dba_services

As an example, here's some sql that generates my local XE tnsnames file.

SQL> with q as ( select  UTL_INADDR.get_host_address ip, host_name from v$instance)
  2  select value || '= (DESCRIPTION= ' || a.addr 
  3*         || '(CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME='||value||')))' tnsnames
  4  from v$LISTENER_NETWORK,
  5       ( select replace(value,q.host_name,q.ip) addr 
  6           from v$LISTENER_NETWORK,q 
  7          where type='LOCAL LISTENER') a
  8  where type = 'SERVICE NAME';


SQL> /
TNSNAMES                                                                                                                      
xe= (DESCRIPTION= (ADDRESS=(PROTOCOL=TCP)(HOST=172.17.0.2)(PORT=1521))(CONNECT_DATA= (SERVER=dedicated) (SERVICE_NAME=xe)))

Upvotes: 1

Related Questions