How to do this
15/05/2018 11:47
Hello All For SQLServer I could cast the ObjectMoniker coln which is of varbinarytype 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). Thedoc.says that there is Implicit Conversion to char type but I'm gettingonly 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%' thanksSunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - You are still top-posting; stop doing so. Place your response AFTERthe 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 VE 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 VE 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 packagep 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

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - Looks like some sort of conversion going on, and might behave differently with different providers. Is there any chance at all of not using the RAW data type, perhaps using a CLOB or BLOB datatype? These are much more amenable to manipulation, and there are good ADO.Net examples on OTN on how to work with them.

My personal take on RAW is that it is (usually) fine for images and similar, but documents (or doc scraps which looks to be what these are?) tend to work better in an appropriate LOB column.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - | The only thing remaining is how to select ones with '%OLE%' stringsin| 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 believeis the output? We cannot help you if you won't provide accurateinformation.

David Fitzjarrell

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - Please stop top-posting.

And, provide which version of Oracle you're using; usable answersdepend upon such information. As to your plight both 9.2.0.6 and10.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 isa test of the utl_raw package')); 1 row created.

SQL> insert into rawtestvalues('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 packagep 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 youposted isn't correct as far as the source RAW value is concerned.

David Fitzjarrell

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - 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 pE for RAW values 700072006F006A006500630074006400620000004500230031003100340038003500350035003000380037000000 Any ideas what I might be doing wrong here ? thanksSunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - My mistake. I was using the free edition of TOAD and it somehow did notshow the all of thevalue and just showed the first character. In SQL/Plus this workedfine.

The only thing remaining is how to select ones with '%OLE%' strings inthem. 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.

thanksSunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - Yes that's why I used it to see what was happening. Also ASCII functionreports 37,0, 79 for first three so what looks ok. Now the string valuein ADO.NET comes as %OLE_SERVER%CrossSectionsC.sym When I do a DataReader.GetString(ColnName). ? Why is that ? thanksSunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - Thank again...I did do the same thing but get now rows SQL> SELECT UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) FROM CORESITE SWHERE 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 LE'; I still get no row selected...stumped what may be wrong here.

Sunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - sjoshi schreef: 20 would be a space, not 0!0 would probably be NULL (empty, undefined, no data).

Sorry - no ado other than a soccer club on this side.


Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - sjoshi schreef: Does that perhaps mean the first three characters are not: percent-space-capitalO?(You do realize the underscore character is a singleposition wildcard?)

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

Answer score: 5
15/05/2018 11:47 - Ok this time I tried using the _ option by specifying 2 characterbefore the O and that worked.

1 Select UTL_RAW.CAST_TO_VARCHAR2(S.OBJECTMONIKER) FROM CORESITE SWhere 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 againSunit

Source is Usenet: comp.databases.oracle.server
Sign in to add a comment

eDiscover
Helpforce eDiscover provides technical articles updated each dayHelpforce eDiscover RSS feed contains the latest technical articles in RSS
Click the logo to go back to the main page
Search eDiscover
  
Categories

Click an icon to go to that category

Helpforce eDiscover contains articles about Microsoft Windows Helpforce eDiscover contains articles about Apple products and MacOS Helpforce eDiscover contains articles about Linux and POSIX operating systems Helpforce eDiscover contains articles about Helpforce Helpforce has a large variety of technical information and articles for you to read Helpforce eDiscover contains articles about databases, MYSQL, SQL Server Oracle Helpforce eDiscover contains articles about Java, JVM and the JRE Helpforce eDiscover contains articles about the QNX operating system Helpforce eDiscover contains articles about Oracle Solaris and Open Solaris Helpforce eDiscover contains articles about RISC OS, Acorn and the BBC Micro Helpforce eDiscover contains articles about Amiga and AmigaOS

Type your comment into the box below