ORA-00936：我的方法中缺少表达式ERROR [英] ORA-00936: missing expression ERROR from my method below

问题描述

请帮助解决此错误消息。



i猜测问题可能是来自oracle查询中传递的参数。



请帮忙看看，看看我做得不好。



谢谢



我尝试过：



公共字符串getEMails（字符串staffid）

{

使用（OracleConnection OracleConn =新的OracleConnection（或））

{

OracleConn.Open（）;

try

{

query =SELECT uetc.team_codes，LPAD（papf.employee_number，5,0）staff_id，papf.full_name full_name，papf.email_address，LPAD（papf1.employee_number， 5,0）supervisor_id，papf1.full_name supervisor_name，papf1.email_address，hl.location_code，SUBSTR（hl.location_code，1,3）branch_code，SUBSTR（hl.location_code，6）branch_name，hl.loc_information15 regi on_name，hl.loc_information14 ZONE_NAME FROM per_all_people_f编制序列，per_all_assignments_f PAAF，per_grades PG，per_jobs PJ，hr_locations_all HL，hr_all_organization_units haou，per_all_people_f papf1，per_all_assignments_f paaf1，per_grades PG1，per_jobs PJ1，hr_locations_all HL1，hr_all_organization_units haou1，ub_emp_team_codes_new uetc WHERE papf.person_id = paaf.person_id和papf.current_employee_flag ='Y'和SYSTATE BETWEEN papf.effective_start_date和papf.effective_end_date AND paaf.primary_flag ='Y'和papf.employee_number< 7000 AND papf.employee_number<> 00000 AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.grade_id = pg.grade_id（+）AND paaf.job_id = pj.job_id（+）AND paaf.location_id = hl.location_id（+）AND paaf.organization_id = haou .organization_id（+）AND paaf.supervisor_id = papf1.person_id（+）AND papf1.person_id = paaf1.person_id（+）AND papf1.current_employee_flag（+）='Y'和SYDRATE之间的papf1.effective_start_date（+）和papf1。 effective_end_date（+）AND paaf1.primary_flag（+）='Y'和SYSDATE BETWEEN paaf1.effective_start_date（+）AND paaf1.effective_end_date（+）AND paaf1.grade_id = pg1.grade_id（+）AND paaf1.job_id = pj1.job_id （+）AND paaf1.location_id = hl1.location_id（+）AND paaf1.organization_id = haou1.organization_id（+）AND papf.person_id = uetc.person_id（+）AND uetc.person_id（+）= papf.person_id AND papf。 employee_number = @staff_id;

OracleCommand OraC =新的OracleCommand（查询，OracleConn）;

OraC.CommandType = Com mandType.Text;

OraC.Parameters.Add（new OracleParameter（@ staff_id，OracleDbType.Varchar2））。Value = staffid;

// @OraC.Parameters。添加（新的OracleParameter（SESSION_ID，OracleDbType.Varchar2））。Value = SessionID;

//OraC.Parameters.Add（new OracleParameter（V_RESULT，OracleDbType.Varchar2,32767））。 Direction = ParameterDirection.Output;





// OracleDataReader dr = OraC.ExecuteNonQuery（）;

OracleDataReader dr = OraC.ExecuteReader（）;

while（dr.Read（））

{

SupervisorEmail = dr [EMAIL_ADDRESS_1]。 ToString（）;

StaffEmail = dr [EMAIL_ADDRESS]。ToString（）;

string id = dr [STAFF_ID]。ToString（）;



}



}

catch（例外情况）

{



ex.Message.ToString（）;

}

终于

{

OracleConn.Close（）;

}

返回SupervisorEmail;

//返回StaffEmail;



}

}

kindly help resolve this error messagage.

i guess the problem could be from the parameter im passing in the oracle query.

please just help look into and see what im not doing correctly.

thanks

What I have tried:

public string getEMails(string staffid)
{
using (OracleConnection OracleConn = new OracleConnection(or))
{
OracleConn.Open();
try
{
query = "SELECT uetc.team_codes, LPAD (papf.employee_number, 5, 0) staff_id,papf.full_name full_name, papf.email_address,LPAD (papf1.employee_number, 5, 0) supervisor_id,papf1.full_name supervisor_name, papf1.email_address, hl.location_code, SUBSTR (hl.location_code, 1, 3) branch_code, SUBSTR (hl.location_code, 6) branch_name, hl.loc_information15 region_name, hl.loc_information14 zone_name FROM per_all_people_f papf, per_all_assignments_f paaf, per_grades pg, per_jobs pj, hr_locations_all hl, hr_all_organization_units haou, per_all_people_f papf1, per_all_assignments_f paaf1, per_grades pg1, per_jobs pj1, hr_locations_all hl1, hr_all_organization_units haou1, ub_emp_team_codes_new uetc WHERE papf.person_id = paaf.person_id AND papf.current_employee_flag = 'Y' AND SYSDATE BETWEEN papf.effective_start_date AND papf.effective_end_date AND paaf.primary_flag = 'Y' AND papf.employee_number < 7000 AND papf.employee_number <> 00000 AND SYSDATE BETWEEN paaf.effective_start_date AND paaf.effective_end_date AND paaf.grade_id = pg.grade_id(+) AND paaf.job_id = pj.job_id(+) AND paaf.location_id = hl.location_id(+) AND paaf.organization_id = haou.organization_id(+) AND paaf.supervisor_id = papf1.person_id(+) AND papf1.person_id = paaf1.person_id(+) AND papf1.current_employee_flag(+) = 'Y' AND SYSDATE BETWEEN papf1.effective_start_date(+) AND papf1.effective_end_date(+) AND paaf1.primary_flag(+) = 'Y' AND SYSDATE BETWEEN paaf1.effective_start_date(+) AND paaf1.effective_end_date(+) AND paaf1.grade_id = pg1.grade_id(+) AND paaf1.job_id = pj1.job_id(+) AND paaf1.location_id = hl1.location_id(+) AND paaf1.organization_id = haou1.organization_id(+) AND papf.person_id = uetc.person_id(+) AND uetc.person_id(+) = papf.person_id AND papf.employee_number = @staff_id";
OracleCommand OraC = new OracleCommand(query, OracleConn);
OraC.CommandType = CommandType.Text;
OraC.Parameters.Add(new OracleParameter("@staff_id", OracleDbType.Varchar2)).Value = staffid;
//OraC.Parameters.Add(new OracleParameter("SESSION_ID", OracleDbType.Varchar2)).Value = SessionID;
//OraC.Parameters.Add(new OracleParameter("V_RESULT", OracleDbType.Varchar2, 32767)).Direction = ParameterDirection.Output;


//OracleDataReader dr = OraC.ExecuteNonQuery();
OracleDataReader dr = OraC.ExecuteReader();
while (dr.Read())
{
SupervisorEmail = dr["EMAIL_ADDRESS_1"].ToString();
StaffEmail = dr["EMAIL_ADDRESS"].ToString();
string id = dr["STAFF_ID"].ToString();

}

}
catch (Exception ex)
{

ex.Message.ToString();
}
finally
{
OracleConn.Close();
}
return SupervisorEmail;
//return StaffEmail;

}
}

推荐答案

您真的希望有人检查这个大型SQL查询吗？



追踪此类错误的常用方法通过删除部分来简化查询，直到错误消失。



这将是最简单的版本：

Do you really expect someone to check this large SQL query?

The common method to track down such errors is simplifying the query by removing portions until the error disappears.

This would be in the simplest version:

SELECT * FROM papf WHERE papf.employee_number = @staff_id;

[在提问者的帮助下更新]

使用 OracleCommand 命名参数必须以冒号而不是at字符作为前缀：

[UPDATE with the help of the questioner]
With OracleCommand named parameters must be prefixed with a colon instead of the at character:

SELECT * FROM papf WHERE papf.employee_number = :staff_id;

[/更新]

[/UPDATE]

这篇关于ORA-00936：我的方法中缺少表达式ERROR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！