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?

On 3/13/23 01:49, Quanwen Zhao wrote:
> Hello listeners :-),
>
> I've an requirement getting all of parent path (including itself) for
> a specified path on Linux or getting all of substring (including
> itself) for a string on Oracle SQL?
>
> such as, the path is "/home/oracle/arch", the desired output is:
>
> /home/oracle/arch
> /home/oracle
> /home
> /
>
>  or I've created a test table.
>
> TEST_at_orcl>create table test1 (arch_loc varchar2(50));
> Table created.
>
>
> TEST_at_orcl>insert into test1 values ('/home/oracle/arch');
> 1 row created.
>
>
> TEST_at_orcl>commit;
> Commit complete.
>
>
> TEST_at_orcl>select * from test1;
> ARCH_LOC
> --------------------------------------------------
> /home/oracle/arch
>
>
> Whatever, using the SHELL or Oracle SQL to get the same output. Please
> help me finish it, thanks beforehand!
>
> Best Regards
> Quanwen Zhao

I would use something like this:

!/usr/bin/perl
my _at_MD=split ('/',$ARGV[0]);
my $output='';
foreach  (_at_MD) {

     $output .= "/$_";
     $output=~s/\/\//\//;
     print "$output\n";

This works:

[mgogala_at_umajor scripts]$ /tmp/ttt '/oracle/base/product/19c/dbhome_1'

/
/oracle
/oracle/base
/oracle/base/product
/oracle/base/product/19c
/oracle/base/product/19c/dbhome_1

You can use PL/SQL regex functions to do the same in PL/SQL.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com

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