4 Replies Latest reply: Jan 17, 2014 1:39 PM by ddevienne RSS

    ORA-12504 error when connecting to Oracle 11G DB using OCI and instantclient-basic-nt-11.2.0.3.0.zip

    910885

      Dear Friends,

       

      I am using OCI and instant_client (11.2.0.3.0)  in my C++ program (on Windows 7) to connect remotely to 11G database.

       

      While doing a connection using server name as well as servername:port/service_name as connection strings, I am getting error of ORA-12504 TNS:listener was not given the SERVICE_NAME in CONNECT_DATA.

       

      According to instant client FAQ & white paper at http://www.oracle.com/technetwork/database/features/instant-client/index.html, no tnsnames.ora is required for instant client. Then why could I be getting this error?

       

      I ensured that only the instant client directory is in Path environment variable. There was a ODBC driver installed on this same host. Could that be modifying the behavior of instant client?

       

      Kindly guide.

       

      Many thanks in advance for your help and time.

       

      Best Regards,

      - ganesh

        • 1. Re: ORA-12504 error when connecting to Oracle 11G DB using OCI and instantclient-basic-nt-11.2.0.3.0.zip
          Jan-Marten Spit

          what is the exact connect string you are using?

           

          are you calling OCIServerAttach before OCISessionBegin?

          • 2. Re: ORA-12504 error when connecting to Oracle 11G DB using OCI and instantclient-basic-nt-11.2.0.3.0.zip
            910885

            >>what is the exact connect string you are using?

            For connection, I am using the plain hostname on which the DB server is running.

             

            >>are you calling OCIServerAttach before OCISessionBegin?

            I am not using OCISessionBegin. For connecting to DB server, I am using the following sequence of OCI calls (in this same order):

            1) g_OCIEnvNlsCreate

            2) g_OCIHandleAlloc //-- error handle: OCI_HTYPE_ERROR

            3) g_OCIHandleAlloc // --- sevice context: OCI_HTYPE_SVCCTX

            4) Setting driver name and application name for the session:

                OCISession* usersession = 0;

                ret = g_OCIAttrGet(m_pSvcCtx, OCI_HTYPE_SVCCTX, &usersession, NULL,

                                         OCI_ATTR_SESSION, m_pErrHdl);

                if (ret == OCI_SUCCESS)

                {

                    ret = g_OCIAttrSet(usersession, OCI_HTYPE_SESSION, (void*)"OCI_DRV", 2,OCI_ATTR_DRIVER_NAME, m_pErrHdl);

                    ret = g_OCIAttrSet(usersession, OCI_HTYPE_SESSION, (void*)"ClientApplication", <ub4>(strlen("DataReconScanning")), OCI_ATTR_CLIENT_IDENTIFIER, m_pErrHdl);

                }

            5) g_OCILogon() using the user, password and host name, as below:

            g_OCILogon(m_pDbCon, m_pErrHdl, &m_pSvcCtx,

                                           (text*)user.c_str(), static_cast<ub4>(user.length()),

                                           (text*)pass.c_str(), static_cast<ub4>(pass.length()),

                                           (text*)host.c_str(), static_cast<ub4>(host.length()));

             

            Thanks.

            • 3. Re: ORA-12504 error when connecting to Oracle 11G DB using OCI and instantclient-basic-nt-11.2.0.3.0.zip
              Kmohan-Oracle

              If you are using Instant Client, you cannot bequeath server process. You need to connect through a connect string that the database listener can resolve. Please see these from Instant Client FAQs. Just the database hostname is not a proper connect string.

              http://www.oracle.com/technetwork/database/features/oci/ic-faq-094177.html#A4428

              Instant Client FAQ

              • 4. Re: ORA-12504 error when connecting to Oracle 11G DB using OCI and instantclient-basic-nt-11.2.0.3.0.zip
                ddevienne

                910885 wrote:

                According to instant client FAQ & white paper at http://www.oracle.com/technetwork/database/features/instant-client/index.html, no tnsnames.ora is required for instant client. Then why could I be getting this error?

                 

                With Instant-Client, you indeed can just "login" with username, and password, but not hostname but a "service" name.

                 

                That service can be a TNS service name (you need to define a TNS_ADMIN and tnsnames.ora, etc...), or an EZCONNECT string of the form //host[:port]/dbservice

                 

                Always test your EZCONNECT string with SQL*Plus at the command line. When it works there, it will work in your app.

                 

                For SQL*Plus, the format is username[/password]@//host[:port]/dbservice

                 

                Note though that if you omit the password, you must surround the //host[:port]/dbservice part with \"...\" (the backslash is required, on Windows DOS and Unix Shells!).

                 

                For example:

                C:\Oracle\\Oracle\instantclient-12.1.0.1.0>sqlplus system@\"//acme:1521/pdb12c_1.foo.net\"

                 

                pdb12c_1.foo.net is the service name I used when I created my DB on the acme server. SQL*Plus prompts you for the password, and you're in .

                 

                In this example, provide //acme:1521/pdb12c_1.foo.net as the value for your host variable, replacing hostname and servicename with your actual values. But do test with SQL*Plus first. --DD