Re: database calls out to ineternet

There are certain things you can do with LOBs over links. INSERT ... SELECT works just fine, and so does a plain SELECT .. INTO. If you don't want to store the data permanently on your web database, you could use a global temporary table that removes the data on commit.

So the workflow would be:

• call remote procedure to populate the temp table
• fetch the data and insert into a local table
• commit, purging the remote data

If you're planning on adding additional services and things, setting up a proper environment with APEX could be worth the trouble, though.

On Fri, May 4, 2018 at 3:07 AM, Jeff Chirco <backseatdba_at_gmail.com> wrote:

> 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 <knecht.stefan_at_gmail.com>
> 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 <backseatdba_at_gmail.com>
>> 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 zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/
>>
>
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | _at_zztat_oracle | fb.me/zztat | zztat.net/blog/

--
http://www.freelists.org/webpage/oracle-l