CREATE OR REPLACE PROCEDURE BJ_PROC(cur_OUT OUT TEST_PKG.T_CURSOR,
V_COUNT OUT INTEGER,
V_FIELDS VARCHAR2,
V_CONDITIONSTR VARCHAR2,
V_TABLE VARCHAR2) AS
v_Statement varchar2(4000);
v_INTERNALFIELDS VARCHAR2(4000);
BEGIN
V_COUNT:=0;
SELECT DECODE(V_FIELDS,NULL,'*',V_FIELDS) INTO v_INTERNALFIELDS FROM DUAL;
IF LENGTH(TRIM(v_INTERNALFIELDS))=0 THEN
v_INTERNALFIELDS:='*';
END IF; IF LENGTH(TRIM(V_CONDITIONSTR))=0 THEN
v_Statement := 'SELECT ' || v_INTERNALFIELDS ||' FROM ' || V_TABLE;
ELSE
v_Statement := 'SELECT ' || v_INTERNALFIELDS ||' FROM ' || V_TABLE ||
' WHERE ' || V_CONDITIONSTR;
END IF;
open cur_OUT for v_Statement;
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20374, '语法错误');
V_COUNT:=-1;
END BJ_PROC;
.net 调用Oracle游标:
public static DataTable BJ_PROC(string V_FIELDS, string V_CONDITIONSTR, string V_TABLE)
{
OleDbParameter[] Param = new OleDbParameter[5]
{
new OleDbParameter("@cur_OUT",10),
new OleDbParameter("@V_COUNT",OleDbType.Single,100),
new OleDbParameter("@V_FIELDS",OleDbType.VarChar),
new OleDbParameter("@V_CONDITIONSTR",OleDbType.VarChar),
new OleDbParameter("@V_TABLE",OleDbType.VarChar)
}; Param[0].Direction = ParameterDirection.ReturnValue; Param[1].Direction = ParameterDirection.ReturnValue; Param[2].Value = V_FIELDS;
Param[2].Direction = ParameterDirection.Input; Param[3].Value = V_CONDITIONSTR;
Param[3].Direction = ParameterDirection.Input; Param[4].Value = V_TABLE;
Param[4].Direction = ParameterDirection.Input; DataTable s_value = DBHelper.GetDataSet("BJ_PROC", Param); return s_value;
}
public static DataTable GetDataSet(string proc_name,params OleDbParameter[] values)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(proc_name, Con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
con.Close();
con.Dispose();
return ds.Tables[0]; }出现错误如下:
ORA-06550: 第 1 行, 第 18 列:
PLS-00306: 调用 'BJ_PROC' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored请问如何解决?
V_COUNT OUT INTEGER,
V_FIELDS VARCHAR2,
V_CONDITIONSTR VARCHAR2,
V_TABLE VARCHAR2) AS
v_Statement varchar2(4000);
v_INTERNALFIELDS VARCHAR2(4000);
BEGIN
V_COUNT:=0;
SELECT DECODE(V_FIELDS,NULL,'*',V_FIELDS) INTO v_INTERNALFIELDS FROM DUAL;
IF LENGTH(TRIM(v_INTERNALFIELDS))=0 THEN
v_INTERNALFIELDS:='*';
END IF; IF LENGTH(TRIM(V_CONDITIONSTR))=0 THEN
v_Statement := 'SELECT ' || v_INTERNALFIELDS ||' FROM ' || V_TABLE;
ELSE
v_Statement := 'SELECT ' || v_INTERNALFIELDS ||' FROM ' || V_TABLE ||
' WHERE ' || V_CONDITIONSTR;
END IF;
open cur_OUT for v_Statement;
EXCEPTION
WHEN OTHERS THEN
Raise_application_error(-20374, '语法错误');
V_COUNT:=-1;
END BJ_PROC;
.net 调用Oracle游标:
public static DataTable BJ_PROC(string V_FIELDS, string V_CONDITIONSTR, string V_TABLE)
{
OleDbParameter[] Param = new OleDbParameter[5]
{
new OleDbParameter("@cur_OUT",10),
new OleDbParameter("@V_COUNT",OleDbType.Single,100),
new OleDbParameter("@V_FIELDS",OleDbType.VarChar),
new OleDbParameter("@V_CONDITIONSTR",OleDbType.VarChar),
new OleDbParameter("@V_TABLE",OleDbType.VarChar)
}; Param[0].Direction = ParameterDirection.ReturnValue; Param[1].Direction = ParameterDirection.ReturnValue; Param[2].Value = V_FIELDS;
Param[2].Direction = ParameterDirection.Input; Param[3].Value = V_CONDITIONSTR;
Param[3].Direction = ParameterDirection.Input; Param[4].Value = V_TABLE;
Param[4].Direction = ParameterDirection.Input; DataTable s_value = DBHelper.GetDataSet("BJ_PROC", Param); return s_value;
}
public static DataTable GetDataSet(string proc_name,params OleDbParameter[] values)
{
DataSet ds = new DataSet();
OleDbCommand cmd = new OleDbCommand(proc_name, Con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddRange(values);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
da.Fill(ds);
con.Close();
con.Dispose();
return ds.Tables[0]; }出现错误如下:
ORA-06550: 第 1 行, 第 18 列:
PLS-00306: 调用 'BJ_PROC' 时参数个数或类型错误
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored请问如何解决?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货