We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now

x

URGENT:data type is not supported

Medium Priority
966 Views
Last Modified: 2013-12-17
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
Comment
Watch Question

Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
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

Commented:
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.

Author

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 ?

Author

Commented:
imports oracle....also doesnt work
Dirk HaestProject manager
CERTIFIED EXPERT

Commented:
You need to reference it first

Project - Add reference
(there will be finding something from oracle)

Author

Commented:
ok figured out..i had to add thru references

Author

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

Commented:
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

Author

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.

Commented:
Try catching a TypeInitializationException and display the message of its InnerException. That might point more specifically to the problem.

Author

Commented:
would you be able to tell me exactly how to do this

Commented:
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)

Commented:
... 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)

Author

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]
Commented:
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.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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.

Commented:
>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.

Author

Commented:
yes its a one form application only

Author

Commented:
do you have any opinion for my other ques.

Commented:
You still haven't commented on wheter you have actually managed to read a single clob object in any of the ways discussed.

Author

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

Commented:
Have you been able to read data from a clob column?

Author

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

Commented:
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?

Author

Commented:
rows returned 250 and its not slow by mins...but i know its slow
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.