asked on

connecting to a remote oracle database using pro*C

Hi guys,
I am having trouble with connecting to a remote oracle database using pro*c.
Currently my linux machine(say A) has pro*c installed and oracle installed. so im connecting with a string that looks something like this

exec sql connect :username IDENTIFIED BY :password;

Now i have to shift my code to other linux system(say B). It has pro*C and oracle installed.
BUT i dont want to use the oracle that is installed in this (B)system, but i want to use
the oracle installed in the other system(A). i guess i need to use a remote database connection string,

In the internet i found some connection string like,

EXEC SQL CONNECT :userid IDENTIFIED BY :password at :db_name using :db_string;

can you please tell me whether this would work and if yes tell me what the 'at' parameter and 'using' parameter is for ?

I am wondering where we have to give the machine name, database name, protocol to use to connect , port etc..etc..
the machine name on which im working is newdelhi.uta.edu and im trying to connect to the remote database in berlin.uta.edu, the database name in berlin.uta.edu is called 'webvigil'.

please give me a solution

ASKER CERTIFIED SOLUTION

catchmeifuwant

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

catchmeifuwant

Here's some information on using it
--------------------------------------

In the following example, you connect to a single nondefault database at a remote node:

/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_string[20] = "NYNON";

/* give the database connection a unique name */
EXEC SQL DECLARE DB_NAME DATABASE;

/* connect to the nondefault database */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT DB_NAME USING :db_string;

The identifiers in this example serve the following purposes:

The host variables username and password identify a valid user.
The host variable db_string contains the Oracle Net syntax for connecting to a nondefault database at a remote node.
The undeclared identifier DB_NAME names a nondefault connection; it is an identifier used by Oracle, not a host or program variable.
The USING clause specifies the network, machine, and database associated with DB_NAME. Later, SQL statements using the AT clause (with DB_NAME) are executed at the database specified by db_string.

Alternatively, you can use a character host variable in the AT clause, as the following example shows:

/* declare needed host variables */
char username[10] = "scott";
char password[10] = "tiger";
char db_name[10] = "oracle1";
char db_string[20] = "NYNON";

/* connect to the nondefault database using db_name */
EXEC SQL CONNECT :username IDENTIFIED BY :password
AT :db_name USING :db_string;
...

If db_name is a host variable, the DECLARE DATABASE statement is not needed. Only if DB_NAME is an undeclared identifier must you execute a DECLARE DB_NAME DATABASE statement before executing a CONNECT ... AT DB_NAME statement.

SQL Operations
If granted the privilege, you can execute any SQL data manipulation statement at the nondefault connection. For example, you might execute the following sequence of statements:

EXEC SQL AT DB_NAME SELECT ...
EXEC SQL AT DB_NAME INSERT ...
EXEC SQL AT DB_NAME UPDATE ...

annamalai77

hi there

well what u can do is create a connect string in the linux machine b which connects to the remote database . i assume that u r in a local network.

then using the connect string u can connect to the database

EXEC SQL CONNECT :username@connect_string IDENTIFIED BY :password

or as said earlier by our friend catchmeifuwant.

otherwise the other way arround is u can create a public database link . but that will require u to change ur queries with the from clause having db links associated with all the tables.

regards
annamalai

Alok Ranjan Nayak

Here is how i am doing

char *myPassword="mypasswd123";
char *connstr1="alokjms@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.14.67.190)(PORT=1521)))(CONNECT_DATA=(SERVICE_NAME=mydbsrv)))'";

EXEC SQL CONNECT :connstr1 IDENTIFIED BY :myPassword; ;

and i am getting below error

SQL ERROR:
ORA-01012: not logged on (1012)