ORA-06550 line 10, column 41: PLS-00103: Encountered the symbol "," when expecting one of the following:

Question

I am executing an anonymous PL/SQL block from C# but I am getting an error like below:

ORA-06550 line 10, column 41: PLS-00103: Encountered the symbol "," when expecting one of the following:

My code:

OracleDB AppConn = new OracleDB();
OracleDataReader eligiblereader = null;

 string id = "2304502001101";
 long provider = 667;
 long policy = 150;
 DateTime to = Convert.ToDateTime("2015/05/06");
using (OracleConnection con = AppConn.Connection)
{
 OracleCommand cmd = con.CreateCommand();

 cmd.Connection = con;
cmd.CommandText = @"declare 
                  p_id   VARCHAR2;
                  p_policy_id   NUMBER;
                  p_provider_id   NUMBER;
                  p_date        DATE;
                  p_tob         tob_type;
                 begin
                                 pbm_pkg.get_member_tob(p_id,p_policy_id,p_provider_id,p_date,p_tob);
open :refcur for select tob_type.benefit_id from dual;
end;";
OracleParameter p = cmd.Parameters.Add(
                         "rs", OracleDbType.RefCursor,
                         DBNull.Value,
                         ParameterDirection.Output);
 OracleParameter p_id = new OracleParameter();
  p_id.OracleDbType = OracleDbType.Varchar2;
  p_id.Direction = ParameterDirection.Input;
  p_id.Value = id;

OracleParameter p_policy_id = new OracleParameter();
p_policy_id.OracleDbType = OracleDbType.Int64;
p_policy_id.Direction = ParameterDirection.Input;
p_policy_id.Value = policy;

OracleParameter p_provider_id = new OracleParameter();
p_provider_id.OracleDbType = OracleDbType.Int64;
p_provider_id.Direction = ParameterDirection.Input;
p_provider_id.Value = provider;

OracleParameter p_date = new OracleParameter();
p_date.OracleDbType = OracleDbType.Date;
p_date.Direction = ParameterDirection.Input;
p_date.Value = to;
cmd.Parameters.Add(p_id);
cmd.Parameters.Add(p_policy_id);
cmd.Parameters.Add(p_provider_id);
cmd.Parameters.Add(p_date);

    try
    {
    con.Open();

    cmd.ExecuteNonQuery();

eligiblereader = ((OracleRefCursor)cmd.Parameters[4].Value).GetDataReader();

 while (eligiblereader.Read())
 {
  string id = eligiblereader.GetValue(0).ToString();

}
}
catch (Exception ex)
 {
}
finally
 {
con.Close();
}
return View();
        }

The error is coming when the command is executing 'cmd.ExecuteNonQuery();'

I think I am missing something in anonymous block.

Answer 1

p_id VARCHAR2;

This is certainly incorrect syntax and will throw a compilation error.

Error reproduce:

SQL> DECLARE
  2    p_id VARCHAR2;
  3  BEGIN
  4    NULL;
  5  END;
  6  /
  p_id VARCHAR2;
       *
ERROR at line 2:
ORA-06550: line 2, column 8:
PLS-00215: String length constraints must be in range (1 .. 32767)


SQL>

So, you must specify the string length constraint between 1 .. 32767.

Solution:

Specify the required length for the string variable.

SQL> DECLARE
  2    p_id VARCHAR2(20);
  3  BEGIN
  4    NULL;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL>

open :refcur for select tob_type.benefit_id from dual;

You need to declare the bind variable for the refcursor.

For example,

SQL> var r refcursor
SQL>
SQL> BEGIN
  2    OPEN :r FOR SELECT empno,ename FROM emp;
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> print r

     EMPNO ENAME
---------- ----------
      7369 SMITH
      7499 ALLEN
      7521 WARD
      7566 JONES
      7654 MARTIN
      7698 BLAKE
      7782 CLARK
      7788 SCOTT
      7839 KING
      7844 TURNER
      7876 ADAMS
      7900 JAMES
      7902 FORD
      7934 MILLER

14 rows selected.

SQL>