Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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

Posted on 2014-03-13
Medium Priority
Last Modified: 2014-03-29
Is there a way I can query remote DB without using remote DB.?

Through java/unix or by anyway.
Question by:sakthikumar
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
  • 6
  • 2
  • 2
  • +5

Author Comment

ID: 39925746
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.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39925763
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 ?

Author Comment

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

if this is successful, we can implement into production .
The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

LVL 74

Assisted Solution

sdstuber earned 1000 total points
ID: 39926077
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.
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39926084
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 :)

LVL 23

Expert Comment

ID: 39926229
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

Author Comment

ID: 39932161
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.

Author Comment

ID: 39932163
This would be of great help to me.
LVL 23

Expert Comment

ID: 39932636
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).
LVL 10

Expert Comment

ID: 39932641
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;

Author Comment

ID: 39937473
I have a query, if it retrieves any record from production, then it should create an alert.

DBAs use only in production.

LVL 77

Accepted Solution

slightwv (䄆 Netminder) earned 1000 total points
ID: 39958011
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.
LVL 12

Expert Comment

by:Sharon Seth
ID: 39958281
The obvious question , can you even access prod db directly outside a prod env ?
LVL 35

Expert Comment

by:Gary Patterson
ID: 39959238
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.

Author Closing Comment

ID: 39964574
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.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

715 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