jrcooperjr
asked on
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.
Try using the to_lob function on the long and use the lob features of ODP.
ASKER
Is "to_lob" an SQL function? I'm not familiar with it. Could you give an example?
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.
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.
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.HtmlControl
<%@ 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=<mypa
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>
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.HtmlControl
<%@ 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=<mypa
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>
ASKER
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
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...
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
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...
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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>
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>
Now that we've defined that we're stuck with long raw and you can now read the contents: Is there anything left outstanding?
ASKER
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.