[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 871
  • Last Modified:

URGENT:data type is not supported

hi
my query in asp.net, vb.net page is something like this
"select a,b,c,d into :a1,:b1,:c1,:d1 from tablename"

i have declared all the into variables before hand. but i am getting this error
data type is not supported

One of the data type is a clob field in oracle which i am getting in a string. which is why its giving an error. how to handle this thing
0
samir25
Asked:
samir25
  • 13
  • 10
  • 2
1 Solution
 
DhaestCommented:
What do you want to do exactly ? Getting the data out of your database to show it on the screen or create a backup (copy from your table into another table)

More information about select into: http://www.w3schools.com/sql/sql_select_into.asp
0
 
ToFroCommented:
Are you using the Oracle Data Provider for .NET (ODP.NET). If not, it might be worth a look. Here is a summarized code example from http://www.oracle.com/technology/pub/articles/price_dotnet2.html

' 1. Read a row from a table containging a clob column
myOracleCommand.CommandText =  "SELECT id, clob_column FROM clob_content WHERE id = 1"
Dim myOracleDataReader As OracleDataReader = myOracleCommand.ExecuteReader()
myOracleDataReader.Read()

' 2. Copy the lob locator to an OracleClob object(no data is read yet).
Dim myOracleClob As OracleClob = myOracleDataReader.GetOracleClob(1)

' 3. Get the clob data using the Read() method of the OracleClob object. The characters are read from
'     the clob into a char array(charArray). The code reads the clob 50 chars at a time until the entire
'     clob is read.
Dim charArray(50) As char
Dim numCharsRead As Integer
numCharsRead = myOracleClob.Read(charArray, 0, 50)
Do While (numCharsRead > 0)
  Console.WriteLine("numCharsRead = " & numCharsRead)
  Dim clobData As New string(charArray, 0, numCharsRead)
  Console.WriteLine("clobData = " & clobData)
  numCharsRead = myOracleClob.Read(charArray, 0, 50)
Loop


Hope this gets you started.
0
 
samir25Author Commented:
hi tofro
i downlaoded odp.net and installed it also. i am using vs.net 2003 ... i closed it and restarted..and i tried to do dim ss as ...i was looking for oracledatareader etc...but didnt see.

system.data.orac....nothing like that is tehre..why ?
0
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.

 
samir25Author Commented:
imports oracle....also doesnt work
0
 
DhaestCommented:
You need to reference it first

Project - Add reference
(there will be finding something from oracle)
0
 
samir25Author Commented:
ok figured out..i had to add thru references
0
 
samir25Author Commented:
i referred and i got no errors.however when i run i get this exception
{"The type initializer for 'Oracle.DataAccess.Client.OracleConnection' threw an exception."}

Try
            Dim strConn As String = ConfigurationSettings.AppSettings("OraConn")
            Dim dt As New DataTable()
            Dim test_id, serial, operation, item, station As String
            Dim start_time, xml_data As String
            Using FConnection As New OracleConnection(strConn)
                Using FCommand As New OracleCommand("select a,b,c into :a, :b, :c from te_v6 where status='OK' and Operation='PL' and item='32'", FConnection)
                    Using da As New OracleDataAdapter(FCommand)
                        Try
                            FConnection.Open()
                            da.Fill(dt)
                            FConnection.Close()
                            FCommand.Dispose()
                            FConnection.Dispose()
                        Catch ex As Exception
                            Throw ex
                        Finally
                            If FConnection.State = ConnectionState.Open Then
                                FConnection.Close()
                            End If
                        End Try
                    End Using
                End Using
            End Using
            GridView1.DataSource = dt
            GridView1.DataBind()
        Catch ex As Exception
            Throw ex
        End Try
0
 
ToFroCommented:
Firstly, try importing both of these:
Imports Oracle.DataAccess.Client
Imports Oracle.DataAccess.Types

And secondly, I'm not sure that filling a DataTable like that is the proper way to handle tables with LOB data columns. Have you tried the example I supplied earlier? It would be interesting to know if you can read the data from the blob column like that.

You should also have a look at the Oracle Data Provider for .NET Developer's Guide:
http://download-east.oracle.com/docs/html/B28089_01/toc.htm
0
 
samir25Author Commented:
hi
thanks for the reply
i have imported both of the above.
secondly, right now i am not pulling the blob/clob. i am simply referring to the normal varchar ones...jsut simple query.
0
 
ToFroCommented:
Try catching a TypeInitializationException and display the message of its InnerException. That might point more specifically to the problem.
0
 
samir25Author Commented:
would you be able to tell me exactly how to do this
0
 
ToFroCommented:
You are already catching a System.Exception like this:

 Catch ex As Exception
                            Throw ex


... Just add directly above these two lines the following code:

Catch typeEx as TypeInitializationException
     MessageBox.Show(typeEx.Message)
0
 
ToFroCommented:
... A little correction, it was actually the InnerException that we were interested in. You could display both. Replace the MessageBox line with this:
MessageBox.Show(typeEx.Message & vbNewLine & vbNewLine & typeEx.InnerException.Message)
0
 
samir25Author Commented:
hi tofro.
i am able to get this running as the ref dll was not matching to the oracle version.
now i am facing another problem.
here is what i did
Dim Dr As OracleDataReader = FCommand.ExecuteReader
                        Dim OracleClob As OracleClob
                        Dr.Read()
                        OracleClob = Dr.GetOracleClob(7)

i tried to run it till here only and it gives below exception
Data provider internal error(-3001) [Oracle.DataAccess.Client.OracleDataReader]
0
 
ToFroCommented:
Internal error suggests this is a general error, which means it could be just about any error caused by the Oracle Data Provider and database communicating. I assume that the index in GetOracleClob(7) is the correct zero-based index for the clob column(as retrurned by the query). The code looks ok, but there could also be something wrong with your query.

I would also really like to know if you have tried fetching just one record and one column with a simple query(e.g. "SELECT theClobColumnName from theTable WHERE rowId = '???'"), and then tried feeding the OracleClob object with that? Just to see if the code works otherwise? If something as simple as the example in the first code doesn't work, there is no point in trying to bind the result of a more advanced query to a DataGrid before the problems are sorted out.

It also just crossed my mind that you are probably debugging this code in a Web Application Project. In that case, I suggest that you create a simple Console or Windows Application just for testing.
0
 
samir25Author Commented:
thanks for responding. i dont whether i did the right thing. but i coudlnt get the above thing working so i did the below thing. it takes a lot of time to load the page.
FConnection.Open()
                        Dim xmlDoc As String
                        da.Fill(dt)
                        Dim objXmlDoc As New XmlDocument
                        Dim objNode As XmlNode
                        Dim xData As String
                        For i = 0 To dt.Rows.Count - 1
                            xmlDoc = dt.Rows(i)("xml_data")
                            objXmlDoc.LoadXml(xmlDoc)
                            objNode = objXmlDoc.SelectSingleNode("//Error")
                            xData = objNode.InnerText.Substring(0, 50)
                        Next

i want to know what you mean by
It also just crossed my mind that you are probably debugging this code in a Web Application Project. In that case, I suggest that you create a simple Console or Windows Application just for testing.
0
 
ToFroCommented:
>i want to know what you mean by
>It also just crossed my mind that you are probably debugging this code in a Web Application Project. In >that case, I suggest that you create a simple Console or Windows Application just for testing.

I personally feel that when testing/debugging an issue like this, it's best to start a new simple project consisting of one form or possibly a console application. Mainly because this leaves less code to look at and isolates the problem. Anyway, that's just my opinion, you might work differently.
0
 
samir25Author Commented:
yes its a one form application only
0
 
samir25Author Commented:
do you have any opinion for my other ques.

0
 
ToFroCommented:
You still haven't commented on wheter you have actually managed to read a single clob object in any of the ways discussed.
0
 
samir25Author Commented:
i am not sure what you are asking but here is what i am doing

FConnection.Open()
                        Dim xmlDoc As String
                        da.Fill(dt)
                        Dim objXmlDoc As New XmlDocument
                        Dim objNode As XmlNode
                        Dim xData As String
                        For i = 0 To dt.Rows.Count - 1
                            xmlDoc = dt.Rows(i)("xml_data")
                            objXmlDoc.LoadXml(xmlDoc)
                            objNode = objXmlDoc.SelectSingleNode("//Error")
                            xData = objNode.InnerText.Substring(0, 50)
                        Next

please let me know what exactly you want to know
0
 
ToFroCommented:
Have you been able to read data from a clob column?
0
 
samir25Author Commented:
oh yes...i can print the content on the gridveiw. everything works fine. except that its extremely slow.because i am loading the xml
0
 
ToFroCommented:
Ok. Was it your intention the whole time to put the data in a xml file, or is that a workaround?
And by extremely slow, do you mean minutes? How many rows are returned by the query?
0
 
samir25Author Commented:
rows returned 250 and its not slow by mins...but i know its slow
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 13
  • 10
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now