?
Solved

Undefined type causing C# error

Posted on 2012-04-11
11
Medium Priority
?
2,729 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

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…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
Suggested Courses

615 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