调用Oracle存储过程出现的问题

    --数据访问层调用方法  
 public object GetPageData(int pindex, string psql, int psize)
       {
           return OracleHelper.GetPageCount(pindex, psql, psize);
       }
--分页调用oracle存储过程        
public static object GetPageCount(int pindex, string psql, int psize)
        {
            //封装参数组
            OleDbParameter pageSql = new OleDbParameter("Psqlcount", psql);
            pageSql.Direction = ParameterDirection.Input;

            OleDbParameter pageCount = new OleDbParameter("Prcount", OleDbType.Integer);
            pageCount.Direction = ParameterDirection.Output;
            OleDbParameter[] param = new OleDbParameter[] { pageSql, pageCount };

            OleDbConnection conn = new OleDbConnection(connWhy);
            OleDbCommand cmd = new OleDbCommand();
            try
            {
                conn.Open();
                cmd.Connection = conn;
                cmd.CommandText = "pendy_Test.PageCount";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddRange(param);
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }
create or replace package pendy_Test      
  is
  TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
--==oracle分页存储过程==
  PROCEDURE DotNetPagination
  (
  Pindex in number,                --分页索引
  Psql in varchar2,                --产生dataset的sql语句
  Psize in number,                 --页面大小
  Pcount out number,               --返回分页总数
  v_cur out type_cur               --返回当前页数据记录
  );
  procedure PageCount
  (
  Psqlcount in varchar2,           --产生dataset的sql语句
  Prcount   out number             --返回记录总数
  );
end pendy_Test;

 

create or replace package body pendy_Test is

--分布存储过程--
PROCEDURE DotNetPagination
  (
  Pindex in number,
  Psql in varchar2,
  Psize in number,
  Pcount out number,
  v_cur out type_cur
  )
  AS
  v_sql VARCHAR2(2000);
  v_count number;
  v_Plow number;
  v_Phei number;

  Begin
  ------------------------------------------------------------取分页总数
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql into v_count;
  Pcount := ceil(v_count/Psize);
  ------------------------------------------------------------显示任意页内容
  v_Phei := Pindex * Psize + Psize;
  v_Plow := v_Phei - Psize + 1;
  --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
  v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
  open v_cur for v_sql;
  End DotNetPagination;
 --**************************************************************************************
  procedure PageCount
  (
  Psqlcount in varchar2,
  Prcount   out number
  )
  as
  v_sql varchar2(1000);
  v_prcount number;
  begin
  v_sql := 'select count(*) from (' || Psqlcount || ')';
  execute immediate v_sql into v_prcount;
  Prcount := v_prcount;                  --返回记录总数
  end PageCount;
 --**************************************************************************************
end pendy_Test;

执行后出现这个错误:

posted on 2016-04-09 11:53  攀迪  阅读(181)  评论(1)    收藏  举报

导航