{"ORA-01036: illegal variable name/number"} - Update Last Number Sequence Oracle Database

Question

I am trying to update last number of sequence in database oracle using textbox on C#. When I tried to execute my program, I have got the error message {"ORA-01036: illegal variable name/number"} I am trying to rectify this problem, but I did not find how to fix it. I am new in #C and Oracle Database. Does anyone here could help me to solve this problem. Thank you in advance Here is my code

private void IDHEADER()
    {
        var result7 = new StringBuilder();
        using (var connectio1n = new OracleConnection(@"Data Source=mcf;Persist Security Info=True;User ID=tmci38001;Password=tmci38001;"))
        {
            string CommandText2 = "SELECT LAST_NUMBER FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMCI_SEQ_BC_AJU_HEADER'";
            OracleCommand command2 = new OracleCommand(CommandText2, connectio1n);
            connectio1n.Open();
            using (OracleDataReader dr = command2.ExecuteReader())
            {
                while (dr.Read())
                {
                    String a = dr[0].ToString();
                    ID_HEADER.Text = a;

                }
            }
        }
    }
private void btnUpd_Click(object sender, EventArgs e)
    {
        Cursor = Cursors.WaitCursor;
        DialogResult result = MessageBox.Show("Update Sequence ?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Question);
        if (result == DialogResult.Yes)
        {
            if (string.IsNullOrEmpty(ID_HEADER.Text))
            {
                MessageBox.Show("Please Fill in all necessary fields",
                                 "Incomplete Input", MessageBoxButtons.OK,
                                     MessageBoxIcon.Error);
            }
            else
            {
        String HEADER = ID_HEADER.Text.Trim();
OracleConnection db = new OracleConnection("Data Source=mcf;Persist Security Info=True;User ID=tmci38001;Password=tmci38001;");
db.Open();
OracleCommand cmd8 = new OracleCommand("ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY LPAD(TO_NUMBER(:HEADER),0); SELECT TMCI_SEQ_BC_AJU_HEADER.NEXTVAL FROM DUAL; ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY 1 ", db);

          cmd8.BindByName = true;
          cmd8.Parameters.Add(":HEADER", HEADER);
          OracleDataReader oraReader8 = null;
          oraReader8 = cmd8.ExecuteReader();
          oraReader8.Close();
          db.Close();
          db.Dispose();
          IDHEADER();
          Cursor = Cursors.Default;
          MessageBox.Show("BC Sequence Successfully updated...");
            }
        }
    }

As additional information, the error line thrown in oraReader8 = cmd8.ExecuteReader();

In which line the error thrown? I suspected the error thrown in `ExecuteReader()` but not sure which query that throws it. — Tetsuya Yamamoto, Nov 16 '18 at 02:20
Yeah, the error line thrown in oraReader8 = cmd8.ExecuteReader(); — nurman syah, Nov 16 '18 at 02:23
Are you tried to run the second query (`ALTER SEQUENCE ...`) in Oracle query editor by replacing parameter name with a value? If it runs perfectly, then the parameter name becomes the issue. — Tetsuya Yamamoto, Nov 16 '18 at 02:31
Yeah, I am trying to execute the 2nd query also in order to the last number that has been settled can be set automatic increment by 1 — nurman syah, Nov 16 '18 at 02:39
Please check this link for your question. https://stackoverflow.com/questions/21375288/ora-01036-illegal-variable-name-number-when-running-query-through-c-sharp — Shakeer Hussain, Nov 16 '18 at 02:39
So you're implying that the query worked perfectly in Oracle SQL editor without the parameter, right? Then you should try remove the colon from parameter name in `Parameters.Add()`. — Tetsuya Yamamoto, Nov 16 '18 at 02:41
@TetsuyaYamamoto : I've tried to remove the column from parameter name in Parameters.Add but it did not work — nurman syah, Nov 16 '18 at 02:50
Since you have multiple statements, try give a shot to wrap query contents inside `BEGIN ... END;` block, .e.g. `BEGIN ALTER SEQUENCE ... END;`. Probably your query treated as anonymous block. — Tetsuya Yamamoto, Nov 16 '18 at 02:54
@TetsuyaYamamoto : I've tried and I've got the new error message like this {"ORA-06550: line 1, column 7:\nPLS-00103: Encountered the symbol \"ALTER\" when expecting one of the following:\n\n ( begin case declare exit for goto if loop mod null pragma\n raise return select update while with \n <<\n continue close current delete fetch lock insert open rollback\n savepoint set sql execute commit forall merge pipe purge"} — nurman syah, Nov 16 '18 at 03:00

score 0 · Answer 1 · answered Nov 16 '18 at 03:25

Since your query has multiple statements (2 ALTER statements and 1 SELECT statement) which should be executed at once, you must wrap them inside anonymous block and use OPEN ... FOR before ALTER statements like this:

OracleCommand cmd8 = new OracleCommand(@"BEGIN
                     OPEN :1 FOR ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY LPAD(TO_NUMBER(:HEADER),0); 
                     SELECT TMCI_SEQ_BC_AJU_HEADER.NEXTVAL FROM DUAL; 
                     OPEN :2 FOR ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY 1;
                     END;", db);

If the query above does not work, use EXECUTE IMMEDIATE for ALTER statements and concatenate the parameter value, because you cannot execute DDL statements (CREATE, ALTER or DROP) as static SQL inside a block:

OracleCommand cmd8 = new OracleCommand(@"BEGIN
                     EXECUTE IMMEDIATE 'ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY LPAD(TO_NUMBER(' || :HEADER || '),0)'; 
                     SELECT TMCI_SEQ_BC_AJU_HEADER.NEXTVAL FROM DUAL; 
                     EXECUTE IMMEDIATE 'ALTER SEQUENCE TMCI_SEQ_BC_AJU_HEADER INCREMENT BY 1';
                     END;", db);

Then use parameter name without colon (:) prefix in OracleCommand.Parameters:

// standard
cmd8.Parameters.Add("HEADER", HEADER);

// alternative
cmd8.Parameters.AddWithValue("HEADER", HEADER);

As a last resort, if the solutions above doesn't work, use each statement inside OracleCommand separately with this condition:

1) ALTER statements must use ExecuteNonQuery()

2) SELECT statement must use ExecuteReader()

Related issues:

How to execute multiple SQL statements in Oracle?

Getting error while trying to alter table in an sql block

{"ORA-01036: illegal variable name/number"} - Update Last Number Sequence Oracle Database

1 Answers1