调用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;
执行后出现这个错误:

浙公网安备 33010602011771号