以下是.net调用过程部分strConn = ConfigurationSettings.AppSettings["ConnStr"];
conn = new OleDbConnection(strConn);
conn.Open(); cmd = new OleDbCommand("pkg_web_manager.bar_transfers_details",conn);
cmd.CommandType = CommandType.StoredProcedure; paraUserLoginID = new OleDbParameter("i_user_loginid",OleDbType.VarChar,30);
paraUserLoginID.Value = strUserLoginID;
cmd.Parameters.Add(paraUserLoginID); paraUserPwd = new OleDbParameter("i_user_loginpass",OleDbType.VarChar,30);
paraUserPwd.Value = strUserPwd;
cmd.Parameters.Add(paraUserPwd);cmd.ExecuteNonQuery();
decTransAmount = paraTransAmount.Value.ToString();
strFlag = paraFlag.Value.ToString();
cmd.Parameters.Clear();
conn.Close();
conn = new OleDbConnection(strConn);
conn.Open(); cmd = new OleDbCommand("pkg_web_manager.bar_transfers_details",conn);
cmd.CommandType = CommandType.StoredProcedure; paraUserLoginID = new OleDbParameter("i_user_loginid",OleDbType.VarChar,30);
paraUserLoginID.Value = strUserLoginID;
cmd.Parameters.Add(paraUserLoginID); paraUserPwd = new OleDbParameter("i_user_loginpass",OleDbType.VarChar,30);
paraUserPwd.Value = strUserPwd;
cmd.Parameters.Add(paraUserPwd);cmd.ExecuteNonQuery();
decTransAmount = paraTransAmount.Value.ToString();
strFlag = paraFlag.Value.ToString();
cmd.Parameters.Clear();
conn.Close();
解决方案 »
- 高分 请教各位大虾这种递归结构该如何设计表?
- 执行下面的操作有什么办法?
- 存储过程中的exec
- 我有一段指令在sqlform30中运行,有时可以通过,有时不能通过,在sqlplus下没有问题,请问是什么原因?谢谢 请问是回推段的问题吗?如果
- 日期类型判断的怪问题
- EXECUTE IMMEDIATE 'RENAME 旧表名 TO 新表名' 这个语句可以进行事务处理吗?
- 数据库服务器A和数据库服务器B的内容是完全一样的,怎么样才能达到两数据库的同步更新?
- 一个简单的存储过程调用的参数问题
- 这样的要求该怎样授权?
- 如何给pl/sql程序传参?
- 如何在存储过程中返回结果集?
- 一个从临时表将数据导入到正式表的脚本
不过我更加需要的是相应的Oracle存储过程。
public System.Data.DataTable dt = null;
da = new OleDbDataAdapter();
da.SelectCommand = cmd; dt = new DataTable("dttable");
da.Fill(dt);
conn.Close();这样你就可以将结果集填充到DataTable中了
CREATE OR REPLACE PACKAGE pkg_queryrmex
AS
TYPE myrctype IS REF CURSOR;
PROCEDURE get(iv_STCD in char, iv_dtBegin in date, iv_dtEnd in date, ov_rc OUT myrctype);
END pkg_queryrmex;
/CREATE OR REPLACE PACKAGE BODY pkg_queryrmex
AS
PROCEDURE get(iv_STCD in char, iv_dtBegin in date, iv_dtEnd in date, ov_rc OUT myrctype)
IS
strSQL VARCHAR2 (1000);
BEGIN
strSQL := 'select * from st_rmex_r where stcd='||iv_STCD||' and YMDHM>='||iv_dtBegin||' and YMDHM<='||iv_dtEnd;
open ov_rc for strSQL;
END get;
END pkg_queryrmex;
然后在.Net里面调用:
DateTime dtBegin = new DateTime(Convert.ToInt32(tbBeginYear.Text),
Convert.ToInt32(tbBeginMonth.Text),
Convert.ToInt32(tbBeginDay.Text), 0, 0, 0, 0);
DateTime dtEnd = new DateTime(Convert.ToInt32(tbEndYear.Text),
Convert.ToInt32(tbEndMonth.Text),
Convert.ToInt32(tbEndDay.Text), 23, 59, 59, 0);
//Procedure
OracleCommand cmd = new OracleCommand("pkg_queryrmex.get", connOracleTest);
cmd.CommandType = CommandType.StoredProcedure;
//Parametes
//iv_STCD
OracleParameter parSTCD = new OracleParameter("iv_STCD", OracleType.Char);
parSTCD.Direction = ParameterDirection.Input;
parSTCD.Value = tbSTCD.Text;
cmd.Parameters.Add(parSTCD);
//iv_dtBegin
OracleParameter pardtBegin = new OracleParameter("iv_dtBegin", OracleType.DateTime);
pardtBegin.Direction = ParameterDirection.Input;
pardtBegin.Value = new OracleDateTime(dtBegin);
cmd.Parameters.Add(pardtBegin);
//iv_dtEnd
OracleParameter pardtEnd = new OracleParameter("iv_dtEnd", OracleType.DateTime);
pardtEnd.Direction = ParameterDirection.Input;
pardtEnd.Value = new OracleDateTime(dtEnd);
cmd.Parameters.Add(pardtEnd);
OracleParameter parRC = new OracleParameter("ov_rc", OracleType.Cursor);
parRC.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parRC);
//OracleDataAdapter
OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds, "ST_RMEX_R");
dgSYQ.DataSource = ds.Tables["ST_RMEX_R"].DefaultView;
dgSYQ.DataBind();
可是老是报错:
ORA-00933: SQL 命令未正确结束 ORA-06512: 在"WUWEI.PKG_QUERYRMEX", line 8 ORA-06512: 在line 1
麻烦帮我看一下。谢谢!
open ov_rc for strSQL;改成:
open ov_rc for select * from st_rmex_r
where stcd=iv_STCD and YMDHM>=iv_dtBegin and YMDHM<=iv_dtEnd;