Solved

ODP.NET Default Column Values

Posted on 2014-11-17
3
217 Views
Last Modified: 2014-11-23
We are trying to migrate from System.Data.OracleClient to the Managed ODP.NET (4.121.1.20131211) data provider.  The main issue that we are having at the moment is we are unable to load the default column values from the database.  With System.Data.OracleClient this happened when calling OracleDataAdapter.FillSchema().  Unfortunately, it doesn't appear that Oracle implemented the logic the same as Microsoft and I can find much information on possible workarounds.

For my purposes I believe that if I manually set the default value on the column that my problem will be solved.  I was considering running the following schema query:

SELECT TABLE_NAME, COLUMN_NAME, DATA_DEFAULT
FROM USER_TAB_COLUMNS 
WHERE data_default IS NOT NULL AND TABLE_NAME = 'MY_TABLE'

Open in new window


My issue is that I am not sure how I can easily take the value that I get from the query and convert it to the value that should be used to set the default column value in all cases.  Can anyone help point me in the right direction?
0
Comment
Question by:kmcbrearty
[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
  • 2
3 Comments
 
LVL 23

Expert Comment

by:Michael Fowler
ID: 40448492
Just a thought as no one else has jumped in but I have seen this solution where the error is not all variables bound.

You Can Solve Your Problem by calling Stored Procedures this way... "begin yourStoredProce(param);end;"
NOTE:  COMMAND TYPE IS TEXT NOT STORED pROCEDURE
OracleCommand cmd = new OracleCommand("begin U_50004REG_REPORTS.USRUNI114(2612);end;", this.conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();

Hope this helps
0
 
LVL 3

Accepted Solution

by:
kmcbrearty earned 0 total points
ID: 40450349
I was able to workaround the issue by redefining the update and insert queries on the DataAdapter to only include the columns that exist in the DataTable.  By not including the values in the queries it allows the database to handle the default values.  I am not sure that this is entirely consistent with the way that the System.Data.OracleClient classes worked but it should solve my problem.
0
 
LVL 3

Author Closing Comment

by:kmcbrearty
ID: 40460453
I included the information on how the problem was actually solved.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

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 article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
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 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

726 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