Reading an Oracle Long Raw column in VB.NET

Posted on 2004-10-20
Medium Priority
Last Modified: 2010-05-18
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.
Question by:jrcooperjr
  • 6
  • 5
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12372889
Try using the to_lob function on the long and use the lob features of ODP.


Author Comment

ID: 12382673
Is "to_lob" an SQL function?  I'm not familiar with it.  Could you give an example?
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12382874
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.
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12384034
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"/>



Author Comment

ID: 12390404
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.

LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12399557
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?

Author Comment

ID: 12400788
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.

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 500 total points
ID: 12400940
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).

Author Comment

ID: 12401399
 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>
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 12401811
Now that we've defined that we're stuck with long raw and you can now read the contents:  Is there anything left outstanding?

Author Comment

ID: 12403174
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.

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Integration Management Part 2
When cloud platforms entered the scene, users and companies jumped on board to take advantage of the many benefits, like the ability to work and connect with company information from various locations. What many didn't foresee was the increased risk…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question