Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

For some of us whom frequently using MS Access in daily use, encountered an error might be very troublesome.

The problem is like this : I am using MS Access for data analysis and checking and have a connection over the network to Oracle database. My connection was using Microsoft ODBC Driver for Oracle. For some security reason, I need to change the password now. The password changed with no problem but encountered error ORA-12154 when trying to relink the tables.

Error as below:

ODBC--call failed
Microsoft ODBC driver for ORacle ORA-12154: TNS could not resolve service name

Generally, ORA-12154 indicated that the specifying TNS alias is not resolving. Few questions needed to clarify;

1. Are you using a DSN for your connection to Oracle database? 
2. If so, what is the TNS alias that you specifying? 
3. Does that alias exist in your tnsnames.ora file? 
4. Are there multiple tnsnames.ora files on your machine?

Your should have your tnsnames.ora file when you installing the Oracle client software/driver. If you wondering what is the version of the Oracle client you have installed, the simplest way is to get DOS command prompt and type 'tnsping [service_name]' where [service_name] is TNS alias specified in your DNS.

The ouput of it may look like this:

C:\Users\jcave>tnsping fuddy_duddy
TNS Ping Utility for 64-bit Windows: Version 11.2.0.1.0 - Production on 07-OCT-2
010 08:10:51
Copyright (c) 1997, 2010, Oracle.  All rights reserved.
Used parameter files:
C:\oracle\product\11.1.0\db_1\NETWORK\ADMIN\sqlnet.ora
TNS-03505: Failed to resolve name

The "Used parameter files" line shows you the directory where the TNS related files are stored. A tnsnames.ora and a sqlnet.ora file should be in that directory and it alwasys be there by default. Perhaps you should check the TNS alias name and confirm with your DNS setting.

How to load image files into BLOB column using PL/SQL

Assuming that you have a list of physical images needed to be loaded into Oracle database. And also you have a free text list of the files, perhaps in form of CSV file format.

From above, you may already get the bigger picture of the scenario which you need to execute.

Lets consider this task needed to be execute in Oracle using Oracle procedure.

The PL/SQL

I already prepared the PL/SQL example for this purpose. Check the code below;

CREATE OR REPLACE PROCEDURE PROC_LOAD_IMAGES (FILE_DIR in VARCHAR2 , MYFILE_NAME in VARCHAR2) AS
  -- FILE_DIR is the directory of the flat file and images
  -- MYFILE_NAME is  the flat file name
  F UTL_FILE.FILE_TYPE;
  V_LINE VARCHAR2 (1000);
  V_CACC_OLD_NUM  VARCHAR2(24 BYTE);
  V_IDENT_TYPE    NUMBER(1);
  V_ID_NUMBER     VARCHAR2(20 BYTE);
  V_FILE_NAME     VARCHAR2(80 BYTE);
  V_FILE_BLOB     BLOB;
  V_CLASS         VARCHAR2(1 BYTE);
  lFile           BFILE;
           
BEGIN
    F := UTL_FILE.FOPEN (upper(FILE_DIR), MYFILE_NAME, 'R');
    IF UTL_FILE.IS_OPEN(F) THEN
        LOOP
            BEGIN
            UTL_FILE.GET_LINE(F, V_LINE, 1000);
            IF V_LINE IS NULL THEN
            EXIT;
            END IF;
                --If using piping, change to '[^|]+'
                V_CACC_OLD_NUM := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 1);
                V_IDENT_TYPE := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 2);
                V_ID_NUMBER := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 3);
                V_FILE_NAME := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 4);
                V_CLASS := REGEXP_SUBSTR(V_LINE, '[^|]+', 1, 5);
             
                INSERT INTO BS_SIGNATURE_IMAGES (CACC_OLD_NUM, IDENT_TYPE, ID_NUMBER, FILE_NAME, CLASS) VALUES( V_CACC_OLD_NUM, V_IDENT_TYPE, V_ID_NUMBER, empty_blob(), V_CLASS) RETURNING FILE_NAME INTO V_FILE_BLOB;
                lFile := BFILENAME(upper(FILE_DIR), V_FILE_NAME);
             
                IF dbms_lob.fileexists(lFile) = 1 THEN      
                    DBMS_LOB.OPEN(lFile, DBMS_LOB.LOB_READONLY);
                    DBMS_LOB.OPEN(V_FILE_BLOB, DBMS_LOB.LOB_READWRITE);
                    DBMS_LOB.LOADFROMFILE(DEST_LOB => V_FILE_BLOB,
                                          SRC_LOB  => lFile,
                                          AMOUNT   => DBMS_LOB.GETLENGTH(lFile));
                    DBMS_LOB.CLOSE(lFile);
                    DBMS_LOB.CLOSE(V_FILE_BLOB);
                END IF;
             
                COMMIT;
                             
            EXIT;
            END;
        END LOOP;
    END IF;
    UTL_FILE.FCLOSE(F);
END;
/

The important portion UTL_FILE function. This is the function to read the file and responsible to load the files into BLOB format into the database.