Skip to Main Content
Forums

Hardware

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

HBA support for solaris 10

807557May 30 2006 — edited May 31 2006
Hi All

I need to know which HBA card will support solaris 10.we have sunfire 1280 server, brocade silkworm 12000 and hitachi 9980


Regards
RPS

Comments

BluShadow
987565 wrote:
Hi,
I have one Column which contains duplicates values with comma separated.
Well there's your first problem. Why are you storing data like that. It's completely against 3rd normal form of relational database design.
Customer ID
-----------------------------
5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,,4,7,2

I wrote this:

select REGEXP_REPLACE('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,,4,7,2', '(^|,)([^,]*)(,\2)+','\1\2') from dual;
---------------------------------
5,6,5,6,7,4,1,2,1,4,7,2

But it eliminates only continuous elements.
I want out put like:
---------------------
5,6,7,4,1,2
You'll have to split the string into individual elements, then distinct them, and then (if you really must), aggregate the elements back together to a single string again.
jeneesh
{message:id=10725915}
Frank Kulash
Hi,

Welcome to the forum!

I think the simpletst way would be to
(1) Split the sting into multiple rows, with one number per row. See {message:id=3702412}
(2) Use GROUP BY or SELECT DISTINCT to ge the unique numbers
(3) Use any string aggregation technique to re-combine the numbers into a comma-delimited string.
Purvesh K
Works 11.2g Onwards
with data as
(
  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
)
select listagg(col, ',') within group(order by 1) col
  from (
        select distinct regexp_substr(col, '[^,]+', 1, level) col
          from data
        connect by level <= regexp_count(col, ',')
       )

COL
-------------
1,2,4,5,6,7
971895
One more..........
select distinct token  from (SELECT TRIM( SUBSTR ( txt
                     , INSTR (txt, ',', 1, level ) + 1
                     , INSTR (txt, ',', 1, level+1) - INSTR (txt, ',', 1, level) -1
                     )
            )
           AS token           
    FROM ( SELECT ','||'5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2'||',' AS txt  FROM dual )
    CONNECT BY level <= LENGTH(txt)-LENGTH(REPLACE(txt,',',''))-1) order by 1
990568
Thanks,
This is giving values in columns like
1
2
4
5
6
7

----
Result require is
---------------------
1,2,4,5,6,7

And i cant use LISTAGG, we are using 11.1

Edited by: 987565 on Feb 12, 2013 4:28 AM

Edited by: 987565 on Feb 12, 2013 4:29 AM
BluShadow
987565 wrote:
Thanks,
This is giving values in columns like
1
2
4
5
6
7

----
Result require is
---------------------
1,2,4,5,6,7

And i cant use LISTAGG, we are using 11.1
So use one of the string aggregation techniques in the link you've already been given, or as linked to in the FAQ: {message:id=9360005}
Purvesh K
This version works on 11.1.
with data as
(
  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
)
select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by  rn - 1), ',') col
  from (
        select col, row_number() over (order by 1) rn
          from (
                select distinct regexp_substr(col, '[^,]+', 1, level) col
                  from data
                connect by level <= length(col) - length(replace(col, ','))
               )
       )
 start with rn = 1
connect by prior rn = rn - 1;

COL                                                                         
-------------- 
1,2,4,5,6,7
user10756087
Hi,
select listagg(col, ',') within group(order by 1) col
from (
select distinct substr(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ','),level,1) col from dual
connect by level<=length(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ',')) );

Thanks....

Edited by: user10756087 on Feb 13, 2013 2:42 AM
Purvesh K
user10756087 wrote:
Hi,
select listagg(col, ',') within group(order by 1) col
from (
select distinct substr(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ','),level,1) col from dual
connect by level<=length(replace('5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2', ',')) );

Thanks....

Edited by: user10756087 on Feb 13, 2013 2:42 AM
In the above SQL, the Connect By clause is not correct. This would work correct only if you have numbers between 0 - 9, but for those exceeding 9 (two or more digit numbers), it will consider them two be Two numbers than to be a Single Integer and will execute an additional Iteration, which will be incorrect.

You need to subtract the String formed after replacing the Delimiter (in this case Comma) from the Original Length of string to ascertain the Number of times the Delimiter occurs in the given string. (See my earlier reply that works even prior to 11g versions)

Also, why are you posting your reply to me. It should have been directed to the OP, isn't it?
chris227
11g upwards 
with data as
(
  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual
)
, r (s, c) as (
select
    cast(regexp_substr(col,'\d+')  as varchar2(4000)) --alter the 4000 to your needs, the lower than lesser memory consumption
   ,replace(col
           ,regexp_substr(col,'\d+')
    )
from data
union all
select
    s||','||regexp_substr(c,'\d+')
   ,replace(c
           ,regexp_substr(c,'\d+')
    )
from r
)
cycle c set is_cycle to 1 default 0

select
s
from r    
where
trim(both ',' from c) is null
and
is_cycle = 0

S 
5,6,7,4,1,2
Edited by: chris227 on 13.02.2013 04:19
simplyfied
Solomon Yakobson
If you don't care obout the order:
with t as (
           select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' str from dual
          )
select  x.newstr
  from  t,
        xmltable(
                 'string-join(distinct-values(ora:tokenize($str,",")),",")'
                 passing ',' || t.str as "str"
                 columns newstr varchar2(2000) path '.'
                ) x
/

NEWSTR
------------
1,2,4,5,6,7
SY.
990568
Thanks Purvesh,

While testing with real data, it is ignoring some values. I didn't understand why it happening.

with data as
(
select '5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716' col from dual
)
select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by rn - 1), ',') col
from (
select col, row_number() over (order by 1) rn
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= length(col) - length(replace(col, ','))
)
)
start with rn = 1
connect by prior rn = rn - 1;

-----
Result I got is:
5714,5714,5716

My Real Query is like that:

declare
cursor c1 is
select o.id id
from order o;
v_char varchar2(200) := '';

begin

for a1 in c1 loop
with data as
(
select o.cust_id as col into v_char
from order o
where o.id = a1.id
)
select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by rn - 1), ',') col
from (
select col, row_number() over (order by 1) rn
from (
select distinct regexp_substr(col, '[^,]+', 1, level) col
from data
connect by level <= length(col) - length(replace(col, ','))
)
)
start with rn = 1
connect by prior rn = rn - 1;

SYS.dbms_output.put_line(v_char);

end loop;
end;

Later, i will update same cust_id with v_char.
Solomon Yakobson
987565 wrote:
While testing with real data, it is ignoring some values. I didn't understand why it happening.
That code is only good for one row table. It will not work for multi-row table. Use code I posted. And you do not need PL/SQL at all.

SY.
990568
SY,
Script is failing, i think ora:tokenize is included in 11.2. And i am using 11.1
chris227
No need for pl/sql (But 11.2 i guess :-( 
create table my_order as
(
  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' id, cast (null as varchar2(4000)) cust_id from dual
union all
  select '8,8,8,8,6,6,5,5,5,6,7,9,9,4,9,1,2,1,4,7,2', null from dual
union all
select '5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716', null from dual
)

update my_order o
set cust_id = (
with r (s, c, id) as (
select
    cast(regexp_substr(id,'\d+')  as varchar2(4000))
   ,replace(id
           ,regexp_substr(id,'\d+,?')
    )
  ,id
from my_order
union all
select
    s||','||regexp_substr(c,'\d+')
   ,replace(c
           ,regexp_substr(c,'\d+')
    )
   ,id
from r
)
cycle c set is_cycle to 1 default 0

select
s
from r    
where
trim(both ',' from c) is null
and
is_cycle = 0
and
r.id = o.id
)

select
 cust_id
from my_order

CUST_ID 
5,6,7,4,1,2 
8,6,5,7,9,4,1,2 
5714,5668,5716
It is also possible to alter the connect by query to work on several rows. Personally i would consider xml-based solutions at last.

Edited by: chris227 on 13.02.2013 06:59
915396
Hi SY,

The solution was really nice!!
Never thought even XML utilities can help in this way. :)

But to all Newbie's like me could you please break the XML stuff and tokenize thing and explain it.

Recently discovered - In oracle version <11, it gives ORA-00932: inconsistent datatypes: expected - got CHAR
What is the workaround in such databases??


Help really appreciated.
Ranit B.
odie_63
ranit B wrote:
Recently discovered - In oracle version <11, it gives ORA-00932: inconsistent datatypes: expected - got CHAR
What is the workaround in such databases??
ora:tokenize is 11.2 only.
The documentation is here : http://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb_xquery.htm#CBAGCBGJ

And indeed, prior to 11g, we can only pass XMLType datatype via the PASSING clause.
The workaround is to wrap the scalar value in an XMLType instance using SYS_XMLGEN or XMLElement (for example).

Edited by: odie_63 on 13 févr. 2013 15:45
Stew Ashton
Answer
Since you don't have 11GR2, which makes this much easier, I would use SUBSTR/INSTR to break the values out, then use START WITH/CONNECT BY to bundle the distinct values back together. I avoid REGEXP stuff when i can because it uses lots of CPU.
WITH INDATA AS (
  select '9,9,8,8,7,7,6,6,5,5,4,4,3,3,2,2,1,1' x from dual
  union all
  select '9,9,8,8,7,7,4,4,3,3,2,2,1,1' x from dual
), TOKENIZED AS (
  SELECT DISTINCT A.INNUM,
  dense_rank() over(partition by innum order by b.column_value) x_rank,
  B.COLUMN_VALUE x
  FROM (select rownum innum, x from indata) a,
  table(cast(multiset(
    SELECT trim(SUBSTR(
      x,
      (case level
        when 1 then 1 
        else instr(x, ',', 1, level-1) + 1 
      end),
      (case instr(x, ',', 1, level)
        when 0 then 4000
        else instr(x, ',', 1, level) - 1 -
          (case level
            when 1 then 0
            else instr(x, ',', 1, level-1) 
          end)
      end)
    ))
    FROM dual
    CONNECT BY level <= LENGTH(x) - LENGTH(REPLACE(x, ',', '')) + 1
  ) AS SYS.ODCIVARCHAR2LIST)) B
)
SELECT ltrim(sys_connect_by_path(x, ','),',') x
FROM TOKENIZED A
where connect_by_isleaf = 1
START WITH X_RANK = 1
connect by (innum, x_rank) = ((prior innum, prior x_rank + 1));

X
--------------------
1,2,3,4,5,6,7,8,9
1,2,3,4,7,8,9
Edited by: Stew Ashton on Feb 13, 2013 3:47 PM
Marked as Answer by 990568 · Sep 27 2020
Solomon Yakobson
987565 wrote:
Script is failing, i think ora:tokenize is included in 11.2. And i am using 11.1
That will teach you to post version. Anyway, using XML for lower versions:
SQL> select  *
  2    from  tbl
  3  /

CUST_ID
------------------------------------------------------------------------------------------------------------------------------------
5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2
8,8,8,8,6,6,5,5,5,6,7,9,9,4,9,1,2,1,4,7,2
5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5668,5716,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,5714,56
68,5716


SQL> select  *
  2    from  v$version
  3  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for 64-bit Windows: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> with t1 as (
            select  rowid rid,
                    val
              from  tbl,
                    xmltable(
                             '/a/b'
                             passing xmltype('<a><b>' || replace(cust_id,',','</b><b>') || '</b></a>')
                             columns
                               val number path '.'
                            )
           ),
     t2 as (
            select  distinct rid,
                             val
              from  t1
           )
select  rtrim(xmlagg(xmlelement(e,val,',').extract('//text()')),',') new_cust_id
  from  t2
  group by rid
/

NEW_CUST_ID
----------------
6,7,2,4,1,5
1,7,2,4,6,5,9,8
5714,5716,5668

SQL>
SY.
chris227
"Plain" sql on 11.1 
update my_order o
set cust_id = (
with data as (
select
 ltrim(c,',') c
,id
from my_order
model
partition by (id)
dimension by (1 rn)
measures (id i, cast(null as varchar2(4000)) c)
rules iterate (1e6) until ltrim(i[1],',') is null (
 c[1]=c[cv()]||','||regexp_substr(i[cv()],'\d+')
,i[1]=replace(i[cv()],regexp_substr(i[cv()],'\d+'))
))

select
 c
from data
where
id=o.id
)
990568
Chris, it working. But not when we have duplicate rows are coming.
like:
1,1,12,12,5,5
1,1,12,12,5,5
sql_coder
BluShadow wrote:
987565 wrote:
Hi,
I have one Column which contains duplicates values with comma separated.
Well there's your first problem. Why are you storing data like that. It's completely against 3rd normal form of relational database design.
just curious, why is it against the 3rd normal form and not against the 1st one ?

ikrischer
Purvesh K
This is an enhanced solution to cater Single Column and Multiple rows scenario:
with data as
(
  select '5,5,5,5,6,6,5,5,5,6,7,4,1,2,1,4,7,2' col from dual union all
  select '5612,5613,5614,5613' col from dual union all
  select '1234,2345,3456,2222,2121,2121,1234' col from dual
)
select ltrim(max(sys_connect_by_path(col, ',')) keep (dense_rank last order by  rn - 1), ',') col
  from (
        select col, row_number() over (partition by original_col order by 1) rn, original_col
          from (
                select distinct regexp_substr(col, '[^,]+', 1, level) col, col original_col
                  from data
                connect by level <= (length(col) - length(replace(col, ',')))
                       and col = prior col
                       and prior dbms_random.value is not null
               )
       )
 start with rn = 1
connect by prior rn = rn - 1
       and original_col = prior original_col
       and prior dbms_random.value is not null
group by original_col;

COL
--------------------------
1234,3456,2345,2222,2121                                                                                                                                                                                                                                                                                               
7,6,2,4,1,5
5613,5612,5614
chris227
987565 wrote:
Chris, it working. But not when we have duplicate rows are coming.
like:
1,1,12,12,5,5
1,1,12,12,5,5
NP 
update my_order o
set cust_id = (
with data as (
select
 ltrim(c,',') c
,id
from (select distinct id from my_order)
model
partition by (row_number() over (order by id) r, id)
dimension by (1 rn)
measures (id i, cast(null as varchar2(4000)) c)
rules iterate (1e6) until ltrim(i[1],',') is null (
 c[1]=c[cv()]||','||regexp_substr(i[cv()],'\d+')
,i[1]=replace(i[cv()],regexp_substr(i[cv()],'\d+'))
))
 
select
 c
from data
where
id=o.id
)
Edited by: chris227 on 15.02.2013 00:00
simplification
1 - 25
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Jun 28 2006
Added on May 30 2006
#storage, #storage-general-discussion
5 comments
204 views