Re: database calls out to ineternet

From: Jeff Chirco <>
Date: Thu, 3 May 2018 13:07:08 -0700
Message-ID: <>

Thanks Stefan. Yeah that is basically the process we were thinking. The problem I have is the output over a database link. We are getting back a json object from the web service and we want the full object and it is not possible to pass a clob/blob over a db link. We don't want to save the info into a table on the remote side because this data is sensitive and needs encryption and the remote database SE. So we would have to get creative in how to pass it back to the main database.

My other possible idea is too standup Apex/Ords on the remote database and have it all the external web service and then just return the straight json it is own web service. So it would be the remote database calling a web service and return the data via the local ords web service. We have never used Apex or ORDS so there would be a learning curve and environment to get going.

On Wed, May 2, 2018 at 9:58 PM, Stefan Knecht <> wrote:

> If you have full control over the environment (it sounds like you do) and
> security is the main focus, you can create something like this:
> - In your main database, create a dedicated user that will own a private
> database link
> - In your new web-enabled database, create a dedicated user that will own
> a stored procedure or package that you call via the database link
> - The database link will exist between these two new users
> - In your main database, you can then create a stored procedure or package
> that calls the remote procedure
> - Whereever you need that data, you then call the local procedure.
> Pseudo code it would look like this:
> create or replace procedure get_data
> as
> output varchar2(1000);
> begin
> execute immediate 'get_data_at_dblink(arg1, arg2,arg3,out)' using l_arg1,
> l_arg2, l_arg3, output;
> -- process data received from web call
> end;
> /
> With proper exception handling in place, this should be fairly secure.
> Your "local" procedure needs to handle the errors related to the database
> link, whereas your remote procedure should handle the exceptions that can
> occur during the call that goes out to the web.
> Stefan
> On Thu, May 3, 2018 at 4:57 AM, Jeff Chirco <> wrote:
>> We have a requirement to make a web service call out to the internet. We
>> want to do this from within pl/sql. We've review the code to do that and
>> that is no a problem. The problem is our security admin doesn't want it to
>> our main database server to make the call and do the processing and is
>> asking that we have a separate database a different server to make the call
>> and do the processing of the return data. This seems overly complicated of
>> a process because we have to call a diff database, processes it there and
>> then it will need to send the results back. versus doing it all in one
>> database.
>> Anybody run into similar issue with your network team or how do you
>> handle external calls? We are an private company and extermley private
>> with our data
>> Jeff
> --
> //
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
> Visit us at | _at_zztat_oracle | |

Received on Thu May 03 2018 - 22:07:08 CEST

Original text of this message