1

I am on SQL*Plus: Release 21.0.0.0.0, on a Windows machine.

In the command prompt, when I do

C:\users\TOMMY> sqlplus /nolog

I see this

SQL>

Here I connect to the Database using a connection string(EZ Connect format)

With Credentials,

username -> dddd

pwd -> wefm@vrev

host -> local

port -> 15210

ServiceName -> abcd.xysp.gthe.com

SQL> connect dddd/"wefm@vrev"@local:15210/abcd.xysp.gthe.com

And I see Output(after a few seconds),

Connected.

My issue begins when I decide to connect from Windows directly with sqlplus. Mentioned below

C:\users\TOMMY> sqlplus -S dddd/"wefm@vrev"@local:15210/abcd.xysp.gthe.com

Results in an error, I am baffled. What am I missing

ERROR: ORA-12154: TNS:could not resolve the connect identifier specified

I have also tried putting the connection string in single quotes ('). When I do that I get this whole thing printed out, which I assume is indicative of me passing the wrong args.

SQL*Plus: Release 21.0.0.0.0 - Production Version 21.3.0.0.0

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

Use SQLPlus to execute SQL, PL/SQL and SQLPlus statements.

Usage 1: sqlplus -H | -V

-H             Displays the SQL*Plus version and the
               usage help.
-V             Displays the SQL*Plus version.

Usage 2: sqlplus [ [] [{logon | /nolog}] [] ]

is: [-C ] [-F] [-L] [-M ""] [-NOLOGINTIME] [-R ] [-S]
-C <version>   Sets the compatibility of affected commands to the
               version specified by <version>.  The version has
               the form "x.y[.z]".  For example, -C 10.2.0
-F             This option improves performance in general. It changes
               the default values settings.
               See SQL*Plus User's Guide for the detailed settings.
-L             Attempts to log on just once, instead of
               prompting on error.
-M "<options>" Sets automatic HTML or CSV markup of output.  The options
               have the form:
               {HTML html_options|CSV csv_options}
               See SQL*Plus User's Guide for detailed HTML and CSV options.
-NOLOGINTIME   Don't display Last Successful Login Time.
-R <level>     Sets restricted mode to disable SQL*Plus commands
               that interact with the file system.  The level can
               be 1, 2 or 3.  The most restrictive is -R 3 which
               disables all user commands interacting with the
               file system.
-S             Sets silent mode which suppresses the display of
               the SQL*Plus banner, prompts, and echoing of
               commands.

is: {[/][@<connect_identifier>] | / } [AS {SYSDBA | SYSOPER | SYSASM | SYSBACKUP | SYSDG | SYSKM | SYSRAC}] [EDITION=value]

Specifies the database account username, password and connect
identifier for the database connection.  Without a connect
identifier, SQL*Plus connects to the default database.

The AS SYSDBA, AS SYSOPER, AS SYSASM, AS SYSBACKUP, AS SYSDG,
AS SYSKM and AS SYSRAC options are database administration privileges.

<connect_identifier> can be in the form of Net Service Name
or Easy Connect.

  @[<net_service_name> | [[//]Host[:Port]/<service_name>] |
    [[[protocol:]//]host1{,host12}[:port1]{,host2:port2}[/service_name]
     [:server][/instance name][?[parameter name=value]
     {&parameter name=value}]]]

    <net_service_name> is a simple name for a service that resolves
    to a connect descriptor.

    Example: Connect to database using Net Service Name and the
             database net service name is ORCL.

       sqlplus username/mypassword@ORCL

    Host specifies the host name or IP address of the database
    server computer.

    Port specifies the listening port on the database server.

    <service name> specifies the service name of the database you
    want to access.

    Example: Connect to database using Easy Connect and the
             Service name is ORCL.

       sqlplus username/mypassword@Host/ORCL

The /NOLOG option starts SQL*Plus without connecting to a
database.

The EDITION specifies the value for Session Edition.

is: @|[.] [ ...]

Runs the specified SQL*Plus script from a web server (URL) or the
local file system (filename.ext) with specified parameters that
will be assigned to substitution variables in the script.

When SQLPlus starts, and after CONNECT commands, the site profile (e.g. $ORACLE_HOME/sqlplus/admin/glogin.sql) and the user profile (e.g. login.sql in the working directory) are run. The files may contain SQLPlus commands.

Refer to the SQL*Plus User's Guide and Reference for more information.

I would like a solution that only involves using a connection string. I don't want to be using tns_ora file Also without any manual intervention. I don't want to separately enter the password after I put in 'username@connect-identifier'.

Please ignore security related issues of passing in the password visibly in the cmd prompt. That is fine with me.

1
  • There are a variety of issues with special characters in the password when placed in the command line, depending on the OS or shell in play. It is not always possible to work around them. The only special character that works universally is the underscore _. The @ in particular is hard to use on the command line because it has special meaning to Oracle internally and can't always be escaped or placed in quotes. In my experience (and I've been at this for almost 30 years) you either need to use a different special character or provide the password in a separate prompt (not on the CL). Commented May 26, 2022 at 14:48

1 Answer 1

1

Solution powershell script test-connect2.ps1:

$username = "demin"
$password = '"test@test"'
$connect_string ="10.241.33.71:1521/DEV"
$log_file = "log.txt"

$list_scripts = "connect $username/$password@$connect_string"
$list_scripts = $list_scripts + " 
select 1,sysdate from dual;
@file1.sql
@file2.sql"
$list_scripts | sqlplus -s /nolog  >>$log_file

Run script:

C:\instantclient_21_3>powershell .\test-connect2.ps1  

output log.txt

     1 SYSDATE
---------- ---------
     1 27-MAY-22


     2 SYSDATE
---------- ---------
     2 27-MAY-22


     3 SYSDATE
---------- ---------
     3 27-MAY-22


SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:34:41 2022
    
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
SQL> alter user DEMIN identified by "test@test";
User altered.
SQL> exit    
    
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
    
C:\>sqlplus demin/"test@test"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:14 2022
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
ERROR:ORA-12154: TNS:could not resolve the connect identifier specified
Enter user-name:
    
C:\>sqlplus demin/\"test@test\"@dev19
SQL*Plus: Release 11.2.0.4.0 Production on Fri May 27 06:35:23 2022
           
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
            
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
            
SQL> show user
USER is "DEMIN"
SQL>

C:\app\product\18.0.0\dbhomeXE\bin>sqlplus demin/\"test@test\"@dev19                                                                                                                                               
SQL*Plus: Release 18.0.0.0.0 - Production on Fri May 27 10:28:18 2022
Version 18.4.0.0.0

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

Last Successful login time: Fri May 27 2022 06:35:29 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>

C:\instantclient_19_15>sqlplus demin/\"test@test\"@dev19                                                                                                                                                           
SQL*Plus: Release 19.0.0.0.0 - Production on Fri May 27 11:23:35 2022
Version 19.15.0.0.0

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

Last Successful login time: Fri May 27 2022 10:31:49 +03:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.12.0.0.0

SQL>


C:\instantclient_21_3>sqlplus demin/\"test@test\"@dev19                                                                                                                                                            
SQL*Plus: Release 21.0.0.0.0 - Production on Fri May 27 11:18:08 2022
Version 21.3.0.0.0
    
Copyright (c) 1982, 2021, Oracle.  All rights reserved.
    
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified
Sign up to request clarification or add additional context in comments.

3 Comments

This does not seem to work in the SQL*Plus version that I have specified. Release 21.0.0.0.0 . When I try the exact above I get this --> ERROR: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor
You can install oracle instant client 19 and sqlplus 19 and connect to database. Instant client is very small about 200kb.
You can use simple powershell script. I updated answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.