| 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
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)
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)
SQL> David Fitzjarrell Received on Mon Oct 02 2006 - 10:58:17 CDT
 |
 |