ORA-00936: missing expression while using stringbuilder

Question

I am using StringBuilder to use multiple queries in asp.net. But I am getting error as

ORA-00936: missing expression

Below is my query.

StringBuilder sb = new StringBuilder();
if (ddlProject.SelectedValue != "0" && ddlBuilding.SelectedValue != "0")
{
    sb.Append("insert into xxacl_pN_LEASES_ALL_h  select sysdate,* from xxacl_pN_LEASES_ALL");
    sb.Append(";");
    sb.Append("update xxacl_pN_LEASES_ALL set ASSIGNED_TO = '" + ddlSalesUser.SelectedValue + "'");
    sb.Append(";");
}
if (ddlProject.SelectedValue != "0" && ddlBuilding.SelectedValue == "0")
{
    sb.Append("insert into xxacl_pN_LEASES_ALL_h  select sysdate,* from xxacl_pN_LEASES_ALL");
    sb.Append(";");
    sb.Append("update xxacl_pN_LEASES_ALL set ASSIGNED_TO = '" + ddlSalesUser.SelectedValue + "'");
    sb.Append(";");
}

OracleConnection ObjPriCon = new OracleConnection(System.Configuration.ConfigurationManager.ConnectionStrings["OracleConn"].ToString());
OracleCommand cmd1 = new OracleCommand();
string allQueries = sb.ToString();
cmd1.CommandText = allQueries;
cmd1.Connection = ObjPriCon;
ObjPriCon.Open();
cmd1.ExecuteNonQuery(); // here is the error caused
ScriptManager.RegisterStartupScript(this, this.GetType(), "alert", "alert('Record updated successfully');window.location ='FrmHoldingCoordinateUpdate.aspx?TranType=FM&PView=N&Mode=A&Redirect=oracle&Key=0&Redirect=" + Request.QueryString["Redirect"] + "&userid=" + Request.QueryString["userid"].ToString() + "';", true);

Also, allQueries gives result as

insert into xxacl_pN_LEASES_ALL_h select getdate(),* from xxacl_pN_LEASES_ALL;update xxacl_pN_LEASES_ALL set ASSIGNED_TO = '5681';

@ThomasSchremser: Nope, sorry my mistake, I will change it. Is that the error ? — Nad, Sep 08 '16 at 11:20
shouldnt there be semi-colon after insert into xxacl_pN_LEASES_ALL_h — Sujit.Warrier, Sep 08 '16 at 11:39
@Mysterio11: First, I want to update the history table with the data from main table. Then Insert into the main table with fresh data in **ORACLE** — Nad, Sep 08 '16 at 11:43
the way to find current date in oracle is SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL; — Sujit.Warrier, Sep 08 '16 at 11:54

score -1 · Accepted Answer · answered Sep 08 '16 at 11:28

-1

You need use alias for table name before * For example:

insert into xxacl_pN_LEASES_ALL_h  select getdate(),t.* from xxacl_pN_LEASES_ALL t;

answered Sep 08 '16 at 11:28

AlexSmet

2,141
1
13
18

And, to clarify this (correct) answer: select * from... works, but it doesn't work if you add other columns, such as `sysdate`. In such cases you must use the table name (or alias) before *. – Sep 08 '16 at 11:30
@mathguy: I used it like this `insert into xxacl_pN_LEASES_ALL_h select sysdate,t.* from xxacl_pN_LEASES_ALL t` but getting error as **ORA-00911: invalid character** – Nad Sep 08 '16 at 11:32
@mathguy: the answer posted by `Surename` is not working for me – Nad Sep 08 '16 at 11:48
@nad: Can you separate insert and update, and execute them as two different query? I think problem in `;` You can try remove `update` query, just for test. – AlexSmet Sep 08 '16 at 11:51
@nad - that is wrong, and unfair to Surename. Clearly his answer DID work, since it cleared your "missing expression" error. Now you have a different error - this means your code has many problems, not one. That is not Surename's fault. Blaming volunteers who give you correct answers for your other mistakes in your code won't win you many friends on this or any other site. – Sep 08 '16 at 11:51
@Surename: I tried your single statement too, it gives error as `ORA-00947: not enough values` – Nad Sep 08 '16 at 12:00
@mathguy: even his answer is not working, I still get the error as `ORA-00947: not enough values` – Nad Sep 08 '16 at 12:00
@mathguy: actually, while inserting into the history table, there is a `SEQ` column whose name is `HISTSEQ_NO` and it is also unique. So how to insert values in this ? – Nad Sep 08 '16 at 12:06
@nad It is another error. Please, check count of columns in source and destination tables. I think destination table `xxacl_pN_LEASES_ALL_h` has more columns, than you try insert. – AlexSmet Sep 08 '16 at 12:13
@nad Return to error `ORA-00911: invalid character` You can try another way. Place you expressions in anonymous block. For example: `sb.Append("BEGIN ");` `sb.Append("insert into xxacl_pN_LEASES_ALL_h select sysdate,* from xxacl_pN_LEASES_ALL");` `sb.Append("; ");` `sb.Append("update xxacl_pN_LEASES_ALL set ASSIGNED_TO = '" + ddlSalesUser.SelectedValue + "'");` `sb.Append(";");` `sb.Append(" END;");` I haven't got C# environment, but I think this exaple must work. – AlexSmet Sep 08 '16 at 12:26

ORA-00936: missing expression while using stringbuilder

1 Answers1