首页 新闻 搜索 专区 学院

自己写的分页存储过程运行报错

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  

流浪蜗牛的主页 流浪蜗牛 | 初学一级 | 园豆:180
提问于:2010-08-12 09:12
< >
分享
其他回答(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;
Launcher | 园豆:45045 (高人七级) | 2010-08-12 09:49
我传的时候没有前缀: 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"); }
支持(0) 反对(0) 流浪蜗牛 | 园豆:180 (初学一级) | 2010-08-12 10:15
@流浪蜗牛:我都看到有了 new OracleParameter(":tblName",OracleType.VarChar, 255) 就这个,改成 new OracleParameter("tblName",OracleType.VarChar, 255) 是 "tblName" 不是 ":tblName".
支持(0) 反对(0) Launcher | 园豆:45045 (高人七级) | 2010-08-12 10:42
也就是说,你通过参数的形式,如果是存储过程的的参数,就不要在参数的名称前加 ":",如果是Sql语句的形式,就要在参数前加 ": "
支持(0) 反对(0) Launcher | 园豆:45045 (高人七级) | 2010-08-12 10:43
0

您好!  这个问题是怎么解决的呢,是不是语法的错误? 我也遇到这个问题了。。。

lfz-woniu | 园豆:194 (初学一级) | 2013-08-09 09:41
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册