Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Loader TNS File

Posted on 2006-07-12
4
Medium Priority
?
3,898 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 1000 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

618 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