Solved

Alter Oracle Sequence in C#

Posted on 2013-05-22
2
1,086 Views
Last Modified: 2013-05-22
I need to alter an Oracle Sequence in my code (C#) and I keep receiving an Invalid Cast error.  I've tried casting to INT64, INT32 and just INT and still get the same error.  Here is my code:

        public Boolean resetSEQ(OracleConnection connectionObj, String owner, String seqname)
        {
            Int64 currVal = 0;
            Boolean success = true;
            OracleCommand command = connectionObj.CreateCommand();
            try
            {
                // Reset Sequence
                ErrMsg = "";

                // Check that the database has been opened
                if (connectionObj == null || connectionObj.State != ConnectionState.Open)
                {
                    ErrMsg = "<br />Database is not opened.";
                    return false;
                }

                // Create Command object
                command.Connection = connectionObj;

                try
                {
                    // Get Current Sequence Value
                    command.CommandText =
                        "SELECT " + owner + "." + seqname + ".nextval FROM dual";

                    currVal = (int)command.ExecuteScalar();
                    ErrMsg = ErrMsg + currVal.ToString();
                }
                catch (Exception ex)
                {
                    ErrMsg = ErrMsg + "<br />Error Retrieving Next Sequence for " + seqname + ": " + ex.Message;
                    success = false;
                }
                //try
                //{
                //    // Get Current Sequence Value
                //    command.CommandText =
                //        "SELECT " + owner + "." + seqname + ".currval FROM dual";

                //    currVal = (Int64)command.ExecuteScalar();
                //    ErrMsg = ErrMsg + currVal.ToString();
                //}
                //catch (Exception ex)
                //{
                //    ErrMsg = ErrMsg + "<br />Error Retrieving Current Sequence for " + seqname + ": " + ex.Message;
                //    success = false;
                //}
            }
            catch (Exception ex)
            {
                ErrMsg = ErrMsg + "<br />ERROR resetSEQ: " + ex.Message;
                success = false;
            }
            finally
            {
                command.Dispose();
            }
            return success;
        }

Open in new window


The code only shows trying to get the current value of the sequence.  If I can get this working I can get the rest working.

Any help or suggestions would be greatly appreciated!
0
Comment
Question by:dyarosh
[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 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 39187163
Have you tried double or decimal? The numeric types coming back from Oracle, IMO, are not very intuitive. You might also try putting a breakpoint on the ExecuteScalar line and executing the following in the Immediate Window to check the type:

?command.ExecuteScalar().GetType()

Open in new window

0
 

Author Closing Comment

by:dyarosh
ID: 39187345
Casting as type Decimal worked.  Thanks.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
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.
This video shows how to recover a database from a user managed backup

724 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