Solved

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

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

Through java/unix or by anyway.
0
Comment
Question by:sakthikumar
  • 6
  • 2
  • 2
  • +5
17 Comments
 

Author Comment

by:sakthikumar
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.
0
 
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 ?
0
 

Author Comment

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

if this is successful, we can implement into production .
0
 
LVL 73

Assisted Solution

by:sdstuber
sdstuber earned 250 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.
0
 
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 :)

Thanks,
0
 
LVL 23

Expert Comment

by:David
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
0
 

Author Comment

by:sakthikumar
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.

SDSTUBER / DVZ,

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

If Unix, can you give some sample shell scripting code,
like
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.
0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

Author Comment

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

Expert Comment

by:David
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).
0
 
LVL 10

Expert Comment

by:dbmullen
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:

declare
  count_me number;
begin
  select count(*) into count_me from owner.table@prod_db;
if something-you-care-about then
          send-email-stuff;
end if;
end;
/
0
 

Author Comment

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

DBAs use only in production.

EE ACCESS?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 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):
#/bin/sh

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

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

if [ "$ERROR_COUNT" -gt 1 ]; then
    mailx -s "Something bad happened in production" myemail@someserver.com
fi

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

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.
0
 
LVL 12

Expert Comment

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

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

Author Closing Comment

by:sakthikumar
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

760 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now