Hi Stefan,
the advantage of Java-Variant as mentioned is the platform-independency (It
doesn't matter for the software-vendor, whether the end-user
has Oracle on Windows, Linux, Solaris...)
But the problem with Java as mentioned here is the frequent security
patching. Also, some Oracle-Upgrades weren't backward compatible with old
java-code.
Therefore I tried instead to use dbms_scheduler and some shell scripts. But
this solution wasn't portable.
I'll test your solution but I don't know if I can use x$ tables and if it's
portable.
It is for me strange why hasn't oracle implemented yet this functionality
on a high level!
Regards,
Ahmed Fikri
Am Do., 12. Dez. 2019 um 12:22 Uhr schrieb Stefan Knecht <
knecht.stefan@xxxxxxxxx>:
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@xxxxxxxxx> 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@xxxxxxxxx>:
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@xxxxxxxxxxxxxxx>:
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 | @zztat_oracle | fb.me/zztat | zztat.net/blog/
