Solved

Undefined type causing C# error

Posted on 2012-04-11
11
2,438 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
 

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
DevOps Toolchain Recommendations

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

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

912 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now