Solved

Undefined type causing C# error

Posted on 2012-04-11
11
2,506 Views
Last Modified: 2012-04-12
Hello Experts,

I am selecting from a table which contains SDO_Geometry but I am not selecting against the geometry column.

When I use the procedure in oracle, I have no problem but when reading the ref_cursor in the application I receive the following error :"Custom type mapping for 'dataSource='MySource' schemaName='MDSYS' typeName='SDO_GEOMETRY'' is not specified or is invalid " 

So I tried looking up the meaning of this and found some references for defining a UDT in the web.config or app.config but I did not see an example of setting up the entry based on an ORACLE Type of SDO_GEOMETRY.

Also, I do not understand why this is even an issue since I am not selecting the Geometry column of the table.

Thanks for your assistance.
0
Comment
Question by:Focker513
  • 6
  • 5
11 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 500 total points
ID: 37837091
That must mean that the ODP.NET just needs to know about the column, and it is an object type.  Can you show me a little of how you are executing the stored procedure, please?
0
 

Author Comment

by:Focker513
ID: 37837531
I think you are right, but just need to know how to define the SDO_GEOMETRY in the web.config.

public GetTables(string table_name, string connString, out BaseCommand procedure)
        {
            procedure = new BaseCommand();
            procedure.ConnectionStr = connString;
            procedure.Cmd = new OracleCommand("MY_PKG.GET_TABLE");
            procedure.Cmd.CommandType = System.Data.CommandType.StoredProcedure;
            OracleParameter pTableName = procedure.Cmd.Parameters.Add("pTableName", OracleDbType.Varchar2, ParameterDirection.Input);
            OracleParameter RESULT_SET = procedure.Cmd.Parameters.Add("RESULT_SET", OracleDbType.RefCursor, ParameterDirection.Output);

            pTableName.Value = table_name;

           
        }

The out parameter is a Command that I am then executing as a datareader into a datatable:


 public DataTable GetDt(BaseCommand com)
        {
            using (OracleConnection connection = new OracleConnection(com.ConnectionStr))
            {
                using (OracleCommand command = com.Cmd)
                {
                    try
                    {
                        command.Connection = connection;
                        connection.Open();
                        using (OracleDataReader r = command.ExecuteReader())
                        {
                            DataTable dt = new DataTable();
                            dataTable.Load(r);
                            connection.Close();
                            return dt;
                        }

                    }
                    catch (OracleException oe)
                    {
                        throw new Exception(oe.Message);
                    }
                    catch (Exception e)
                    {
                        throw new Exception(e.Message);
                    }

                }
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 37837545
Can you look at the GET_TABLE stored procedure in the MY_PKG package?  Does it reference SDO_GEOMETRY?
0
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 

Author Comment

by:Focker513
ID: 37837559
Yes and it is not in any way referencing SDO_Geometry although the table being referenced does have an SDO_Geometry column.

I am doing a specific selection not a select *.
0
 

Author Comment

by:Focker513
ID: 37837598
Found the issue, another selection within the procedure does have a geometry column.
Sorry for the inconvenience.
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 37837839
I am learning about how .NET and Oracle work together with UDTs, and I am curious how you get this issue solved.
0
 

Author Comment

by:Focker513
ID: 37837920
I know when using the entity framework as an ORM you must define UDT's in the config file.
SDO_Geometry though is an Oracle complex type so I found a package that supposedly deals with it, called NetSdoGeom. I just could not believe I needed that without directly selecting that field. Little did I know another table in the procedure had a Geom column which was the culprit.
0
 

Author Comment

by:Focker513
ID: 37838009
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 37838155
Cool!!  That is what I use (INullable, IOracleCustomType, IOracleCustomTypeFactory, ...) to work with Oracle Custom objects.  I don't have to do anything in the config files, because it is just a parameter value to a package procedure:

       
    OracleParameter parameter = new OracleParameter()
            {
                ParameterName = parameterName,
                OracleDbType = OracleDbType.Object,
                Direction = ParameterDirection.Input,
                UdtTypeName = customTypeAttribute.UdtTypeName,
                Value = this.CreateTable<TFactory, TTable, TRecord>(recordArray),
            };

Open in new window

0
 

Author Comment

by:Focker513
ID: 37838187
And this works with SDO_Geometry?
0
 
LVL 96

Expert Comment

by:Bob Learned
ID: 37838216
No, that works with the UDT that I work with.  I haven't tried to access the spatial geometry stuff yet, so this is a good question to learn something that might be useful for the future.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

749 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