4 Replies Latest reply on May 10, 2021 8:52 AM by Karthick M

    Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...

    sri gar Active Member

      Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...and send emails ( Failure emails ) if the connection is not successful.


      I am thinking of creating a reusable command task to ping Oracle instance ( tnsnames ) which is on Unix server jus tlike we ping from Toad to see connectivity success.  and get the ping result. Can any one help me unix ping command

      which include Connection name, service name , username , password . I see the below unix command might be useful but I dont see the username( should be  infa service account ) and password.



      tnsping connection/servicename

        • 1. Re: Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...
          Syed Aziz Guru

          Hello Sri,


          If your PowerCenter repository is on database host1.domain.com, please use the below format



          Please confirm the SID by tnsping host1 from command prompt


          Also, the configuration needs to be confirmed from the Oracle client side.  I think the tnsping command shows the most useful information here.  If tnsping is successful, it will show as fine.  The tnsping shows where the connection information is picked up from, in this case /home/oracle/apps/oracle/product/ You can use this same technique when it fails to determine which tnsnames.ora file is being used that would refer to the invalid Oracle server.


          Best regards,


          • 2. Re: Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...
            Smitha HC Guru

            Hi Sri,


            You can aslo use SQLplus connect command, where you can specify the username,password and Connection string. Refer below link.


            SQL*Plus Quick Start




            • 3. Re: Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...
              Nico Heinze Guru

              To clarify a few details before they get messed up badly:

              TNSPING does not require a user name and a password. So using tnsping to test the IP connection to an Oracle database is a good idea.

              Using SQL*PLUS requires a user name and a password (assuming you don't use Oracle wallets).

              Using SQL*PLUS in a command task requires you (except with a wallet) to store user name and password in plain text somewhere (e.g. within the command itself). This probably will be a bad security breach. So that's an approach I usually don't recommend: everyone who has read access to the workflow can (ab-)use this user name and password for whatever purpose. Your security officers may want to kill you for that.


              ping is a network command. It helps in (almost) no way when you are trying to identify connection problems to some Oracle database.


              BTW why do you want to use a command task to check an Oracle connection?

              You can perform the same action within a fairly simple PowerCenter mapping. You just need some dummy source delivering one record only; then use a SQL transformation to perform a "SELECT 2 AS two FROM DUAL" statement; the SQL transformation just needs one output port of type Integer named "two", and it will have a SQLError output port.

              If the connection to the database works, then SQLError will be NULL. In any other case, SQLError will contain some error message. So you can easily distinguish between a successful and a failed attempt to connect using this SQLError output port.

              And this approach has the additional advantage that neither any user name nor any password must be exposed in any way because these details are stored within the PowerCenter connection you use for the SQL transformation.


              Does that help?




              • 4. Re: Create reusable wOrkflow to check/ping the database (Oracle ) connectivity...
                Karthick M New Member

                Hello Sri,


                Please use the below SQLPLUS command to check the connectivity from your Linux box.

                $ sqlplus <username>/<password>@connection_string