| 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:
> > 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
>
>
>
>
>
>
>
>
>
>
>
> > >
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 Received on Mon Oct 02 2006 - 11:11:43 CDT
 |
 |