Solved

SQL Loader TNS File

Posted on 2006-07-12
4
3,703 Views
Last Modified: 2008-02-01
Using SQL Loader you pass in the username and password and then the reference to the TNS database name. Is there a way though to just pass in real database name so I don't need to reference the TNS file?

For those of you who think I am doing something crazy, or want to waste a post saying it can't be done read on :)



Reason:
For those that are interested. I am distributing the oracle client tools with my .net application to a friend. I don't want him to have to configure the tns file though.

Background:
Before you say it can't be done :) In .net when you are creating a client connection to an oracle database you normally have to provide the tns short name. But as oracle points out here, you can in fact provide the database params directly instead of the tns filename.

FROM: http://www.oracle.com/technology/pub/articles/cook_dotnet.html 
"To use the OraDb alias defined in the tnsnames.ora file shown above, you would use the following syntax:
Dim oradb As String = "Data Source=OraDb;User Id=scott;Password=tiger;" ' VB.NET
string oradb = "Data Source=OraDb;User Id=scott;Password=tiger;"; // C#
You can modify the connection string to obviate the need for the tnsnames.ora file, however. Simply replace the name of the alias with how it would be defined in a tnsnames.ora file.

' VB.NET
Dim oradb As String = "Data Source=(DESCRIPTION=" _
           + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521)))" _
           + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));" _
           + "User Id=scott;Password=tiger;"

string oradb = "Data Source=(DESCRIPTION="              // C#
             + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=OTNSRVR)(PORT=1521)))"
             + "(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=ORCL)));"
             + "User Id=scott;Password=tiger;";"



And yes I did try: sqlldr.exe username:password@(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)))(CONNECT_DATA =(SID = SERVERSTUFF)(SERVER = DEDICATED)))

Thanks for the help!!
0
Comment
Question by:toby_lang
  • 2
4 Comments
 
LVL 14

Expert Comment

by:sathyagiri
ID: 17095392
Just wondering if it wd be easier for you to just supply the tnsnames.ora and ask your friend to just place it under the network\admin foler
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17095495
0
 
LVL 14

Accepted Solution

by:
sathyagiri earned 250 total points
ID: 17095566
Create a parameter file like this (say parfile.txt)

userid=<userid>@"(DESCRIPTION =(ADDRESS_LIST =(ADDRESS = (PROTOCOL = TCP)(HOST = SERVER)(PORT = 1521)))(CONNECT_DATA =(SID = SERVERSTUFF)(SERVER = DEDICATED)))"/<password>
control=<controlfile>

Now invoke your sqlldr using

sqlldr parfile=<c:\parfile.txt>

This should work. Tried it out worked for me.
0
 
LVL 16

Expert Comment

by:MohanKNair
ID: 17097520
There are many ways to connect to Oracle database like ODBC, JDBC thin client or using third party drivers. But sqlloader uses SQL*NET to connect to Oracle and you need to configure tnsnames file. By setting the environment variable TWO_TASK it is not required to specify TNS alias while connecting.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question