The error message means that one (or more) of the shared libraries required for TCP communications to work could not be loaded, perhaps due to o/s package updates? or it can mean that the /tmp/.oracle directory is not writeable by the oracle user (or doesn't exist perhaps because someone did some 'housekeeping' on /tmp . In either case tracing the listener at level 16 as Gaius suggested should reveal the issue.
As @ik_zelf mentioned, your PATH variable will need to include the "bin" directory of your Oracle software install. This will allow you to execute commonly-needed commands/utilities like "sqlplus" and "lsnrctl" without changing directories. This is typically $ORACLE_HOME/bin
Your TNS_ADMIN variable needs to be set in a similar matter, pointing to the location of your tnsnames.ora, listener.ora, etc. By default, SQL Plus checks this directory (defined by TNS_ADMIN) for tnsnames.ora. This is typically $ORACLE_HOME/network/admin
If you want to connect to your database via TNS, your tnsnames.ora file (assuming a ORACLE_SID of "myDatabase", a host of "myHostname" and the default port) would look something like this:
myDatabase =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myHostname)(PORT = 1521))
(CONNECT_DATA = (SERVICE_NAME = myDatabase)))
This would allow you to connect locally simply by doing the following from a command line:
sqlplus username/password@myDatabase
Of course, as SQL Plus looks for the tnsnames.ora file in the location defined by $TNS_ADMIN, this will only function locally. To access this database via TNS (Transparent Network Substrate) from a remote machine, you would have to install the Oracle client and ensure that the TNS entry for myDatabase (as described above) is included in the remote machine's tnsnames.ora file as well.
Best Answer
There's two routes to go through.
Firstly, Server-side. This is where a process on the machine hosting the DB will write the data to its file system using UTL_FILE. A DIRECTORY entry needs to be created in the database by a DBA pointing to a directory on the file system which can be written to by the OS process running the database (generally 'oracle')
Secondly, client-side. This is where a client program (eg SQL*Plus, Java, Python, Excel...) connects to the database, pulls the required data across to the client and writes it to a file on the client's file system.
You can have the client and server on the same machine, or a shared directory/file system that is accessible to both a client and server (one side needs to be able to read/write, the other can just be read).
You need to decide which best fits your situation, including