Solved

SQL Loader TNS File

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

Suggested Solutions

Title # Comments Views Activity
make null the repeated levels 2 48
update using pipeline function 3 44
setting local variables in a cursor block 3 41
ORA-01843: not a valid month - use NVL 14 15
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
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…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

739 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