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?

Wow, thanks to Mladen, Sayan and Maxim's soluton, I'll play with it again.

Best Regards
Quanwen Zhao

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