Solved

Reading an Oracle Long Raw column in VB.NET

Posted on 2004-10-20
11
2,483 Views
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.
0
Comment
Question by:jrcooperjr
  • 6
  • 5
11 Comments
 
LVL 76

Expert Comment

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

0
 
LVL 1

Author Comment

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

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.
0
 
LVL 76

Expert Comment

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

Check out:
http://download-west.oracle.com/docs/cd/B14117_01/win.101/b10117/features003.htm#sthref123

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" %>

<html>
<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');
            'commit;

            try
                  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 "

                  con.open()

                  dataReader = cmd.ExecuteReader()

              dataReader.Read()

                  junk.text = datareader("col1")

                  con.close()
                  con.dispose()

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

      end sub

</script>

<body>

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

</body>
</html>







0
 
LVL 1

Author Comment

by:jrcooperjr
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 "
               con.open()
               dataReader = cmd.ExecuteReader()
               dataReader.Read()
     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
                    fstreamwriter.Write(System.Convert.Tochar(byteData(i)))
                    i += 1
                Loop
                fstreamwriter.Close()

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.

Thanks...
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 76

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?
0
 
LVL 1

Author Comment

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

0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 125 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).
0
 
LVL 1

Author Comment

by:jrcooperjr
ID: 12401399
Thanks...
 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>
0
 
LVL 76

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?
0
 
LVL 1

Author Comment

by:jrcooperjr
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.
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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 …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now