Wow, thanks to Mladen, Sayan and Maxim's soluton, I'll play with it again.
Best Regards
Quanwen Zhao
Maxim <mdemenko@xxxxxxxxx> 于2023年3月21日周二 23:25写道：
Slash has two meanings in the path representation - one is the separator
between different levels of path hierarchy, another one is a root , so it
always has to be handled separately - either by recursive throwing the
chunks of path away, or by showing the root, so most simple adjustment to
the bash expression i can think, would be
~$ :() { [[ ! -z $1 ]] && echo $1 && : ${1%/*} || echo /; } ; :
/oracle/base/product/19c/dbhome_1
/oracle/base/product/19c/dbhome_1
/oracle/base/product/19c
/oracle/base/product
/oracle/base
/oracle
/
Regards
(reminded me of 0!=1 ;-) )
Maxim
On Tue, Mar 21, 2023 at 5:26 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
And just for fun, xquery solution:
https://dbfiddle.uk/ULeCzVAp
create table test1 (arch_loc )
as
select '/home/oracle/arch' from dual union all
select '/a/b/c/d/e' from dual union all
select '/x1/y2/z3' from dual;
select *
from test1
,xmltable('
let $codepoints := fn:string-to-codepoints($A)
let $characterCode := fn:string-to-codepoints($character)[1]
let $positions := fn:index-of($codepoints, $characterCode)
for $position in ($positions,string-length($A))
order by $position descending
return fn:substring($A,1,$position)
'
passing
arch_loc as "A",
'/' as "character"
columns
n for ordinality,
c varchar2(50) path '.'
)(+)
On Tue, Mar 21, 2023 at 3:43 AM Sayan Malakshinov <xt.and.r@xxxxxxxxx>
wrote:
As Mladen mentioned, each Oracle installation contains its own Perl in
the $ORACLE_HOME/perl/bin/perl directory. To use it, you can simply call
the script with the appropriate arguments like this:
```
#> $ORACLE_HOME/perl/bin/perl -l ttree.pl /x/y/z
/x/y/z
/x/y/
/x/
/
```
Alternatively, you can modify the shebang in your script to point to the
Oracle Perl installation:
1. Edit the ttree.pl file to include the {ORACLE_HOME} placeholder:
#> cat ttree.pl
#!{ORACLE_HOME}/perl/bin/perl -l
$_=$ARGV[0];
print;
print while(s#[^/]+/?$##)
2. Use sed to replace the {ORACLE_HOME} placeholder with the actual
$ORACLE_HOME value:
#> sed -i "s#{ORACLE_HOME}#$ORACLE_HOME#" ttree.pl
3. Verify the updated shebang:
#> cat ttree.pl
#!/opt/oracle/product/19c/dbhome_1/perl/bin/perl -l
$_=$ARGV[0];
print;
print while(s#[^/]+/?$##)
4. Run the script:
#> ./ttree.pl /a/b/c/d/e
/a/b/c/d/e
/a/b/c/d/
/a/b/c/
/a/b/
/a/
/
On Tue, Mar 21, 2023 at 2:51 AM Mladen Gogala <gogala.mladen@xxxxxxxxx>
wrote:
On 3/20/23 22:22, Quanwen Zhao wrote:
/tmp/ttt: line 1: !/usr/bin/perl: No such file or directory
Well, you don't have Perl installed. Writing Perl scripts is much
easier with the interpreter installed. You can use perl interpreter form
$ORACLE_HOME. So, please set your path to include $ORACLE_HOME/perl/bin and
replace the line #!/usr/bin/perl with #!/usr/bin/env perl. That should do
the trick. Here is the perl from Oracle 19.18:
bash-4.4$ cd $ORACLE_HOME/perl
bash-4.4$ cd bin
bash-4.4$ ./perl -v
This is perl 5, version 36, subversion 0 (v5.36.0) built for
x86_64-linux-thread-multi
Copyright 1987-2022, Larry Wall
Perl may be copied only under the terms of either the Artistic License
or the
GNU General Public License, which may be found in the Perl 5 source kit.
Complete documentation for Perl, including FAQ lists, should be found on
this system using "man perl" or "perldoc perl". If you have access to
the
Internet, point your browser at https://www.perl.org/, the Perl Home
Page.
mgogala@umajor ~]$ sqlplus scott/tiger@localhost/orclcdb
SQL*Plus: Release 21.0.0.0.0 - Production on Mon Mar 20 22:33:50 2023
Version 21.9.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Last Successful login time: Thu Mar 09 2023 17:40:12 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0
SQL>
BTW, my desktop is Fedora 37 and I am running my Oracle in podman:
[mgogala@umajor ~]$ podman ps
CONTAINER ID IMAGE COMMAND CREATED
STATUS PORTS NAMES
dd907d72efa8 localhost/ora19:18 6 weeks ago Up 6
minutes ora19-18
That is why I need localhost and SQL*Net. It's rather easy to create
your own container on top of OL8. Unfortunately, generic images are made
with the base version, so that means that fast ingest (memoptimize for
write) is not available. I am playing with it, will write a blog post when
done. You don't need K8S, they are complicated. Docker (or podman)
container is all that you need. And since my database is meant to be thrown
away when the new version comes in, I am not mounting volumes and don't
need docker-compose. That has an added benefit of making I/O slower and
thereby differences in execution plans more noticeable.
Regards
--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217https://dbwhisperer.wordpress.com
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org
--
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org
