ORA-00936: missing expression ERROR from my method below

Question

0.00/5 (No votes)

See more:

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;

}
}

Posted 27-Jul-16 0:25am

Emmablakes

Updated 27-Jul-16 0:53am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jochen Arndt · Answer 1 · 2016-07-27T00:53:00

Solution 1

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:

SQL

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

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

SQL

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

[/UPDATE]

Posted 27-Jul-16 0:53am

Jochen Arndt

Updated 27-Jul-16 1:28am

v3

Comments

Emmablakes 27-Jul-16 7:14am

thanks @jochen, i actually dont expect anyone to read that long query but just wanted to show exactly what i was doing and get the best possible solution showing all.
thought, someone said i can't use a @ in an oracle parameter, so that also why i had to show it all.

Jochen Arndt 27-Jul-16 7:26am

I don't know the Oracle specific limitations (have not using it so far; only other SQL databases). But my above short query and some research should let you know.

It seems you are right: Use a colon instead of @. I will update my answer.