9 Replies Latest reply on Jan 4, 2018 5:31 PM by rp0428

    Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-

    3611866

      Hi All,

       

      Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-

       

      Query :-  SELECT COUNT(1) FROM omnix.ngi_antenna_v@nc_mbn_omnix_prod;

       

      Error :- 

       

       

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

      12154. 00000 -  "TNS:could not resolve the connect identifier specified"

      *Cause:    A connection to a database or other service was requested using

                 a connect identifier, and the connect identifier specified could not

                 be resolved into a connect descriptor using one of the naming methods

                 configured. For example, if the type of connect identifier used was a

                 net service name then the net service name could not be found in a

                 naming method repository, or the repository could not be

                 located or reached.

      *Action:  

                 - If you are using local naming (TNSNAMES.ORA file):

                 - Make sure that "TNSNAMES" is listed as one of the values of the

                 NAMES.DIRECTORY_PATH parameter in the Oracle Net profile

                 (SQLNET.ORA)

                 - Verify that a TNSNAMES.ORA file exists and is in the proper

                 directory and is accessible.

                 - Check that the net service name used as the connect identifier

                 exists in the TNSNAMES.ORA file.

                 - Make sure there are no syntax errors anywhere in the TNSNAMES.ORA

                 file.  Look for unmatched parentheses or stray characters. Errors

                 in a TNSNAMES.ORA file may make it unusable.

                 - If you are using directory naming:

                 - Verify that "LDAP" is listed as one of the values of the

                 NAMES.DIRETORY_PATH parameter in the Oracle Net profile

                 (SQLNET.ORA).

                 - Verify that the LDAP directory server is up and that it is

                 accessible.

                 - Verify that the net service name or database name used as the

                 connect identifier is configured in the directory.

                 - Verify that the default context being used is correct by

                 specifying a fully qualified net service name or a full LDAP DN

                 as the connect identifier

                 - If you are using easy connect naming:

                 - Verify that "EZCONNECT" is listed as one of the values of the

                 NAMES.DIRETORY_PATH parameter in the Oracle Net profile

                 (SQLNET.ORA).

                 - Make sure the host, port and service name specified

                 are correct.

                 - Try enclosing the connect identifier in quote marks.

      Error at Line: 1 Column: 42

       

      Please let me know if anyone know the solution for this. it would be great help.

        • 1. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
          3611866

          Please reply anyone i am waiting for solution this is very urgent please

          • 2. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
            Oyvind Isene

            This error has probably nothing to do with SQL Developer. Are you sure you are connected to the same database instance? The error says that the Client cannot resolve "nc_mbn_omnix_prod". Since the Client in this case is the database instance you are Connected to it will use the same TNSNAMES.ORA file independent of if you are Connected With SQL Plus or other SQL Developer.

             

            select * from all_db_links;

            Does this SQL give you the same output from SQL Plus and SQL Developer?

             

            (Sorry for random init caps, something funny With this editor or my web browser).

            • 3. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
              3611866

              Thanks Oyvind for the reply.

               

              I just checked  this query on both sqlplus and sqldeveloper select * from all_db_links;

               

              Its giving the same output on both. I unable to find the correct this issue.

               

              SQL> select * from all_db_links;

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              PUBLIC

              DBLINK_VIEW_NC_OMNIX

              NC_OMNIX_VIEW_USER

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              10.11.118.27:1521/OMNIXPRD

              10-AUG-16

               

               

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              PUBLIC

              NC_MBN_OMNIX_PROD

              NGI

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              OMNIXPRD

              10-AUG-16

               

               

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              MNIL_PROD

              NC_MBN_OMNIX.MOBINIL.CORP

              NGI

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.11.105.49 )

              (PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = omnix)))

              24-JAN-16

               

               

              OWNER

              ------------------------------

              DB_LINK

              --------------------------------------------------------------------------------

              USERNAME

              ------------------------------

              HOST

              --------------------------------------------------------------------------------

              CREATED

              ------------------

              • 5. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
                3611866

                This is very urgent issue i need to fix this ASAP. could anyone please help on this issue.

                • 6. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
                  EdStevens

                  dd671f86-3db9-40e1-b234-39faa82364e0 wrote:

                   

                  Please reply anyone i am waiting for solution this is very urgent please

                  =================================

                   

                   

                  This forum is not a chat line, and it is not paid support.

                   

                  Everyone here has a job for which they are paid, and this forum is not it.

                   

                  No one is responsible for monitoring it and giving a quick response. 

                   

                  Furthermore, it is a global forum.  The person with the information you seek may very well live 20 time zones away from you and was going to bed just as you posted. He will not even see your post for several more hours. 

                   

                  Your original post went up in the middle of the night for half the world.

                   

                  No one with the information you seek is deliberately withholding it until you sound sufficiently desperate.

                   

                  Second, because of my first point, there is no "urgent" here. 

                  "Urgent" means one of two things -

                   

                  1) people's live are at stake, or

                   

                  2) you have a customer-facing, revenue-producing production system that is down. 

                  (And to get some perspective on the second case, keep the first in mind.)

                   

                  For the first, you call whatever civil emergency service seems appropriate. 

                   

                  For the second, you open an SR with Oracle - which requires a paid-up support contract. For *them* to consider your problem "urgent", you will need to demonstrate that your problem falls under item #2. I seriously doubt your problem fits that criteria.

                   

                  You don't have a support contract?  Now you know the exact dollar figure that your company places on the data. 

                  ====

                  Now, to your immediate problem.

                   

                  Please re-read Oyvind Isene - msg 2.    I'd be willing to bet that you are not connecting to the db you think your are, thus the db link is not using the tnsnames.ora you think it is.

                  Show us the results of the following, from both sqlplus and SQL Dev

                   

                  select instance_name, host_name from v$instance;

                  • 7. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
                    3611866

                    I already check this and this is same result on both

                     

                    INSTANCE_NAME

                    ----------------

                    HOST_NAME

                    ----------------------------------------------------------------

                    NCTST

                    nctest

                     

                    I know this whatever you written instead of writing these long mail you should think about the solution. I am not forcing to anyone to give the answer. this is open discussion form and whoever know the answer that person will reply.

                    • 8. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-
                      3611866

                      Is anybody face similar kind of issue. i checked all possible solution but non of them is working.

                      • 9. Re: Sql query is working fine in sqlplus command prompt but when i running through sqldeveloper then getting the following error-

                        I know this whatever you written instead of writing these long mail you should think about the solution. I am not forcing to anyone to give the answer. this is open discussion form and whoever know the answer that person will reply.

                        Yes - this is an open discussion forum.

                         

                        And it is everyone's responsibility, including yours, to follow the forum guidelines when posting.

                         

                        Please review ALL of the guidelines and, if you decide to continue posting, commit to following them:

                        Community Posting Etiquette

                         

                        Do not mark your question as urgent or ASAP.