• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2853
  • Last Modified:

Reading an Oracle Long Raw column in VB.NET

I just need to get the data from a long raw column in an Oracle table (it is text data) and display it on a VB Windows form.  (I can work with other column data types with no problem, but I'm hitting a wall with this one.)  Thanks for any help.
  • 6
  • 5
1 Solution
slightwv (䄆 Netminder) Commented:
Try using the to_lob function on the long and use the lob features of ODP.

jrcooperjrAuthor Commented:
Is "to_lob" an SQL function?  I'm not familiar with it.  Could you give an example?
slightwv (䄆 Netminder) Commented:
It would appear that I typed before I tried.....

Yes, to_lob is a function.  However, according to the docs: it is only to convert longs to lobs when creating a new table.  The docs state that it cannot be used for any other purpose.

I'll keep looking for a solution.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

slightwv (䄆 Netminder) Commented:
Here's the answer (It was hard to get from the docs)

Check out:

in the section: Obtaining LONG and LONG RAW Data

pay specific attention to the paragraph:
      To obtain data beyond InitialLONGFetchSize bytes or characters, one of the following must be in the select list:

working code sample:
<%@ import namespace = "System" %>
<%@ import namespace = "System.Configuration" %>
<%@ import namespace = "System.Web" %>
<%@ import namespace = "System.Web.UI" %>
<%@ import namespace = "System.Web.UI.WebControls" %>
<%@ import namespace = "System.Web.UI.HtmlControls" %>
<%@ import namespace = "System.Web.Security" %>

<%@ import namespace = "System.Data" %>
<%@ import namespace = "Oracle.DataAccess.Client" %>
<%@ import namespace = "Oracle.DataAccess.Types" %>

<title>DB Sample</title>

<script language="vb" runat="server">

      sub page_load(sender as object, e as eventargs)

            'drop table tab1;
            'create table tab1 (
            '      col1 long
            'insert into tab1 values('Hello World');

                  Dim dataReader As OracleDataReader
                  Dim con As New OracleConnection()

                  con = New OracleConnection("User Id=<myuser>;Password=<mypassword>;Data Source=<myDB>;")

                  Dim cmd as OracleCommand = new OracleCommand()
                  cmd.Connection      = con
                  cmd.CommandType      = CommandType.Text

                  cmd.commandText      = " select rowid, col1 from tab1 "


                  dataReader = cmd.ExecuteReader()


                  junk.text = datareader("col1")


            catch ex as exception
                  response.Write("<BR><BR>" + ex.message)
            end try

      end sub



      <form runat="server" id="f1">
            <asp:textbox id="junk" runat="server"/>


jrcooperjrAuthor Commented:
Thanks, slightwv, for your reply... it got me on the right track and I think I have solved my problem.

I wasn't able to get your example to work... I probably was not understanding something, since I could not get
your "junk.text = dataReader("Col1") to function without an error.

However, taking the example you gave plus the Oracle reference you provided, I came up with the following code:

               cmd.commandText     = " select rowid, long_raw_field from table1 "
               dataReader = cmd.ExecuteReader()
     dim DiskFile as string = "C:\temp.txt"
     dim byteData(2000) as byte
     dim numbytes as long
                numbytes = dataReader.GetBytes(1, 0, byteData, 0, 2000)
                Dim fstreamwriter As StreamWriter = File.AppendText(DiskFile)
                i = 0
                Do While i < numbytes
                    i += 1

This works, and my output file (on disk) ends up containing the text that I am looking for.  I know the above needs additional polishing, such as handling situations where the long-raw field is > than 2000 chars, and also for more directly handling the text data and just putting it directly in a box on the windows form I am using rather than writing it out to the disk, but I can take care of those issues.

If you (or anyone else) would like to suggest an alternative way of doing this, especially if in my ignorance I have done something that is horribly inefficient,  I'd be very interested in learning other possibilities.

I'll wait a day or two before marking this query as answered, but as far as I'm concerned right now, I have a solution.

slightwv (䄆 Netminder) Commented:
Did you create the test table provided in the source code?  My sample used a long column not a long raw column (I did this as a quick example so I could use the textbox for display).  What object are you wanting to use to display the long raw column?  also, what type of data is stored in the long raw column?
jrcooperjrAuthor Commented:
I did not use your table creation script, as I was accessing an existing table that has a long-raw column in it.  I think the problem I encountered had to do with the "junk.text" item.  Maybe by not including and running all of the code you supplied, I omitted something that would have provided a definition for "junk".  (I'm not familiar enough with .net to know if there are some assumptions I missed that are associated with "junk.text".)
The long-raw column has text data, and I'm eventually displaying it in a richtextbox object.
The above code successfully extracts the data from the long-raw column and stores it in the disk file.
I then load the Richtextbox object from the disk file.  
Of course, the above example only works properly if the long-raw column has 2000 bytes or fewer.  Since the actual data may contain some very large amounts of text in the long-raw column, I've added logic to handle the data 2000 bytes at a time.

slightwv (䄆 Netminder) Commented:
The long raw column is what was causing my example to fail.  Long raw columns are to store binary data.  Long columns are for text.

to get your example to work for more than 2000 characters, you will need to loop until you no longer get data.

If possible, can you change the table to use BLOBs instead of long raw?  LOBs are much easier to work with and some day Oracle is going to remove the long data types (They keep saying this but I really don't see it happening for quite a while).
jrcooperjrAuthor Commented:
 I understood I needed to loop to handle more than 2000 chars....
I would love to be able to change the field to be a BLOB-type... unfortunately, the table is used by a major application supplied by an outside vendor, and the table definition is controlled by that vendor.... and their next release of the application (which uses Oracle 9) still defines the field as long-raw.....<sigh>
slightwv (䄆 Netminder) Commented:
Now that we've defined that we're stuck with long raw and you can now read the contents:  Is there anything left outstanding?
jrcooperjrAuthor Commented:
No, thanks.... I've got everything I need (except for a complete and total knowledge of .net) so I'll go ahead and close this question.... thanks again for your assistance.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now