Solved

SQL Loader TNS File

Posted on 2006-07-12
4
3,627 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.

Join & Write a Comment

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

747 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now