| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to do this
fitzjarrell_at_cox.net wrote:
> sjoshi wrote:
> > fitzjarrell_at_cox.net wrote:
> > > sjoshi wrote:
> > > > My mistake. I was using the free edition of TOAD and it somehow did not
> > > > show the all of the
> > > > value and just showed the first character. In SQL/Plus this worked
> > > > fine.
> > > >
> > > > The only thing remaining is how to select ones with '%OLE%' strings in
> > > > them. The output is something like this
> > > >
> > > > UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER)
> > > > --------------------------------------------------------------------------------
> > > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ H S S C . s y m
> > > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ S . s y m
> > > >
> > > > I tried with Like '% O L E%' but that did not work.
> > > >
> > > > This is Oracle 10g Release 2.
> > > >
> > > > thanks
> > > > Sunit
> > > >
> > > > fitzjarrell_at_cox.net wrote:
> > > > > sjoshi wrote:
> > > > > > I tried using the UTL_Raw package as in:
> > > > > >
> > > > > > Select UTL_Raw.cast_to_varchar2(S.ObjectMoniker) From CORESite S
> > > > > >
> > > > > > It dumps out contents like this
> > > > > >
> > > > > > p
> > > > > > E
> > > > > >
> > > > > > for RAW values
> > > > > >
> > > > > > 700072006F006A00650063007400640062000000
> > > > > > 4500230031003100340038003500350035003000380037000000
> > > > > >
> > > > > > Any ideas what I might be doing wrong here ?
> > > > > >
> > > > > > thanks
> > > > > > Sunit
> > > > > >
> > > > > > sjoshi wrote:
> > > > > > > Hello All
> > > > > > >
> > > > > > > For SQLServer I could cast the ObjectMoniker coln which is of varbinary
> > > > > > > type to nvarchar to see if it's got the %OLE% string like in
> > > > > > >
> > > > > > > cast(S.ObjectMoniker as nvarchar(100)) like '%OLE%'
> > > > > > >
> > > > > > > How do I do this for Oracle ? In this case it is of type RAW(256). The
> > > > > > > doc.says that there is Implicit Conversion to char type but I'm getting
> > > > > > > only numbers when I try this :
> > > > > > >
> > > > > > > Select S.ObjectMoniker From CORESite S
> > > > > > >
> > > > > > > If I add the Where Clause, I get 0 rows
> > > > > > >
> > > > > > > Where S.ObjectMoniker Like '%OLE%'
> > > > > > >
> > > > > > > thanks
> > > > > > > Sunit
> > > > >
> > > > > Please stop top-posting.
> > > > >
> > > > > And, provide which version of Oracle you're using; usable answers
> > > > > depend upon such information. As to your plight both 9.2.0.6 and
> > > > > 10.2.0.2 behave in this manner:
> > > > >
> > > > > SQL> create table rawtest(testcol raw(256));
> > > > >
> > > > > Table created.
> > > > >
> > > > > SQL> insert into rawtest(testcol) values (utl_raw.cast_to_raw('This is
> > > > > a test of the utl_raw package'));
> > > > >
> > > > > 1 row created.
> > > > >
> > > > > SQL> insert into rawtest
> > > > > values('700072006F006A006500630074006400620000004500230031003100340038003500350035003000380037000000');
> > > > >
> > > > > 1 row created.
> > > > >
> > > > > SQL> select utl_raw.cast_to_varchar2(testcol)
> > > > > 2 from rawtest;
> > > > >
> > > > > UTL_RAW.CAST_TO_VARCHAR2(TESTCOL)
> > > > > --------------------------------------------------------------------------------
> > > > > This is a test of the utl_raw package
> > > > > p r o j e c t d b E # 1 1 4 8 5 5 5 0 8 7
> > > > >
> > > > > SQL>
> > > > >
> > > > > I don't understand your 'problem' or why you have it, unless what you
> > > > > posted isn't correct as far as the source RAW value is concerned.
> > > > >
> > > > >
> > > > > David Fitzjarrell
> > >
> > > You are still top-posting; stop doing so. Place your response AFTER
> > > the text you're replying to, NOT before.
> > >
> > > Obviously you didn't try hard enough:
> > >
> > > SQL> insert into rawtest values (utl_raw.cast_to_raw('% O L E _ S E R V
> > > E R % \ C r o s s S e c t i o n s \ H S S C . s y m'));
> > >
> > > 1 row created.
> > >
> > > SQL> insert into rawtest values (utl_raw.cast_to_raw('% O L E _ S E R V
> > > E R % \ C r o s s S e c t i o n s \ S . s y m'));
> > >
> > > 1 row created.
> > >
> > > SQL> select utl_raw.cast_to_varchar2(testcol)
> > > 2 from rawtest;
> > >
> > > UTL_RAW.CAST_TO_VARCHAR2(TESTCOL)
> > > --------------------------------------------------------------------------------
> > > This is a test of the utl_raw package
> > > p r o j e c t d b E # 1 1 4 8 5 5 5 0 8 7
> > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ H S S C . s y m
> > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ S . s y m
> > >
> > > SQL> l
> > > 1 select utl_raw.cast_to_varchar2(testcol)
> > > 2 from rawtest
> > > 3 where utl_raw.cast_to_varchar2(testcol) like '% O L E %'
> > > 4 /
> > >
> > > UTL_RAW.CAST_TO_VARCHAR2(TESTCOL)
> > > --------------------------------------------------------------------------------
> > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ H S S C . s y m
> > > % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ S . s y m
> > >
> > > SQL>
> > >
> > >
> > >
> > > David Fitzjarrell
> >
> > Thank again...I did do the same thing but get now rows
> >
> > SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) FROM CORESITE S
> > WHERE
> > 2 UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) LIKE '% O L E %'
> > 3 /
> >
> > no rows selected
> >
> > SQL> spool off
> >
> > Also If I do this
> >
> > WHERE SUBSTR(UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER), 2, 7) = 'O L
> > E';
> >
> > I still get no row selected...stumped what may be wrong here.
> >
> > Sunit
> >
>
> >
Ok this time I tried using the _ option by specifying 2 character before the O and that worked.
1 Select UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) FROM CORESITE S
Where
2* UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) LIKE '__O%'
SQL> /
UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER)
% O L E _ S E R V E R % \ C r o s s S e c t i o n s \ C . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ R S . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ W . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ L . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ H S S C . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ S . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ H S S R . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ C S . s y m % O L E _ S E R V E R % \ C r o s s S e c t i o n s \ T . s y m
Does that make sense ?
thanks again
Sunit
Received on Mon Oct 02 2006 - 13:26:41 CDT
 |
 |