自己写的分页存储过程运行报错
1
[已关闭问题]
我在数据库里写了一个分页过程
Oracle 数据库，但在执行的时候报错：
public static DataSet RunProcedure(string storedProcName, IDataParameter[] parameters, string tableName )
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet dataSet = new DataSet();
connection.Open();
OracleDataAdapter sqlDA = new OracleDataAdapter();
sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters );
sqlDA.Fill( dataSet, tableName );
connection.Close();
return dataSet;
}
}
在 sqlDA.Fill( dataSet, tableName ); 时候报错，我的过程是能执行的，我在数据库里仕了：
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev sum variance execute forall merge time timestamp interval date <a string literal with character set specification> <a number> <a single-quoted SQL string> pipe <an alternatively-quoted string literal with character set specification> <an alternatively-quoted S ORA-06550: line 1, column 57: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-identifier> <a bind variable> avg count current exists max min prior sql stddev su ORA-06550: line 1, column 79: PLS-00103: Encountered the symbol ":" when expecting one of the following: ( - + case mod new not null <an identifier> <a double-quoted delimited-ident
其他回答(1)
0
protected const string PARM_SQLTEXT = "SqlText";
protected const string PARM_SQLSTATISTIC = "SqlStatistic";
protected const string PARM_PAGEINDEX = "PageIndex";
protected const string PARM_PAGESIZE = "PageSize";
protected const string PARM_TOTALCOUNT = "TotalCount";
protected const string PARM_RECCURSOR = "RecCursor";
OracleParameter[] parms = new OracleParameter[6];
parms[0] = new OracleParameter(PARM_PAGEINDEX, OracleDbType.Int32);
parms[1] = new OracleParameter(PARM_PAGESIZE, OracleDbType.Int32);
parms[2] = new OracleParameter(PARM_SQLTEXT, OracleDbType.Varchar2);
parms[3] = new OracleParameter(PARM_SQLSTATISTIC, OracleDbType.Varchar2);
parms[4] = new OracleParameter(PARM_TOTALCOUNT, OracleDbType.Int32, null, ParameterDirection.Output);
parms[5] = new OracleParameter(PARM_RECCURSOR, OracleDbType.RefCursor, null, ParameterDirection.Output);
int totalCount = 0; //记录总数
parms[0].Value = pageIndex; // 要查询的页码
parms[1].Value = pageSize; // 页大小
parms[2].Value = 选取记录的查询语句;
parms[3].Value = 统计记录总数的查询语句,可以和选取记录的查询语句相同;
OracleConnection conn = new OracleConnection(数据库连接字符串);
OracleCommand cmd =new OracleCommand(conn);
cmd.CommandText = "COMMON_PACK.PAGED_QUERY"; // 分页存储过程名,注意格式,包名前缀.
cmd.CommandType= CommandType.StoredProcedure;
cmd.Parameters.AddRange(parms);
conn.Open();
try
{
OracleDataReader rdr = cmd.ExecuteReader(CommandBehavior.SingleResult);
totalCount = int.Parse(parms[4].Value.ToString()); // 取记录总数
while (reader.Read())
{
// 读数据
}
reader.Dispose();
}
finally
{
if (conn!= null && conn.State != ConnectionState.Closed)
conn.Close();
}
存储过程传得 Parameter Name,不需要 : 前缀.
下面给分页存储过程的申明,实现网上都有,我这里只是演示这些参数的对应关系:
create or replace PACKAGE COMMON_PACK AS
/* 游标类型定义，用于返回数据集 */
TYPE RecordCursor IS REF CURSOR;
PROCEDURE Paged_Query(pageIndex NUMBER,pageSize NUMBER,sqlText VARCHAR2,sqlStatistic VARCHAR2,totalCount OUT NUMBER,recCursor OUT RecordCursor);
END COMMON_PACK;
我传的时候没有前缀：
public DataSet GetPageList(int PageSize, int PageIndex, string strWhere)
{
OracleParameter[] parameters =
{new OracleParameter(":tblName",OracleType.VarChar, 255),
new OracleParameter(":fldName",OracleType.VarChar, 255),
new OracleParameter(":PageSize", OracleType.Number),
new OracleParameter(":PageIndex", OracleType.Number),
new OracleParameter(":IsReCount", OracleType.Clob),
new OracleParameter(":OrderType", OracleType.Clob),
new OracleParameter(":strWhere", OracleType.VarChar,1000),};
parameters[0].Value = "C_USERS";
parameters[1].Value = "OPER_COD";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperOra.RunProcedure("UP_GetRecordByPage",parameters, "C_USERS");
}
@流浪蜗牛:我都看到有了 new OracleParameter(":tblName",OracleType.VarChar, 255) 就这个,改成 new OracleParameter("tblName",OracleType.VarChar, 255)
是 "tblName" 不是 ":tblName".
也就是说,你通过参数的形式,如果是存储过程的的参数,就不要在参数的名称前加 ":",如果是Sql语句的形式,就要在参数前加 ": "
0
您好！ 这个问题是怎么解决的呢，是不是语法的错误？ 我也遇到这个问题了。。。
