Re: JVM in the database

From: Stefan Knecht <knecht.stefan_at_gmail.com>
Date: Thu, 12 Dec 2019 18:22:47 +0700
Message-ID: <CAP50yQ-Qq8TZeWTgYXJN2zr90eq8reHzVZH4R8cOZ94E38=w7g_at_mail.gmail.com>



 You can actually do it in pure PL/SQL nowadays:

function                x$krbmsft               ( path in varchar2 ) return
foo.zz$krbmsft_tt
pipelined
is
  l_path                        varchar2(1024)          := path || '/';
  l_ns                          varchar2(1024);
  l_files                       foo.zz$krbmsft_tt;
begin

  info(13, 'Searching for files in directory ' || l_path || '..');   sys.dbms_backup_restore.searchfiles(l_path, l_ns);

  select foo.zz$krbmsft_t(fname_krbmsft, size_krbmsft, stamp_krbmsft)     bulk collect into l_files
    from x$krbmsft;

  info(13, 'Returning list of ' || l_files.count || ' files..');

  for i in 1..l_files.count loop
-- info(13, 'Returning file ' || l_files(i).filename);

    pipe row(l_files(i));
  end loop;
  info(13, 'Complete.');
end;

Taken out of a package, so you may need to fiddle a bit to make it run standalone.

Of course, it's not officially documented AFAIK, which means it may not be suitable for all scenarios. But, RMAN uses it, and it works perfectly fine.

On Thu, Dec 12, 2019 at 2:52 AM Ahmed <gherrami_at_gmail.com> wrote:

> A use case is when you want to list the content of an oracle directory.
> I think when you need to do some things at OS level, java is the best
> choice. Because java is platform-independent.
>
> regards
> Ahmed Fikri
>
> Am Mi., 11. Dez. 2019 um 16:06 Uhr schrieb Sayan Malakshinov <
> xt.and.r_at_gmail.com>:
>
>> Hi Tim,
>>
>> Thanks for mentioning about OS commands, I've just remembered that i use
>> internal Java to execute them with timeout parameter:
>> https://github.com/xtender/xt_shell
>>
>> ср, 11 дек. 2019 г., 17:51 Tim Hall <tim_at_oracle-base.com>:
>>
>>> Hi.
>>>
>>> I suspect a lot of this comes down to one of the following:
>>>
>>> - The functionality didn't exist outside of Java at the time the
>>> solution was first required, and people have stuck with it.
>>> - The Java version was faster at the time, for the specific use case.
>>> - Familiarity. Someone can find an easy example in Java, so they just go
>>> with it.
>>> - PL/SQL feels static, when the world is moving.
>>>
>>> Examples:
>>>
>>> When I first started using Java Stored Procedures for BLOB exports there
>>> was no alternative. The file handling stuff I wrote in Java was because
>>> UTL_FILE couldn't do it. Even when more functionality was added, there are
>>> still things that are a pain in PL/SQL, like listing the files in
>>> directories. I know these can be done with the scheduler or external
>>> tables, but it's a pain compared to using Java. Running OS commands from
>>> PL/SQL was another use case. You can now used the scheduler, but for a long
>>> time you couldn't, and I would still say they are more painful.
>>>
>>> In one job we used UUIDs for the primary key in a bunch of tables. The
>>> Java UUID generator was faster than SYS_GUID() at the time. Not sure if
>>> that is still the case. We had some other maths stuff that was faster in
>>> Java, but natively compiled PL/SQL was comparable, so we went that route.
>>>
>>> Cheers
>>>
>>> Tim...
>>>
>>

-- 
//
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
Received on Thu Dec 12 2019 - 12:22:47 CET

Original text of this message