Is there a way I can query remote DB without using remote DB.?

Is there a way I can query remote DB without using remote DB.?

Through java/unix or by anyway.
Who is Participating?

Improve company productivity with a Business Account.Sign Up

slightwv (䄆 Netminder)Connect With a Mentor Commented:
Responding to the neglected question alert.

Where does the dev database come into question?

Just do what sdstuber suggested but without the dev database.

Create a shell script on any machine that uses sqlplus to connect to prod, run some queries, check the results and use mailx to send an alert.

Something like (just typed in...  No Unix to test with):

sqlplus username/password@prod_database > /tmp/junk.log  << EOF
    select case when count(*) = 1 then 'ERROR' end from dual;

ERROR_COUNT=`grep -c ERROR /tmp/junk.log`

if [ "$ERROR_COUNT" -gt 1 ]; then
    mailx -s "Something bad happened in production"

Open in new window

You can add as many selects from wherever you want.  Just make sure whatever code you write adds a distinct string like 'ERROR' in the output for grep to count.

I suppose you 'could' send the emai from the dev database with another quick sqlplus call that connects to the dev database but why?

somethig like this using UTL_MAIL with all the correct parameters:
sqlplus devuser/devpassword@devdatabase << EOF
exec utl_mail ...

Open in new window

All that said:
I have a stored procedure in my production databases that checks everything I deem important and the prod database itself emails me every morning.  In the procedure the subject line of the email changes from OK to PROBLEM if something is hinky.  If I see 'OK', I never have to read the email...

No email, one of two things:
-Database is down (or has major problems).
-Email server is down.
sakthikumarAuthor Commented:
I am planning to query production db, and based on the results I want to alert users by sending email from dev. db.

But, my dbas are not allowing me to create db links. Is there anyother way i can accomplish this.
Naveen KumarProduction Manager / Application Support ManagerCommented:
you can send alerts from the production database itself after checking/verifying right. Why do you need to get the results into the dev database ?
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

sakthikumarAuthor Commented:
anything if i am changing deploying in prod db, need lot of approvals.

if this is successful, we can implement into production .
sdstuberConnect With a Mentor Commented:
What you are describing is a production process running out of a non-production environment.

So, yes, it's certainly possible using a java stored procedure with a jdbc connection to your production database; but that seems ill advised.  If your notifications are important they should be part of production.  What if your development database goes down?

If you're going to use java, you don't even need the dev database at all.  Just write a java program that queries your production database and sends emails.    It could run on a production app server.  Similarly you could write a unix shell script that uses sqlplus to query data and then mail the results  using cron on a production server.

So, yes, it's possible to do within development; but you probably have other production systems that could be better suited.
Naveen KumarProduction Manager / Application Support ManagerCommented:
my view is also more less in the lines of sdstuber here....

Do not connect the prod environments/databases to the DEV/UAT databases by whatever means it is with db links, executables developed in c or pro*c or whatever programming language as there is always risk which comes with it for the production environment.

The best thing to do here is to follow the process though it takes time & approvals as that would be the long term/strategic way of putting in things which would help even after we move out of roles & responsibilites over time :)

DavidSenior Oracle Database AdministratorCommented:
I'd like to hear more about your actual goal, to notify people about certain result sets.  There are "best-practice" techniques already available, subject of course to your database version.  For instance, email may be sent after:

Test for some Boolean outcome (statement was successful/unsuccessful)
For some threshold or event (storage is filling up)
Results from a scheduled job
sakthikumarAuthor Commented:
This option is needed for developers(support team) only.
Just to have an alert when there is some problem.
This is not of high importance, but having something like this will be very useful.


Please tell me which technique we can use to accomplish this.

If Unix, can you give some sample shell scripting code,
1. How to connect to prod. db, execute the query fetch the results.
2. Connect back to dev. db and execute the procedure(already available)
     to send email with the results.
sakthikumarAuthor Commented:
This would be of great help to me.
DavidSenior Oracle Database AdministratorCommented:
Provide a sample statement (on what condition / event do you want to send a message); which O/S and version?  

Are your DBAs using Enterprise Manager? In all environments or just prod?  Does your shop allow Developers EE access (should).
I'm not sure I understand, I'm assuming you have a way to "deploy" code into production.  Fill out the forms, get the approvals, and deploy you code/process into production.  seems pretty basic.

regardless, if your non-prod and prod username/password match, you could do something like this connected to non-prod:

  count_me number;
  select count(*) into count_me from owner.table@prod_db;
if something-you-care-about then
end if;
sakthikumarAuthor Commented:
I have a query, if it retrieves any record from production, then it should create an alert.

DBAs use only in production.

Sharon SethCommented:
The obvious question , can you even access prod db directly outside a prod env ?
Gary PattersonVP Technology / Senior Consultant Commented:
Here is the short answer:  In order to query the prod database, you have to establish a connection to that database.  There are a lot of ways to establish a connection, but all of them require that you have access to a profile in prod with adequate rights to run the query.

You've already indicated that your DBAs won't allow you to create a link in the dev DB to prod.  That isn't surprising.

You have two basic categories of options:

1) Local access from the prod system.  Do you have access to the prod system?  Once logged on to the prod system, do you have adequate credentials to run queries on the prod database?  If so, then the best approach is to set up a scheduled job that runs the needed query and sends the email - all right from the prod system.

2) Remote access to prod system.  Do you have the ability to access the prod database remotely from dev (or any other system)?  If so, you could use sqlplus, or ODBC, JDBC, DRDA.

Hard to make suggestions without knowing what rights you have and what connection methods are an option.
sakthikumarAuthor Commented:
Thanks  slightwv,

I am very poor in Unix, I am not able accomplish this,

Thats the reason I want someone to help me in this pinch of code.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.