Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Reading an Oracle Long Raw column in VB.NET

Posted on 2004-10-20
11
Medium Priority
?
2,717 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 78

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 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.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 78

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
 
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?
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 78

Accepted Solution

by:
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).
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 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?
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

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!

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
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…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Screencast - Getting to Know the Pipeline
Suggested Courses

971 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