CREATE OR REPLACE package BODY pk_page
as
procedure p_wt(
mycs out mytype,
pk in varchar2,
px in varchar2,
n1 in integer,
n2 in integer,
sql_t in varchar2
)
as
sqltemp varchar2(2000);
begin
sqltemp:=sqltemp||'select tb1.* from';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||'select tb.*,row_number() over(order by '|| pk || ' '|| px ||')rn from';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||sql_t;
sqltemp:=sqltemp||')tb';
sqltemp:=sqltemp||')tb1';
sqltemp:=sqltemp||' where tb1.rn>='|| n1;
sqltemp:=sqltemp||' and tb1.rn<='|| n2;
open mycs for sqltemp; end p_wt;
end pk_page;
没有问题,
.net调用出错:
public DataSet pageRs(string sql,string pk,string px,int n1,int n2)
{
openConn();
OracleCommand ocmd = new OracleCommand("pk_page.p_wt",strcon);
OracleParameter p_pk = new OracleParameter("pk", OracleType.VarChar);
OracleParameter p_px = new OracleParameter("px", OracleType.VarChar);
OracleParameter p_n1 = new OracleParameter("n1", OracleType.Int32);
OracleParameter p_n2 = new OracleParameter("n2", OracleType.Int32);
OracleParameter p_sql_t = new OracleParameter("sql_t", OracleType.VarChar);
OracleParameter p_mycs = new OracleParameter("mycs", OracleType.Cursor);
p_pk.Direction = ParameterDirection.Input;
p_px.Direction = ParameterDirection.Input;
p_n1.Direction = ParameterDirection.Input;
p_n2.Direction = ParameterDirection.Input;
p_sql_t.Direction = ParameterDirection.Input;
p_mycs.Direction = ParameterDirection.Output;
p_pk.Value = pk;
p_px.Value = px;
p_n1.Value = n1;
p_n2.Value = n2;
p_sql_t.Value = sql;
ocmd.Parameters.Add(p_pk);
ocmd.Parameters.Add(p_px);
ocmd.Parameters.Add(p_n1);
ocmd.Parameters.Add(p_n2);
ocmd.Parameters.Add(p_sql_t);
ocmd.Parameters.Add(p_mycs); OracleDataAdapter da = new OracleDataAdapter(ocmd);
DataSet ds = new DataSet();
da.Fill(ds);//---------------ORA-01036: 非法的变量名/编号
closeConn();
return ds;
}
问题解决再追加100分。谢谢。
as
procedure p_wt(
mycs out mytype,
pk in varchar2,
px in varchar2,
n1 in integer,
n2 in integer,
sql_t in varchar2
)
as
sqltemp varchar2(2000);
begin
sqltemp:=sqltemp||'select tb1.* from';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||'select tb.*,row_number() over(order by '|| pk || ' '|| px ||')rn from';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||sql_t;
sqltemp:=sqltemp||')tb';
sqltemp:=sqltemp||')tb1';
sqltemp:=sqltemp||' where tb1.rn>='|| n1;
sqltemp:=sqltemp||' and tb1.rn<='|| n2;
open mycs for sqltemp; end p_wt;
end pk_page;
没有问题,
.net调用出错:
public DataSet pageRs(string sql,string pk,string px,int n1,int n2)
{
openConn();
OracleCommand ocmd = new OracleCommand("pk_page.p_wt",strcon);
OracleParameter p_pk = new OracleParameter("pk", OracleType.VarChar);
OracleParameter p_px = new OracleParameter("px", OracleType.VarChar);
OracleParameter p_n1 = new OracleParameter("n1", OracleType.Int32);
OracleParameter p_n2 = new OracleParameter("n2", OracleType.Int32);
OracleParameter p_sql_t = new OracleParameter("sql_t", OracleType.VarChar);
OracleParameter p_mycs = new OracleParameter("mycs", OracleType.Cursor);
p_pk.Direction = ParameterDirection.Input;
p_px.Direction = ParameterDirection.Input;
p_n1.Direction = ParameterDirection.Input;
p_n2.Direction = ParameterDirection.Input;
p_sql_t.Direction = ParameterDirection.Input;
p_mycs.Direction = ParameterDirection.Output;
p_pk.Value = pk;
p_px.Value = px;
p_n1.Value = n1;
p_n2.Value = n2;
p_sql_t.Value = sql;
ocmd.Parameters.Add(p_pk);
ocmd.Parameters.Add(p_px);
ocmd.Parameters.Add(p_n1);
ocmd.Parameters.Add(p_n2);
ocmd.Parameters.Add(p_sql_t);
ocmd.Parameters.Add(p_mycs); OracleDataAdapter da = new OracleDataAdapter(ocmd);
DataSet ds = new DataSet();
da.Fill(ds);//---------------ORA-01036: 非法的变量名/编号
closeConn();
return ds;
}
问题解决再追加100分。谢谢。
sqltemp:=sqltemp||'select tb1.* from ';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||'select tb.*,row_number() over(order by '|| pk ||','|| px ||') rn from ';
sqltemp:=sqltemp||sql_t||' tb';
sqltemp:=sqltemp||') tb1';
sqltemp:=sqltemp||' where tb1.rn>='|| n1;
sqltemp:=sqltemp||' and tb1.rn<='|| n2;
open mycs for sqltemp;
--dbms_output.put_line(sqltemp);
order by id desc
或者
order by id asc
而不是
order by id , desc
我的
sql_t 是个 sql语句。
忘了说明了,我传的参数可以是:
pk 是 "id"
px 是 "desc"或者"asc"
n1 是 1
n2 是 20
sql_t 是sql语句例如: "select * from t_student"
我已经调试过,存储过程应该没有问题的。
是不是我调试的有问题? 能不能.net与存储过程联合起来调试呢?我调试是单调的存储过程,没有问题
--我是看你贴出来的拼接sql 打印出来 少了好多空格
sqltemp:=sqltemp||'select tb1.* from ';
sqltemp:=sqltemp||'(';
sqltemp:=sqltemp||'select tb.*,row_number() over(order by '|| pk ||' '|| px ||') rn from ';
sqltemp:=sqltemp||sql_t||' tb';
sqltemp:=sqltemp||') tb1';
sqltemp:=sqltemp||' where tb1.rn>='|| n1;
sqltemp:=sqltemp||' and tb1.rn<='|| n2;
open mycs for sqltemp;
你mytype应该是动态游标类refcursor吧?看了下你包没有什么错,可能在于你.net调用时候参数问题
会不会你是参数顺序错了,包过程中返回的动态游标参数是第一个,你ocmd.Parameters.Add(p_mycs);放在最后了
你改了试试呢
哪里缺少空格?
调试存储过程的sql语句是这样的。
select tb1.* from(select tb.*,row_number() over(order by id desc)rn from(select * from t_student)tb)tb1 where tb1.rn>=1 and tb1.rn<=20
public int totalRec(string sql) {
OracleCommand ocmd = new OracleCommand();
OracleParameter op = new OracleParameter("sql_str", OracleType.VarChar);
OracleParameter op1 = new OracleParameter();
op1.OracleType = OracleType.Number;
op1.Direction = ParameterDirection.ReturnValue;
op.Value = sql;
ocmd.Parameters.Add(op);
ocmd.Parameters.Add(op1);
ocmd.Connection = strcon;
ocmd.CommandText = "f_page_totalRec";
ocmd.CommandType = CommandType.StoredProcedure;
openConn();
ocmd.ExecuteNonQuery();
closeConn();
return int.Parse(op1.Value.ToString());
}
public DataSet pageRs(string sql,string pk,string px,int n1,int n2)
{
openConn();
OracleCommand ocmd = new OracleCommand("pk_page.p_wt",strcon);
OracleParameter p_pk = new OracleParameter(":pk", OracleType.VarChar);
OracleParameter p_px = new OracleParameter(":px", OracleType.VarChar);
OracleParameter p_n1 = new OracleParameter(":n1", OracleType.Number);
OracleParameter p_n2 = new OracleParameter(":n2", OracleType.Number);
OracleParameter p_sql_t = new OracleParameter(":sql_t", OracleType.VarChar);
OracleParameter p_mycs = new OracleParameter(":mycs", OracleType.Cursor);
p_pk.Direction = ParameterDirection.Input;
p_px.Direction = ParameterDirection.Input;
p_n1.Direction = ParameterDirection.Input;
p_n2.Direction = ParameterDirection.Input;
p_sql_t.Direction = ParameterDirection.Input;
p_mycs.Direction = ParameterDirection.Output;
p_pk.Value = pk;
p_px.Value = px;
p_n1.Value = n1;
p_n2.Value = n2;
p_sql_t.Value = sql;
ocmd.Parameters.Add(p_mycs);
ocmd.Parameters.Add(p_pk);
ocmd.Parameters.Add(p_px);
ocmd.Parameters.Add(p_n1);
ocmd.Parameters.Add(p_n2);
ocmd.Parameters.Add(p_sql_t);
OracleDataAdapter da = new OracleDataAdapter(ocmd);
DataSet ds = new DataSet();
da.Fill(ds,"t_student");
closeConn();
return ds;
}
包体是这样的:
create or replace package pk_page
is
type mytype is ref cursor;
procedure p_wt(
mycs out mytype,
pk in varchar2,
px in varchar2,
n1 in number,
n2 in number,
sql_t in varchar2
);
end;
也是同样的错误
是这个 pageRs 函数
http://blog.csdn.net/wangyihust/archive/2005/05/21/377721.aspx
da.Fill(ds,"test");
test是个表名。
我是个动态sql语句。
:pk 把冒号去掉看看
OracleCommand ocmd = new OracleCommand("[color=#FF6600pk_page.p_wt][/color]",strcon);