Re: How to get all of parent path (including itself) for a specified path on Linux or how to get all of substring (including itself) for a string on Oracle SQL?

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;

          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 '.'

> 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_at_gmail.com>
> 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_at_umajor ~]$ sqlplus scott/tiger_at_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_at_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

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