Oracle分页存储过程出错，帮忙看下

Question

--创建包规范
create or replace package package_page as
  type cursor_page is ref cursor;
  Procedure proc_page( 
             p_curpage Number,                     --当前页
             p_pagesize Number,                   --每页大小
             p_tablename varchar2,                --表名emp e
             p_where varchar2,                       --查询条件e.ename like '%S%'
             p_tablecolumn varchar2,             --查询列e.id,e.ename,e.job
             p_order varchar2,                         --排序e.ename desc
             p_rowcount out Number,             --总条数,输出参数
             p_pagecount out number,            --总页数 
             p_cursor out cursor_page);        --结果集
end package_page;
--创建包主休
Create Or Replace Package Body package_page
Is
       --存储过程
      Procedure proc_page(
             p_curpage Number,
             p_pagesize Number,
             p_tablename varchar2,
             p_where varchar2,
             p_tablecolumn varchar2,
             p_order varchar2,  
             p_rowcount out Number,
             p_pagecount out number,
             p_cursor out cursor_page
      )
      is
            v_count_sql varchar2(2000);
            v_select_sql varchar2(2000);
      begin
            --查询总条数
            v_count_sql:='select count(*) from '||p_tablename;
            --连接查询条件(''也属于is null)
            if p_where is not null  then
               v_count_sql:=v_count_sql||' where '||p_where;
            end if;
            --执行查询,查询总条数
            execute immediate v_count_sql into p_rowcount;
            
            --dbms_output.put_line('查询总条数SQL=>'||v_count_sql);
            --dbms_output.put_line('查询总条数Count='||p_rowcount);
            
             --得到总页数
             if mod(p_rowcount,p_pagesize)=0 then
                p_pagecount:=p_rowcount/p_pagesize;
             else
                p_pagecount:=p_rowcount/p_pagesize+1;
             end if;
            
            --如果查询记录大于0则查询结果集
            if p_rowcount>0 and p_curpage>=1 and p_curpage<=p_pagecount then
               
               --查询所有(只有一页)
               if p_rowcount<=p_pagesize then
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if;
               elsif p_curpage=1 then  --查询第一页
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where||' and rownum<='||p_pagesize;
                  else
                     v_select_sql:=v_select_sql||' where rownum<='||p_pagesize;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if; 
               else      --查询指定页
                  v_select_sql:='select * from (select '|| p_tablecolumn ||',rownum row_num from '|| p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if;
                  v_select_sql:=v_select_sql||') where row_num>'||((p_curpage-1)*p_pagesize)||' and row_num<='||(p_curpage*p_pagesize);
               end if;
               --执行查询
               dbms_output.put_line('查询语句=>'||v_select_sql);
               open p_cursor for v_select_sql;
            end if;
            
      end proc_page;
end package_page;
 我是用的.NET开发
调用代码如下 ：        OracleParameter[] para = { 
             new OracleParameter("p_curpage",OracleType.Number),
             new OracleParameter("p_pagesize",OracleType.Number),
             new OracleParameter("p_tablename",OracleType.Number),
             new OracleParameter("p_where",OracleType.VarChar),
             new OracleParameter("p_tablecolumn",OracleType.VarChar),
             new OracleParameter("p_order",OracleType.VarChar),
             new OracleParameter("p_rowcount",OracleType.Number),
             new OracleParameter("p_pagecount",OracleType.Number),
             new OracleParameter("p_cursor",OracleType.Cursor)
        };        para[0].Value = Convert.ToInt32(txtPageIndex.Text);
        para[1].Value = Convert.ToInt32(txtSize.Text);
        para[2].Value = "HOSPITAL_CONFIG e";
        para[3].Value = "";
        para[4].Value = "e.*";
        para[5].Value = "e.HOSPITAL_NO desc";
        para[6].Direction = ParameterDirection.Output;
        para[7].Direction = ParameterDirection.Output;
        para[8].Direction = ParameterDirection.Output;        //DbHelperSQL2.RunProcedure("PACKAGE_PAGE.proc_page", para,"ds");
        DataSet ds= DbHelperOra.RunProcedure("PACKAGE_PAGE.proc_page", para, "ds");
        this.GridView1.DataSource = ds;
        this.GridView1.DataBind();        int record = Convert.ToInt32(para[6].Value);传入的页码和页大小时经常出错，有的时候可以，有的时候报益处异常，好像是页码和页大小，总记录数相除时的错误，才高手帮忙看下，
我第一次用ORACLE，麻烦了，项目临时要改成ORACLE的

Answer 1

是varchar2（）范围定义小了吗。不懂.net。

Answer 2

改了一下，再试试：CREATE OR REPLACE PACKAGE PACKAGE_PAGE AS
  TYPE CURSOR_PAGE IS REF CURSOR;
  PROCEDURE PROC_PAGE(
            P_CURPAGE NUMBER,                    --当前页
            P_PAGESIZE NUMBER,                  --每页大小
            P_TABLENAME VARCHAR2,                --表名EMP E
            P_WHERE VARCHAR2,                      --查询条件E.ENAME LIKE '%S%'
            P_TABLECOLUMN VARCHAR2,            --查询列E.ID,E.ENAME,E.JOB
            P_ORDER VARCHAR2,                        --排序E.ENAME DESC
            P_ROWCOUNT OUT NUMBER,            --总条数,输出参数
            P_PAGECOUNT OUT NUMBER,            --总页数
            P_CURSOR OUT CURSOR_PAGE);        --结果集
END PACKAGE_PAGE;CREATE OR REPLACE PACKAGE BODY PACKAGE_PAGE
IS
      --存储过程
      PROCEDURE PROC_PAGE(
            P_CURPAGE NUMBER,
            P_PAGESIZE NUMBER,
            P_TABLENAME VARCHAR2,
            P_WHERE VARCHAR2,
            P_TABLECOLUMN VARCHAR2,
            P_ORDER VARCHAR2,
            P_ROWCOUNT OUT NUMBER,
            P_PAGECOUNT OUT NUMBER,
            P_CURSOR OUT CURSOR_PAGE
      )
      IS
            V_COUNT_SQL VARCHAR2(2000);
            V_SELECT_SQL VARCHAR2(2000);
      BEGIN
            --查询总条数
            V_COUNT_SQL:='SELECT COUNT(*) FROM '||P_TABLENAME;
            --连接查询条件(''也属于IS NULL)
            IF P_WHERE IS NOT NULL  THEN
              V_COUNT_SQL:=V_COUNT_SQL||' WHERE '||P_WHERE;
            END IF;
            --执行查询,查询总条数
            EXECUTE IMMEDIATE V_COUNT_SQL INTO P_ROWCOUNT;            --DBMS_OUTPUT.PUT_LINE('查询总条数SQL=>'||V_COUNT_SQL);
            --DBMS_OUTPUT.PUT_LINE('查询总条数COUNT='||P_ROWCOUNT);            --得到总页数
            IF MOD(P_ROWCOUNT,P_PAGESIZE)=0 THEN
                P_PAGECOUNT:=P_ROWCOUNT/P_PAGESIZE;
            ELSE
                P_PAGECOUNT:=TRUNC(P_ROWCOUNT/P_PAGESIZE) + 1;
            END IF;            --如果查询记录大于0则查询结果集
            IF P_ROWCOUNT>0 AND P_CURPAGE>=1 AND P_CURPAGE <=P_PAGECOUNT THEN              --查询所有(只有一页)
              IF P_ROWCOUNT <=P_PAGESIZE THEN
                  V_SELECT_SQL:='SELECT '||P_TABLECOLUMN||' FROM '||P_TABLENAME;
                  IF P_WHERE IS NOT NULL THEN
                    V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE;
                  END IF;
                  IF P_ORDER IS NOT NULL THEN
                      V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
                  END IF;
              ELSIF P_CURPAGE=1 THEN  --查询第一页
                  V_SELECT_SQL:='SELECT '||P_TABLECOLUMN||' FROM '||P_TABLENAME;
                  IF P_WHERE IS NOT NULL THEN
                    V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE||' AND ROWNUM <='||P_PAGESIZE;
                  ELSE
                    V_SELECT_SQL:=V_SELECT_SQL||' WHERE ROWNUM <='||P_PAGESIZE;
                  END IF;
                  IF P_ORDER IS NOT NULL THEN
                      V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
                  END IF;
              ELSE      --查询指定页
                  V_SELECT_SQL:='SELECT * FROM (SELECT '|| P_TABLECOLUMN ||',ROWNUM ROW_NUM FROM '|| P_TABLENAME;
                  IF P_WHERE IS NOT NULL THEN
                    V_SELECT_SQL:=V_SELECT_SQL||' WHERE '||P_WHERE;
                  END IF;
                  IF P_ORDER IS NOT NULL THEN
                      V_SELECT_SQL:=V_SELECT_SQL||' ORDER BY '||P_ORDER;
                  END IF;
                  V_SELECT_SQL:=V_SELECT_SQL||') WHERE ROW_NUM>'||((P_CURPAGE-1)*P_PAGESIZE)||' AND ROW_NUM <='||(P_CURPAGE*P_PAGESIZE);
              END IF;
              --执行查询
              DBMS_OUTPUT.PUT_LINE('查询语句=>'||V_SELECT_SQL);
              OPEN P_CURSOR FOR V_SELECT_SQL;
            END IF;      END PROC_PAGE;
END PACKAGE_PAGE;