code从C#和ODP.NET调用一个函数在一个包 [英] Code for calling a function in a package from C# and ODP.NET
问题描述
我试着写C#code。与ODP.NET调用一个函数在一个包。我得到以下两个错误:
ORA-06550:第1行,第7列:
PLS-00306:错号码或呼叫INSERT_FUNC类型的参数
ORA-06550:第1行,第7列:
PL / SQL:语句被忽略ORA-06550:第1行,第7列:
PLS-00221:'INSERT_FUNC'不是一个过程或未定义
ORA-06550:第1行,第7列:
PL / SQL:语句被忽略
我的的OracleCommand
设置为:
cmd.CommandText =PKG_NAME.INSERT_FUNC;
cmd.CommandType = CommandType.StoredProcedure;
- 我应该如何传递参数给下面的功能?
- 请我需要添加一个
返回值
参数?我见过很多论坛讨论需要在<$ C $添加返回值
参数作为第一个的OracleParameter
C>的OracleCommand 对象。
我想AP preciate任何建议。
CREATE OR REPLACE
PACKAGE BODY程序包名称IS
功能insert_func(
i_description在tableName.description%TYPE,
i_theme在tableName.theme%TYPE,
o_id OUT tableName.id%TYPE,
o_error_msg OUT VARCHAR2)
RETURN NUMBER
IS
l_program VARCHAR2(100):='PKG_NAME.INSERT_FUNC';
开始
INSERT INTO表A(EVENT_ID,ID,描述,主题,lock_version)
VALUES(rms12.tablea_seq.NEXTVAL,rms12.tablea_id_seq.NEXTVAL,i_description,i_theme,NULL);
INSERT INTO表B(ID,描述,主题)
VALUES(rms12.id_seq.CURRVAL,i_description,i_theme);
SELECT rms12.id_seq.CURRVAL
INTO o_id
FROM DUAL;
返回1;
例外
WHEN OTHERS THEN
o_error_msg:= sql_lib.create_msg(
PACKAGE_ERROR',SQLERRM,l_program,TO_CHAR(SQL code));
返回0;
END insert_func;
END程序包名称;
这是我在这个论坛的第一个问题,我很高兴地张贴到我自己的答案。
我们可以通过设置 CommandType.StoredProcedure
呼叫使用ODP.NET的Oracle软件包的功能。
ORA-06550:第1行,第7列:
PLS-00221:'INSERT_FUNC'不是一个过程或未定义
ORA-06550:第1行,第7列:PL / SQL:语句被忽略
如果你得到这个错误,只需添加这条线的命令对象上的第一个参数:
cmd.Parameters.Add(RETURN_VALUE,OracleDbType.Int16,
ParameterDirection.ReturnValue);
下面是工作code:
使用(VAR康恩=新的OracleConnection(ORADB))
使用(VAR CMD = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText =PKG_NAME.INSERT_FUNC; cmd.BindByName =真; cmd.Parameters.Add(RETURN_VALUE,OracleDbType.Int16,
ParameterDirection.ReturnValue);
cmd.Parameters.Add(i_description,OracleDbType.Varchar2,1000,
promotionEventSetupDetails.PromotionDescription,
ParameterDirection.Input);
cmd.Parameters.Add(i_theme,OracleDbType.Varchar2,80
promotionEventSetupDetails.PromotionTheme,
ParameterDirection.Input);
cmd.Parameters.Add(o_id,OracleDbType.Varchar2,
ParameterDirection.Output);
cmd.Parameters.Add(o_error_msg,OracleDbType.Varchar2,
ParameterDirection.Output); conn.Open();
使用(VAR博士= cmd.ExecuteReader())
{
//做一些工作在这里
}
}
I've tried to write C# code with ODP.NET to call a function in a package. I'm getting the two errors below:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to INSERT_FUNC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
ORA-06550: line 1, column 7:
PLS-00221: 'INSERT_FUNC' is not a procedure or is undefined
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
My OracleCommand
is set up as:
cmd.CommandText = "PKG_NAME.INSERT_FUNC";
cmd.CommandType = CommandType.StoredProcedure;
- How should I pass parameters to the function below?
- Do I need to add a
ReturnValue
parameter? I've seen many forums discussing the need to add aReturnValue
parameter as the firstOracleParameter
on theOracleCommand
object.
I would appreciate any suggestions.
CREATE OR REPLACE
PACKAGE BODY pkg_name IS
FUNCTION insert_func (
i_description IN tableName.description%TYPE,
i_theme IN tableName.theme%TYPE,
o_id OUT tableName.id%TYPE,
o_error_msg OUT VARCHAR2 )
RETURN NUMBER
IS
l_program VARCHAR2(100) := 'PKG_NAME.INSERT_FUNC';
BEGIN
INSERT INTO tablea ( event_id, id, description, theme, lock_version )
VALUES ( rms12.tablea_seq.NEXTVAL, rms12.tablea_id_seq.NEXTVAL, i_description, i_theme, NULL );
INSERT INTO tableb ( id, description, theme )
VALUES ( rms12.id_seq.CURRVAL, i_description, i_theme );
SELECT rms12.id_seq.CURRVAL
INTO o_id
FROM dual;
RETURN 1;
EXCEPTION
WHEN OTHERS THEN
o_error_msg := sql_lib.create_msg(
'PACKAGE_ERROR', SQLERRM, l_program, TO_CHAR( SQLCODE ) );
RETURN 0;
END insert_func;
END pkg_name;
This is my first question on this forum and I am happy to post to my own answer.
We can call an oracle package function using ODP.NET by setting CommandType.StoredProcedure
.
ORA-06550: line 1, column 7:
PLS-00221: 'INSERT_FUNC' is not a procedure or is undefined
ORA-06550: line 1, column 7: PL/SQL: Statement ignored
If you get this error, just add this line as the first parameter on the command object:
cmd.Parameters.Add("Return_Value", OracleDbType.Int16,
ParameterDirection.ReturnValue);
Here is the working code:
using (var conn = new OracleConnection(oradb))
using (var cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "PKG_NAME.INSERT_FUNC";
cmd.BindByName = true;
cmd.Parameters.Add("Return_Value", OracleDbType.Int16,
ParameterDirection.ReturnValue);
cmd.Parameters.Add("i_description", OracleDbType.Varchar2, 1000,
promotionEventSetupDetails.PromotionDescription,
ParameterDirection.Input);
cmd.Parameters.Add("i_theme", OracleDbType.Varchar2, 80,
promotionEventSetupDetails.PromotionTheme,
ParameterDirection.Input);
cmd.Parameters.Add("o_id", OracleDbType.Varchar2,
ParameterDirection.Output);
cmd.Parameters.Add("o_error_msg", OracleDbType.Varchar2,
ParameterDirection.Output);
conn.Open();
using (var dr = cmd.ExecuteReader())
{
// do some work here
}
}
这篇关于code从C#和ODP.NET调用一个函数在一个包的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!