MIME-Version: 1.0
References: <7560_1573888013_5DCFA00D_7560_15439_1_d2338893765b49d2836d02394268fc81@vontobel.com>
 <CAKsxbLo2Tgoc-1_-i9zeh1+zmNsydzL3uToEBQaOecu28037hw@mail.gmail.com>
 <18414_1575645676_5DEA71EC_18414_8273_1_b8295c4c96c94054b3ce0c65cc955e8e@vontobel.com>
 <CAKsxbLpsZsX=csj8fDLwS-Hg3a_0wnKtHs1+Qh9kXDE=evGEMA@mail.gmail.com>
 <473_1575932906_5DEED3EA_473_14080_1_6d8204ab9e964e388d4459d54b9095cd@vontobel.com>
 <CAP=5zEiMV2EFNCNmGrstn8k0gxv0VX8=OX=yxfnwP81q5BHzpw@mail.gmail.com>
 <CAP=5zEgO0b0JcTjRAoUZLkDYHsSQ1+ya=QQ5AssuWv=gVma1sQ@mail.gmail.com>
 <3248_1575988511_5DEFAD1F_3248_4784_1_8cd96ccdfce54be38a7c8978c33296b8@vontobel.com>
 <CAP=5zEi-WsLM857y-N+7rzpDuNNDZunghDqFsrhLNXp_UQJUSQ@mail.gmail.com>
 <CAOVevU4DpHMBD1uujNTnh75FyqJvkysax0NfuD=pB2D-n0SR2g@mail.gmail.com> <CANkb5P0Mfh4tnJAE7kedk0sOcRAXnGX1oy9Bk8SfS_toFgMHew@mail.gmail.com>
In-Reply-To: <CANkb5P0Mfh4tnJAE7kedk0sOcRAXnGX1oy9Bk8SfS_toFgMHew@mail.gmail.com>
From: Stefan Knecht <knecht.stefan@gmail.com>
Date: Thu, 12 Dec 2019 18:22:47 +0700
Message-ID: <CAP50yQ-Qq8TZeWTgYXJN2zr90eq8reHzVZH4R8cOZ94E38=w7g@mail.gmail.com>
Subject: Re: JVM in the database
To: gherrami@gmail.com
Cc: Noveljic Nenad <nenad.noveljic@vontobel.com>,
 "ORACLE-L (oracle-l@freelists.org)" <oracle-l@freelists.org>
Content-Type: multipart/alternative; boundary="0000000000009c327005997ff796"
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-To: knecht.stefan@gmail.com
--0000000000009c327005997ff796
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

 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)          :=3D 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@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@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
>>
>> =D1=81=D1=80, 11 =D0=B4=D0=B5=D0=BA. 2019 =D0=B3., 17:51 Tim Hall <tim@o=
racle-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 g=
o
>>> with it.
>>> - PL/SQL feels static, when the world is moving.
>>>
>>> Examples:
>>>
>>> When I first started using Java Stored Procedures for BLOB exports ther=
e
>>> 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 fro=
m
>>> 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 i=
n
>>> Java, but natively compiled PL/SQL was comparable, so we went that rout=
e.
>>>
>>> Cheers
>>>
>>> Tim...
>>>
>>

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

--0000000000009c327005997ff796
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">
<div>You can actually do it in pure PL/SQL nowadays:</div><div><span style=
=3D"font-family:monospace"><br></span></div><div><span style=3D"font-family=
:monospace">function =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0x$krbmsft =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 ( path in var=
char2 ) return<br>foo.zz$krbmsft_tt<br>pipelined<br>is<br>=C2=A0 l_path =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =
=C2=A0varchar2(1024) =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0:=3D path || &#39;/&=
#39;;<br>=C2=A0 l_ns =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0varchar2(1024);<br>=C2=A0 l_files =C2=
=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 f=
oo.zz$krbmsft_tt;<br>begin<br><br>=C2=A0 info(13, &#39;Searching for files =
in directory &#39; || l_path || &#39;..&#39;);<br>=C2=A0 sys.dbms_backup_re=
store.searchfiles(l_path, l_ns);<br><br>=C2=A0 select foo.zz$krbmsft_t(fnam=
e_krbmsft, size_krbmsft, stamp_krbmsft)<br>=C2=A0 =C2=A0 bulk collect into =
l_files<br>=C2=A0 =C2=A0 from x$krbmsft;<br><br>=C2=A0 info(13, &#39;Return=
ing list of &#39; || l_files.count || &#39; files..&#39;);<br><br>=C2=A0 fo=
r i in 1..l_files.count loop<br>-- =C2=A0 =C2=A0info(13, &#39;Returning fil=
e &#39; || l_files(i).filename);<br>=C2=A0 =C2=A0 pipe row(l_files(i));<br>=
=C2=A0 end loop;<br>=C2=A0 info(13, &#39;Complete.&#39;);<br>end;</span></d=
iv><div><br></div><div>Taken out of a package, so you may need to fiddle a =
bit to make it run standalone.</div><div><br></div><div>Of course, it&#39;s=
 not officially documented AFAIK, which means it may not be suitable for al=
l scenarios. But, RMAN uses it, and it works perfectly fine.</div><div><br>=
</div>

<div><br></div><div><br></div></div><br><div class=3D"gmail_quote"><div dir=
=3D"ltr" class=3D"gmail_attr">On Thu, Dec 12, 2019 at 2:52 AM Ahmed &lt;<a =
href=3D"mailto:gherrami@gmail.com">gherrami@gmail.com</a>&gt; wrote:<br></d=
iv><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;bord=
er-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div>=
A use case is when you want to list the content of an oracle directory.</di=
v><div>I think when you need to do some things at OS level, java is the bes=
t choice. Because java is platform-independent.</div><div><br></div><div>re=
gards</div><div>Ahmed Fikri<br></div></div><br><div class=3D"gmail_quote"><=
div dir=3D"ltr" class=3D"gmail_attr">Am Mi., 11. Dez. 2019 um 16:06=C2=A0Uh=
r schrieb Sayan Malakshinov &lt;<a href=3D"mailto:xt.and.r@gmail.com" targe=
t=3D"_blank">xt.and.r@gmail.com</a>&gt;:<br></div><blockquote class=3D"gmai=
l_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,20=
4,204);padding-left:1ex"><div dir=3D"auto">Hi Tim,<div dir=3D"auto"><br></d=
iv><div dir=3D"auto">Thanks for mentioning about OS commands, I&#39;ve just=
 remembered that i use internal Java to execute them with timeout parameter=
:=C2=A0<a href=3D"https://github.com/xtender/xt_shell" target=3D"_blank">ht=
tps://github.com/xtender/xt_shell</a></div></div><br><div class=3D"gmail_qu=
ote"><div dir=3D"ltr" class=3D"gmail_attr">=D1=81=D1=80, 11 =D0=B4=D0=B5=D0=
=BA. 2019 =D0=B3., 17:51 Tim Hall &lt;<a href=3D"mailto:tim@oracle-base.com=
" target=3D"_blank">tim@oracle-base.com</a>&gt;:<br></div><blockquote class=
=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rg=
b(204,204,204);padding-left:1ex"><div dir=3D"ltr"><div dir=3D"ltr">Hi.<div>=
<br></div><div>I suspect a lot of this comes down to one of the following:<=
/div><div><br></div><div>- The functionality didn&#39;t exist outside of Ja=
va at the time the solution was first required, and people have stuck with =
it.</div><div>- The Java version was faster at the time, for the specific u=
se case.</div><div>- Familiarity. Someone can find an easy example in Java,=
 so they just go with it.</div><div>- PL/SQL feels static, when the world i=
s moving.</div><div><br></div><div>Examples:</div><div><br></div><div>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 c=
ouldn&#39;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&#39;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=
9;t, and I would still=C2=A0say they are more painful.</div><div><br></div>=
<div>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 th=
at 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.</div>=
<div><br></div><div>Cheers<br></div><div><br></div><div>Tim...</div></div><=
/div>
</blockquote></div>
</blockquote></div>
</blockquote></div><br clear=3D"all"><br>-- <br><div dir=3D"ltr" class=3D"g=
mail_signature"><div dir=3D"ltr"><div><div dir=3D"ltr"><div>//</div><span s=
tyle=3D"font-size:small">zztat - The Next-Gen Oracle Performance Monitoring=
 and Reaction Framework!</span><div style=3D"font-size:small">Visit us at=
=C2=A0<a href=3D"http://zztat.net/" style=3D"color:rgb(17,85,204)" target=
=3D"_blank">zztat.net</a>=C2=A0| @zztat_oracle | <a href=3D"http://fb.me/zz=
tat" target=3D"_blank">fb.me/zztat</a> | <a href=3D"http://zztat.net/blog/"=
 target=3D"_blank">zztat.net/blog/</a></div></div></div></div></div>

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