Solved

SQL Loader TNS File

Posted on 2006-07-12
4
3,772 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

729 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