?
Solved

Undefined type causing C# error

Posted on 2012-04-11
11
Medium Priority
?
2,602 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
11 Comments
 
LVL 96

Accepted Solution

by:
Bob Learned earned 2000 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
TCP/IP Network Protocol Cheat Sheet

TCP/IP is a set of network protocols which is best known for connecting the machines that make up the Internet. The truth is that TCP/IP is one of the oldest network protocols and its survival is mainly based on its simplicity and universality.

 

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

New benefit for Premium Members - Upgrade now!

Ready to get started with anonymous questions today? It's easy! Learn more.

Question has a verified solution.

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

How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses

762 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