在C#中使用varchar2使用PL/SQL assoc数组索引 [英] using PL/SQL assoc array index by varchar2 in C#
问题描述
我有PL/SQL代码:
I have PL/SQL code:
create or replace
package TEST_PKG2 as
type AssocArray is table of varchar2(20) index by binary_integer;
type AssocArray_varchar is table of varchar2(20) index by varchar2(10);
--type AssocArray_varchar is table of varchar2(20) index by integer;
function assoc_arry_return(Param1 in AssocArray) return AssocArray_varchar;
end TEST_PKG2;
create or replace
package body TEST_PKG2 as
function assoc_arry_return(Param1 in AssocArray) return AssocArray_varchar
is
v_return_value AssocArray_varchar;
begin
v_return_value('name1'):=Param1(1);
v_return_value('name2'):=Param1(2);
v_return_value('name3'):=Param1(3);
return v_return_value;
end assoc_arry_return;
end TEST_PKG2;
控制代码以检查pl/sql:
controle code to check pl/sql:
set serveroutput on
declare
assoc_array test_pkg2.AssocArray;
assoc_array_return test_pkg2.AssocArray_varchar;
begin
assoc_array(1):='test1';
assoc_array(2):='test2';
assoc_array(3):='test3';
assoc_array_return:= test_pkg2.assoc_arry_return(assoc_array);
dbms_output.put_line(assoc_array_return.first||' - '||assoc_array_return(assoc_array_return.first));
end;
和用于执行此功能的C#代码:
and C# code for execute this function:
command = new OracleCommand("TEST_PKG2.assoc_arry_return", OracleConnection);
command.CommandType = CommandType.StoredProcedure;
OracleParameter return_value = new OracleParameter();
return_value.OracleDbType = OracleDbType.Varchar2;
return_value.Direction = ParameterDirection.ReturnValue;
return_value.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
return_value.Size=3;
return_value.ArrayBindSize = new int[3] { 20, 20, 20 };
command.Parameters.Add(return_value);
OracleParameter Param4 = command.Parameters.Add("Param1", OracleDbType.Varchar2);
Param4.Direction = ParameterDirection.Input;
Param4.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param4.Value = new string[3] { "First Element", "Second Element", "Third Element" };
Param4.Size = 3;
Param4.ArrayBindSize = new int[3] { 20, 20, 20 };
Param4.ArrayBindStatus = new OracleParameterStatus[3] { OracleParameterStatus.Success, OracleParameterStatus.Success, OracleParameterStatus.Success };
command.ExecuteNonQuery();
当我用thic代码执行应用时,执行死于最后一行,错误为"ORA-06550:第1行,第15列: PLS-00382:表达式的类型错误 ORA-06550:第1行,第7列: PL/SQL:语句被忽略"
When I execute app with thic code, execution dies on last line with error "ORA-06550: Line 1, column 15: PLS-00382: expression is of wrong type ORA-06550: Line 1, column 7: PL/SQL: Statement ignored"
当我使用由varchar2索引的assoc数组时,这是一种特殊情况. 当我按整数类型的包中的返回类型(通过注释行代码id的包)更改索引时,一切正常.
This is a special case, when I use assoc array indexed by varchar2. when I change index by of return type in package on integer (commented out line code id package), all is OK.
您知道如何在C#中使用此程序包吗?
Do you have any idea how use this package in C#?
谢谢
推荐答案
所以我尝试使用数据类型对象.
So I tried to use a data type object.
第一步,我创建data_type对象
In first step I create data_type object
create or replace type STB_JIG_TARLOG_VALUE as object
(
NAME varchar2(300),
VALUE varchar2(500)
)
和
create or replace type
STB_JIG_TARLOG_VALUES is table of STB_JIG_TARLOG_VALUE
它们不是包装的一部分.
They are not part of package.
我现在也可以将此类型用作函数的返回类型:
I can now use this type also as the return type of the function:
function get_data(...) return STB_JIG_TARLOG_VALUES
is
items STB_JIG_TARLOG_VALUES := STB_JIG_TARLOG_VALUES();
begin
items.extend;
items(1):= STB_JIG_TARLOG_VALUE('key_name','key_value');
...
end get_data;
最后,我们只需选择以下内容即可得到返回值:
and finally we get the return value simply select:
select * from table (get_data(...));
列名称在STB_JIG_TARLOG_VALUE中定义,并且将值添加到返回值的方式可以不同(例如,循环...)
Names of columns are defined in STB_JIG_TARLOG_VALUE and adding value to the return value can be different ways (for example loop...)
DataReader不会从此查询中获取数据.
And get data from this query is not problem with DataReader.
这篇关于在C#中使用varchar2使用PL/SQL assoc数组索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!