Re: How to do this

From: <fitzjarrell_at_cox.net>
Date: 2 Oct 2006 09:30:52 -0700
Message-ID: <1159806652.365895.126860@k70g2000cwa.googlegroups.com>

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

| 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

Is this the output or have you posted something you want us to believe is the output? We cannot help you if you won't provide accurate information.

David Fitzjarrell Received on Mon Oct 02 2006 - 11:30:52 CDT