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?

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
/

> 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_at_gmail.com>
> 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_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