Solved

Undefined type causing C# error

Posted on 2012-04-11
11
2,459 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

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

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…
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 shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
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.

773 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