ORA-12545: Connect failed because target host or object does not exist

[ghost]

Hey Mattn, I'm using mac osx darwin amd/64, when trying to connect, I got the above error. My oracle.go is as follow:

package main

import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-oci8"
)

func main() {
    db, err := sql.Open("oci8", "user/password@host:port:sid")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()

    if err = db.Ping(); err != nil {
        fmt.Printf("Error connecting to the database: %s\n", err)
        return
    }
}

Did I do the format wrong? I used the same details on SQL Dveloper, and it's working fine there. Do you have any ideas why I keep getting this error ORA-12545? Also what would be the idiomatic way to open a connection? suppose put all the details in a string is not very secured. Many thanks in advance!

[MichaelS11]

The connect string format is located here: https://godoc.org/github.com/mattn/go-oci8#ParseDSN

[username/[password]@]host[:port][/instance_name][?param1=value1&...&paramN=valueN]

Looks like your connect string format needs to be changed to match the wanted format.

[ghost]

The connect string format is located here: https://godoc.org/github.com/mattn/go-oci8#ParseDSN

[username/[password]@]host[:port][/instance_name][?param1=value1&...&paramN=valueN]

Looks like your connect string format needs to be changed to match the wanted format.

Hey Michael, thank you for your reply! Please could you clarify what is [/instance_name] please? Also does the [] mean they are optional or do I need to wrap the string in [] please? Sorry to ask such basic questions, I have never worked on Oracle before. Thank you again!

[MichaelS11]

Looking at test code is a great way to see how things are done. Here is a good place to start:

https://github.com/mattn/go-oci8/blob/9816237c2e725b378da0a7b3f675e539f477d701/oci8Sql_test.go#L21-L35

[cjbj]

go-oci8 uses Oracle's C libraries which is also used by a majority of other products and libraries, e.g. SQL*Plus, Python cx_Oracle, Node.js node-oracledb etc etc. SQLDeveloper generally uses Java, which has different requirements. Oracle C API connection strings can have various formats; one of these is the 'Easy Connect' syntax shown above. For details see https://www.oracle.com/pls/topic/lookup?ctx=dblatest&id=GUID-B0437826-43C1-49EC-A94D-B650B6A4A6EE

[ghost]

Thank you both!! Very helpful information. I actually got all the details from an xml file that I imported onto the SQLDeveloper to access the database. Does that mean the details I got are not suitable for oci8 connection? The link on Oracle Easy Connect Naming Method is super helpful, thank you Chris, I'm sure it will benefit lots others who have same struggles. What I got from the xml file is

         <StringRefAddr addrType="OracleConnectionType">
            <Contents>BASIC</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="oraDriverType">
            <Contents>thin</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="sid">
            <Contents>SID</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="port">
            <Contents>1521</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="user">
            <Contents>USER</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="role">
            <Contents/>
         </StringRefAddr>
         <StringRefAddr addrType="NoPasswordConnection">
            <Contents>TRUE</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="subtype">
            <Contents>oraJDBC</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="OS_AUTHENTICATION">
            <Contents>false</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="KERBEROS_AUTHENTICATION">
            <Contents>false</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="ConnName">
            <Contents>CONN_NAME</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="RaptorConnectionType">
            <Contents>Oracle</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="hostname">
            <Contents>hostname.db.com</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="customUrl">
            <Contents>jdbc:oracle:thin:@hostname.db.com:1521:SID</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="SavePassword">
            <Contents>true</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="password">
            <Contents>PASSWORD</Contents>
         </StringRefAddr>
         <StringRefAddr addrType="driver">
            <Contents>oracle.jdbc.OracleDriver</Contents>
         </StringRefAddr>
      </RefAddresses>

And what I put in the sql.Open() string is USER@//hostname.db.com:1521/SID (tried USER/PASSWORD@//hostname.db.com:1521/SID and USER@hostname.db.com:1521/SID too), but still get the error ORA-12154: TNS:could not resolve the connect identifier specified. What am I doing wrong? ( •︵•｡ )

[cjbj]

Easy Connect syntax uses 'service names', not SIDs (which were deprecated a very long time ago). You could find it with select value from v$parameter where name like '%service_name%' if you have privileges. Or try one of the other connection syntaxes. Some of the snippets in https://oracle.github.io/node-oracledb/doc/api.html#connectionstrings might help

[MichaelS11]

Looking at the examples you posted, there is a lot of extra characters that are not needed, like "//" for example.

If you setup the client side tnsnames.ora file, that will easily support sid, service names, or more complicated connection string setups. The "host[:port][/instance_name]" part can be replaced with the tnsnames.ora entry name.

Can you do an Oracle command tnsping? Can you post the input and output of that command.

Also can try the below, manually replace the "type_blah_here" with the correct text.

openString := "type_user_here/type_password_here@type_ip_address_here"

 db, err := sql.Open("oci8", openString)

[ghost]

Thank you both so much for your help! I checked this morning with colleague and turned out my details were incorrect. Now have successfully connected to it. 🙏