Home » Developer & Programmer » Precompilers, OCI & OCCI » Ora-12154 when working with Pro*fortran. (Oracle 9i, v9.2.0.6, OS -- AIX 5.3)
Ora-12154 when working with Pro*fortran. [message #375800] Sun, 14 December 2008 21:41 Go to next message
nitintz
Messages: 5
Registered: December 2008
Junior Member
Hi All,
when i try to include a select statement in my pro*fortran code
I am getting Ora-12154 TNS could not resolve service name

The select query is given below.

EXEC SQL SELECT PERIOD_NAME, UBR, UER
1 INTO :ENAME, :SAL, :COMM
2 FROM TEST

I am using the 32bit version of libraries and the compilation is happening without any problem.
Earlier when i tried with 64 bit libraries i was getting errors during compilation.

Note: I am not getting any errors when I run only the conect statement.
Re: Ora-12154 when working with Pro*fortran. [message #375817 is a reply to message #375800] Mon, 15 December 2008 00:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA-12154: 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.

   See the Oracle Net Services Administrators Guide or the Oracle
   operating system specific guide for more information on naming.

Regards
Michel
Re: Ora-12154 when working with Pro*fortran. [message #375823 is a reply to message #375817] Mon, 15 December 2008 00:58 Go to previous messageGo to next message
nitintz
Messages: 5
Registered: December 2008
Junior Member
Hi,
Sorry I forgot to mention earlier that I am able to connect to the database through Sql*plus using the same connect string as in pro*fortran.
Also i am not getting this error when just connecting from pro*fortran, But error is seen when I try to do any select or insert statement in the code

Regards
Nitin

[Updated on: Mon, 15 December 2008 01:01]

Report message to a moderator

Re: Ora-12154 when working with Pro*fortran. [message #375827 is a reply to message #375823] Mon, 15 December 2008 01:17 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have not the same TNS environment for both program. That is you don't point to the same tnsnames.ora and/or sqlnet.ora
Check any TNS_ADMIN variable, check if ORACLE_HOME is set in the same way...

Regards
Michel

[Updated on: Mon, 15 December 2008 01:18]

Report message to a moderator

Re: Ora-12154 when working with Pro*fortran. [message #375835 is a reply to message #375827] Mon, 15 December 2008 02:07 Go to previous messageGo to next message
nitintz
Messages: 5
Registered: December 2008
Junior Member
Hi,
I have the same env settings (like TNS_ADMIN, ORACLE_HOME, etc)for both program.
Infact when i run from the same command prompt i can run sql*plus without any problem, but with pro*fortran I get ora-12154 error.
Re: Ora-12154 when working with Pro*fortran. [message #375841 is a reply to message #375835] Mon, 15 December 2008 02:25 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
When you connect through sqlplus, do you use the same service name as hard coded into your Fortran program?

PS: If would also help if you can post the code, tnsping output, output of a connect to sqlplus, etc.
Re: Ora-12154 when working with Pro*fortran. [message #375848 is a reply to message #375841] Mon, 15 December 2008 02:57 Go to previous messageGo to next message
nitintz
Messages: 5
Registered: December 2008
Junior Member
the code is given below
      PROGRAM QUERY
      IMPLICIT NONE
      EXEC SQL BEGIN DECLARE SECTION
          CHARACTER*20   UIDPWD
          CHARACTER*20   ENAME
          INTEGER        SAL
          INTEGER        COMM
          INTEGER        I
          DIMENSION      ENAME(4)
          DIMENSION      SAL(4)
          DIMENSION      COMM(4)
      EXEC SQL END DECLARE SECTION

      EXEC SQL INCLUDE SQLCA
      EXEC SQL WHENEVER SQLERROR DO CALL SQLERR

*     LOG ON TO ORACLE.
      UIDPWD='DEVELOPMENT/DEV@DEV'
*      EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
      EXEC SQL CONNECT :UIDPWD
*      EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
      PRINT *,'CONNECTED TO ORACLE'

      I=1
*      EXEC SQL WHENEVER NOT FOUND GOTO 7400
*      EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
      EXEC SQL SELECT PERIOD_NAME, UBR, UER
     1    INTO :ENAME, :SAL, :COMM
     2    FROM TEST
*     3    WHERE UBR=1000
*      EXEC SQL WHENEVER SQLERROR DO CALL SQLERR

 7400 END

      SUBROUTINE SIGNOFF (NUMQ)
      INTEGER NUMQ
      EXEC SQL INCLUDE SQLCA
      PRINT 8000, NUMQ
 8000 FORMAT (/, ' TOTAL NUMBER QUERIED WAS: ', I4)
      PRINT 8200
 8200 FORMAT (/, ' HAVE A GOOD DAY.', /)
      EXEC SQL COMMIT WORK RELEASE
      STOP
      END

      SUBROUTINE SQLERR
      EXEC SQL INCLUDE SQLCA
      EXEC SQL WHENEVER SQLERROR CONTINUE
      PRINT 9000
 9000 FORMAT (/, ' ORACLE ERROR DETECTED:  ')
      PRINT *, SQLEMC
* 9200 FORMAT (' ', A70)
      EXEC SQL ROLLBACK WORK RELEASE
      STOP
      END


The TNSPING output is
$ tnsping dev

TNS Ping Utility for IBM/AIX RISC System/6000: Version 9.2.0.6.0 - Production on
 15-DEC-2008 13:27:49

Copyright (c) 1997 Oracle Corporation.  All rights reserved.

Used parameter files:
/erp/oracle/devdb/9.2.0/network/admin/DEV_qdevelop/sqlnet_ifile.ora

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION= (ADDRESS=(PROTOCOL=tcp)(HOST=qdevelop.quest-
global.com)(PORT=1522)) (CONNECT_DATA= (SID=DEV)))
OK (1270 msec)
$


The SQL*plus connect output is
$ sqlplus /nolog

SQL*Plus: Release 9.2.0.6.0 - Production on Mon Dec 15 13:29:38 2008

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

SQL> conn development/dev@dev
Connected.
SQL>


Re: Ora-12154 when working with Pro*fortran. [message #376393 is a reply to message #375848] Wed, 17 December 2008 03:44 Go to previous messageGo to next message
Frank Naude
Messages: 4579
Registered: April 1998
Senior Member
Everything looks perfectly fine to me. Something else you can try is to remove the "@DEV" part from the connect string to see if you can make a local connection.

PS: Is Pro*Fortran still supported? If so, you can always log a call with Oracle Support.
Re: Ora-12154 when working with Pro*fortran. [message #376568 is a reply to message #376393] Wed, 17 December 2008 21:19 Go to previous message
nitintz
Messages: 5
Registered: December 2008
Junior Member
HI,

I am not able to connect if I revome the connect string in the profor code, but connection is happening from sqlplus prompt.
And I have no idea about the support, as a new bee I was just trying to get this connection going and then worrry about the support.

Just observed that when the dimension of array ENAME is increased (to > number of rows in select statement) I got a new error
ORA-02112: SELECT into returns too many rows.

So I changed the code to something like this,
changes are added :ENAME(I) instead of ENAME earlier

and now I get the following error
ORA-00905: Missing keyword

The new code looks like this.

PROGRAM QUERY
IMPLICIT NONE
EXEC SQL BEGIN DECLARE SECTION
CHARACTER*20 UIDPWD
CHARACTER*20 ENAME
DIMENSION ENAME(5)
INTEGER J
EXEC SQL END DECLARE SECTION

EXEC SQL INCLUDE SQLCA
EXEC SQL WHENEVER SQLERROR DO CALL SQLERR

* LOG ON TO ORACLE.
UIDPWD='DEVELOPMENT/DEV@DEV'
* EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
EXEC SQL CONNECT :UIDPWD
* EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
PRINT *,'CONNECTED TO ORACLE'

* EXEC SQL WHENEVER NOT FOUND GOTO 7400
* EXEC SQL WHENEVER SQLERROR DO CALL SQLERR
EXEC SQL SELECT PERIOD_NAME
1 INTO :ENAME
2 FROM DEVELOPMENT.TEST
* 3 WHERE UBR=1000
DO 200 J= 1,4
PRINT *, ENAME(J)
200 CONTINUE
STOP
7400 END

SUBROUTINE SQLERR
EXEC SQL INCLUDE SQLCA
EXEC SQL WHENEVER SQLERROR CONTINUE
PRINT 9000
9000 FORMAT (/, ' ORACLE ERROR DETECTED: ')
PRINT *, SQLEMC
* 9200 FORMAT (' ', A70)
EXEC SQL ROLLBACK WORK RELEASE
STOP
END



Somebody Please help to resolve this problem.
Thanks in advance
Nitin

[Updated on: Thu, 18 December 2008 02:52]

Report message to a moderator

Previous Topic: Unable to Insert XML data into XMLType Table :-(
Next Topic: PCC-S-02014 error while compiling with in Sun Solaris
Goto Forum:
  


Current Time: Thu Mar 28 05:10:27 CDT 2024