Go Premium for a chance to win a PS4. Enter to Win


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
  • 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 .

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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 78

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" myemail@someserver.com

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month11 days, 17 hours left to enroll

916 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