ORACLE的SP如下,如何通过VB.NET来调用并取得返回值?

[amortal]

CREATE OR REPLACE TYPE STR_NEW_NUM_ARRAY AS TABLE OF VARCHAR2(11);

CREATE OR REPLACE PACKAGE TEST_PKG AS
PROCEDURE TestVarchar2(
IN_ARRAY OUT STR_NEW_NUM_ARRAY,
OUT_ARRAY OUT STR_NEW_NUM_ARRAY);
END TEST_PKG;

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
PROCEDURE TestVarchar2(OUT_ARRAY OUT STR_NEW_NUM_ARRAY);
END TEST_PKG;

CREATE OR REPLACE PACKAGE BODY TEST_PKG AS
PROCEDURE TestVarchar2(
IN_ARRAY OUT STR_NEW_NUM_ARRAY,
OUT_ARRAY OUT STR_NEW_NUM_ARRAY)
IS
ary_I_Team STR_NEW_NUM_ARRAY;
idx NUMBER DEFAULT 0;
BEGIN
idx := IN_ARRAY.COUNT;
ary_I_Team := STR_NEW_NUM_ARRAY();
ary_I_Team.EXTEND;
ary_I_Team(1) := '1234567890';
ary_I_Team.EXTEND;
ary_I_Team(2) := '1234567891';
OUT_ARRAY := ary_I_Team;
END TestVarchar2;
END TEST_PKG;

[timiil]

建议使用DAAB即DataAccessApplicationBlock访问～

[amortal]

有没有SRC参照,上面的SP比较简单,要是能按照上面的SP写个SAMPLE出来,就太感谢了,100分送上!

[amortal]

是高手没时间回答，还是真的没人懂啊？

[exing]

你要返回的是数组，这个我在网上也没有看到可用的资料，是比较麻烦

[amortal]

就是因为比较难解决，所以作为问题提出来了，希望大家帮帮忙啊！

[xiaowen01]

如果数据不是特别多，可以只返回一varchar,之间用“，”连接，在接收到这个之后再进行拆分

[amortal]

如果我的内容里面有[,]呢？
这个问题是不是没有人会啊？已经好多天了！
CSDN上的问题就只能到这个程度了吗？

[amortal]

一个多月了，问题尚未解决！

[机器人]

ODP.NET supports PL/SQL Associative Array (formerly known as PL/SQL Index-By Tables) binding.

An application can bind an OracleParameter, as a PL/SQL Associative Array, to a PL/SQL stored procedure. The following OracleParameter properties are used for this feature.

CollectionType

This property must be set to OracleCollectionType.PLSQLAssociativeArray to bind a PL/SQL Associative Array.

ArrayBindSize

This property is ignored for the fixed-length element types (such as Int32).

For variable-length element types (such as Varchar2), each element in the ArrayBindSize property specifies the size of the corresponding element in the Value property.

For Output parameters, InputOutput parameters, and return values, this property must be set for variable-length variables.

ArrayBindStatus

This property specifies the execution status of each element in the OracleParameter.Value property.

Size

This property specifies the maximum number of elements to be bound in the PL/SQL Associative Array.

Value

This property must either be set to an array of values or null or DBNull.Value.


Code Example

This example binds three OracleParameter objects as PL/SQL Associative Arrays: Param1 as an In parameter, Param2 as an InputOutput parameter, and Param3 as an Output parameter.

PL/SQL Package : My Pack

CREATE PACKAGE MYPACK AS
TYPE AssocArrayVarchar2_t is table of VARCHAR(20) index by BINARY_INTEGER;
PROCEDURE TestVarchar2(
Param1 IN AssocArrayVarchar2_t,
Param2 IN OUT AssocArrayVarchar2_t,
Param3 OUT AssocArrayVarchar2_t);
END MYPACK;

PL/SQL Package Body : My Pack

CREATE PACKAGE BODY MYPACK AS
PROCEDURE TestVarchar2(
Param1 IN AssocArrayVarchar2_t,
Param2 IN OUT AssocArrayVarchar2_t,
Param3 OUT AssocArrayVarchar2_t)
IS
i integer;
BEGIN
-- copy a few elements from y to z
Param3(1) := Param2(1);
Param3(2) := NULL;
Param3(3) := Param2(3);

-- copy all elements from x to y
Param2(1) := Param1(1);
Param2(2) := Param1(2);
Param2(3) := Param1(3);

FOR i IN 1..3 LOOP
insert into T1 values(i, Param2(i));
END LOOP;

FOR i IN 1..3 LOOP
select COL2 into Param2(i) from T2 where COL1 = i;
END LOOP;
END TestVarchar2;
END MYPACK;

ODP.NET Example

public void BindAssocArray(){
...

OracleCommand cmd = new OracleCommand(
"begin MyPack.TestVarchar2(:1, :2, :3); end;", con);

OracleParameter Param1 = cmd.Parameters.Add(...);
OracleParameter Param2 = cmd.Parameters.Add(...);
OracleParameter Param3 = cmd.Parameters.Add(...);

Param1.Direction = ParameterDirection.Input;
Param2.Direction = ParameterDirection.InputOutput;
Param3.Direction = ParameterDirection.Output;

// Specify that we are binding PL/SQL Associative Array
Param1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param2.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
Param3.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

// Setup the values for PL/SQL Associative Array
Param1.Value = new string[3]{"First Element",
"Second Element ",
"Third Element ",
Param2.Value = new string[3]{"First Element",
"Second Element ",
"Third Element ",
Param3.Value = null;

// Specify the maximum number of elements in the PL/SQL Associative Array
Param1.Size = 3;
Param2.Size = 3;
Param3.Size = 3;

// Setup the ArrayBindSize for Param1
Param1.ArrayBindSize = new int[3]{13, 14, 13};

// Setup the ArrayBindStatus for Param1
Param1.ArrayBindStatus = new OracleParameterStatus[3]{
OracleParameterStatus.Success,
OracleParameterStatus.Success,
OracleParameterStatus.Success};

// Setup the ArrayBindSize for Param2
Param2.ArrayBindSize = new int[3]{20, 20, 20};

// Setup the ArrayBindSize for Param3
Param3.ArrayBindSize = new int[3]{20, 20, 20};

// execute the cmd
cmd.ExecuteNonQuery();

//print out the parameter's values
...
}