Home » SQL & PL/SQL » SQL & PL/SQL » Joining on optional TABLE OF NUMBER parameters (Oracle 11G)
 Joining on optional TABLE OF NUMBER parameters [message #646368] |
Mon, 28 December 2015 17:18  |
 |
Sam D.
Messages: 17
Registered: December 2015
Location: USA
|
Junior Member |
|
|
Hi! I'd like to know the best way to deal with the situation of having several TABLE OF NUMBER parameters of variable length passed to a function.
Some of these parameters can have zero elements, so we need to return all elements.
1) Sample table:
CREATE TABLE TRX_EVENTS
(
EVENT_ID NUMBER NOT NULL,
EVENT_NAME VARCHAR2(48 BYTE) NOT NULL,
EVENT_USER VARCHAR2(50 BYTE),
EVENT_TIME TIMESTAMP(2),
EVENT_SOURCE_ID NUMBER,
EVENT_LOCATION_ID NUMBER,
EVENT_ORIGINATOR_ID NUMBER,
EVENT_STATUS_ID NUMBER,
EVENT_DESC VARCHAR2(128 BYTE),
EVENT_EDIT_TIME TIMESTAMP(2),
EVENT_EDIT_USER NUMBER
)
2) Sample package:
CREATE OR REPLACE TYPE IDTYPE AS OBJECT
(
id NUMBER
);
/
CREATE OR REPLACE TYPE IDTABLETYPE AS TABLE OF IDTYPE;
/
CREATE OR REPLACE PACKAGE EVENT_VIEW_PACKAGE
AS
TYPE intTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN intTableType;
p_MyLocationIDList IN intTableType;
p_MyOriginatorIDList IN intTableType;
p_MyStatusIDList IN intTableType;
)RETURN sys_refcursor;
END;
/
CREATE OR REPLACE PACKAGE BODY EVENT_VIEW_PACKAGE
AS
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN intTableType;
p_MyLocationIDList IN intTableType;
p_MyOriginatorIDList IN intTableType;
p_MyStatusIDList IN intTableType;
)
RETURN sys_refcursor
AS
v_refcur sys_refcursor;
iMyIDList1 IDTABLETYPE;
iMyIDList2 IDTABLETYPE;
iMyIDList3 IDTABLETYPE;
iMyIDList4 IDTABLETYPE;
BEGIN
-- Create tables to join on. I was told not to use cursors or dynamic sql.
iMyIDList1 := IDTABLETYPE();
iMyIDList1.Extend(p_MySourceIDList.count);
FOR i IN p_MySourceIDList.First .. p_MySourceIDList.Last
LOOP
iMyIDList1(i) := IDTYPE(p_MySourceIDList(i));
END LOOP;
iMyIDList2 := IDTABLETYPE();
iMyIDList2.Extend(p_MyLocationIDList.count);
FOR i IN p_MyLocationIDList.First .. p_MyLocationIDList.Last
LOOP
iMyIDList2(i) := IDTYPE(p_MyLocationIDList(i));
END LOOP;
iMyIDList3 := IDTABLETYPE();
iMyIDList3.Extend(p_MyOriginatorIDList.count);
FOR i IN p_MyOriginatorIDList.First .. p_MyOriginatorIDList.Last
LOOP
iMyIDList3(i) := IDTYPE(p_MyOriginatorIDList(i));
END LOOP;
iMyIDList4 := IDTABLETYPE();
iMyIDList4.Extend(p_MyStatusIDList.count);
FOR i IN p_MyStatusIDList.First .. p_MyStatusIDList.Last
LOOP
iMyIDList4(i) := IDTYPE(p_MyStatusIDList(i));
END LOOP;
-- This query should join on 1 or more lists of IDs. If IDs are not passed in the TABLE OF NUMBER, then ALL records are wanted, so we should not join
OPEN v_refcur FOR
SELECT * FROM TRX_EVENTS ev
JOIN (SELECT id FROM TABLE(iMyIDList1)) IdFilter1 ON ev.EVENT_SOURCE_ID = IdFilter1.id
JOIN (SELECT id FROM TABLE(iMyIDList2)) IdFilter2 ON ev.EVENT_LOCATION_ID = IdFilter2.id
JOIN (SELECT id FROM TABLE(iMyIDList3)) IdFilter3 ON ev.EVENT_ORIGINATOR_ID = IdFilter3.id
JOIN (SELECT id FROM TABLE(iMyIDList4)) IdFilter4 ON ev.EVENT_STATUS_ID = IdFilter4.id
RETURN(v_refcur);
END FUNC_GET_EVENTS;
/
3) Data population function:
CREATE OR REPLACE
FUNCTION POPULATE_TEST_DATA RETURN VARCHAR2 AS
MAX_NUMBER_OF_EVENTS CONSTANT NUMBER (10) := 500; /* This number can be 2-3 mln */
BEGIN
DBMS_RANDOM.seed (MAX_NUMBER_OF_EVENTS);
FOR i IN 1..MAX_NUMBER_OF_EVENTS
LOOP
INSERT INTO TRX_EVENTS
(
EVENT_ID
,EVENT_NAME
,EVENT_USER
,EVENT_TIME
,EVENT_SOURCE_ID
,EVENT_LOCATION_ID
,EVENT_ORIGINATOR_ID
,EVENT_STATUS_ID
,EVENT_DESC
,EVENT_EDIT_TIME
,EVENT_EDIT_USER
)
VALUES
(
i -- EVENT_ID - NUMBER NOT NULL
,'EVENT_NAME ' || i
,'User ' || (select TRUNC(DBMS_RANDOM.value(1,25)) AS small_number from DUAL)
,(select TO_DATE(TRUNC( DBMS_RANDOM.VALUE(TO_CHAR(DATE '2010-01-01','J'),TO_CHAR(DATE '2015-12-31','J'))),'J') FROM DUAL)
,(select TRUNC(DBMS_RANDOM.value(1,50)) AS small_number from DUAL)
,(select TRUNC(DBMS_RANDOM.value(1,25)) AS small_number from DUAL)
,(select TRUNC(DBMS_RANDOM.value(1,75)) AS small_number from DUAL)
,(select TRUNC(DBMS_RANDOM.value(-1,3)) AS small_number from DUAL)
,'Event Description ' || (select DBMS_RANDOM.string('L', 5) str from DUAL)
,SYSDATE -- EVENT_EDIT_TIME - TIMESTAMP(2)
,(select TRUNC(DBMS_RANDOM.value(1,3)) AS small_number from DUAL)
);
END LOOP;
COMMIT;
RETURN 'FINISHED';
END POPULATE_TEST_DATA;
Execute the data population function:
TRUNCATE TABLE TRX_EVENTS;
DECLARE
Result VARCHAR2(32767);
BEGIN
Result := LSEDBA.POPULATE_TEST_DATA;
END;
Infinite thanks in advance
[Updated on: Mon, 28 December 2015 17:23] Report message to a moderator |
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646369 is a reply to message #646368] |
Mon, 28 December 2015 18:20  |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Just throw all that PL/SQL away. Throw away type definition too.
All that code is nothing but:
SELECT *
FROM TRX_EVENTS ev
JOIN (SELECT column_value id FROM TABLE(sys.OdciNumberList(p_MySourceIDList-elements))) IdFilter1 ON ev.EVENT_SOURCE_ID = IdFilter1.id
JOIN (SELECT column_value id FROM TABLE(sys.OdciNumberList(p_MyLocationIDList-elements))) IdFilter2 ON ev.EVENT_LOCATION_ID = IdFilter2.id
JOIN (SELECT column_value id FROM TABLE(sys.OdciNumberList(p_MyOriginatorIDList-elements))) IdFilter3 ON ev.EVENT_ORIGINATOR_ID = IdFilter3.id
JOIN (SELECT column_value id FROM TABLE(sys.OdciNumberList(p_MyStatusIDList-elements))) IdFilter4 ON ev.EVENT_STATUS_ID = IdFilter4.id
/
Or, if you create nested table type:
CREATE OR REPLACE
TYPE IDTABLETYPE
AS TABLE OF NUMBER;
/
SELECT *
FROM TRX_EVENTS ev
WHERE ev.EVENT_SOURCE_ID MEMBER OF IDTABLETYPE(p_MySourceIDList-elements)
AND ev.EVENT_LOCATION_ID MEMBER OF IDTABLETYPE(p_MyLocationIDList-elements)
AND ev.EVENT_ORIGINATOR_ID MEMBER OF IDTABLETYPE(p_MyOriginatorIDList-elements)
AND ev.EVENT_STATUS_ID MEMBER OF IDTABLETYPE(p_MyStatusIDList-elements)
/
However MEMBER OF can be slower.
SY.
|
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646370 is a reply to message #646369] |
Mon, 28 December 2015 18:28 |
|
Sam D.
Messages: 17
Registered: December 2015
Location: USA
|
Junior Member |
|
|
Thank you for taking a look. It's definitely going to help me.
I am not sure what this syntax is " p_MySourceIDList-elements ". What's -element?
I guess my other question is this:
Let's say the user did NOT pass any ids for Source ID and Location ID implying that all records should be returned.
The user did pass Originator IDs and Status IDs implying that he wants only records with certain Originator and Status IDs.
How can I structure the query ?
Many thanks in advance,
M
[Updated on: Mon, 28 December 2015 18:28] Report message to a moderator |
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646371 is a reply to message #646370] |
Mon, 28 December 2015 18:37 |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Sam D. wrote on Mon, 28 December 2015 19:28
Let's say the user did NOT pass any ids for Source ID and Location ID implying that all records should be returned.
Then you could use something like:
SELECT *
FROM TRX_EVENTS ev
WHERE (ev.EVENT_SOURCE_ID MEMBER OF IDTABLETYPE(p_MySourceIDList-elements) OR IDTABLETYPE(p_MySourceIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_LOCATION_ID MEMBER OF IDTABLETYPE(p_MyLocationIDList-elements) OR IDTABLETYPE(p_MyLocationIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_ORIGINATOR_ID MEMBER OF IDTABLETYPE(p_MyOriginatorIDList-elements) OR IDTABLETYPE(p_MyOriginatorIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_STATUS_ID MEMBER OF IDTABLETYPE(p_MyStatusIDList-elements) OR IDTABLETYPE(p_MyStatusIDList-elements) = IDTABLETYPE())
/
For example, user passed 3-element nested table idtabletype(7369,7654,7876):
SQL> select * from emp
2 where empno member of idtabletype(7369,7654,7876) or idtabletype(7369,7654,7876) = idtabletype()
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12-1980 800 20
7654 MARTIN SALESMAN 7698 28-09-1981 1250 1400 30
7876 ADAMS CLERK 7788 23-05-1987 1100 20
SQL>
And select selected requested employees. Now user passed empty nested table idtabletype():
SQL> select * from emp
2 where empno member of idtabletype() or idtabletype() = idtabletype()
3 /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-12-1980 800 20
7499 ALLEN SALESMAN 7698 20-02-1981 1600 300 30
7521 WARD SALESMAN 7698 22-02-1981 1250 500 30
7566 JONES MANAGER 7839 02-04-1981 2975 20
7654 MARTIN SALESMAN 7698 28-09-1981 1250 1400 30
7698 BLAKE MANAGER 7839 01-05-1981 2850 30
7782 CLARK MANAGER 7839 09-06-1981 2450 10
7788 SCOTT ANALYST 7566 19-04-1987 3000 20
7839 KING PRESIDENT 17-11-1981 5000 10
7844 TURNER SALESMAN 7698 08-09-1981 1500 0 30
7876 ADAMS CLERK 7788 23-05-1987 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ---------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-12-1981 950 30
7902 FORD ANALYST 7566 03-12-1981 3000 20
7934 MILLER CLERK 7782 23-01-1982 1300 10
14 rows selected.
SQL>
And same select returned all employees.
So you can build pipelined function around that SQL.
SY.
|
|
|
|
|
|
|
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646577 is a reply to message #646576] |
Mon, 04 January 2016 16:27 |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Use:
CREATE OR REPLACE
TYPE IDTABLETYPE
AS TABLE OF NUMBER;
/
CREATE OR REPLACE PACKAGE EVENT_VIEW_PACKAGE
AS
TYPE intTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN IDTABLETYPE,
p_MyLocationIDList IN IDTABLETYPE,
p_MyOriginatorIDList IN IDTABLETYPE,
p_MyStatusIDList IN IDTABLETYPE
)RETURN sys_refcursor;
END;
/
CREATE OR REPLACE PACKAGE BODY EVENT_VIEW_PACKAGE
AS
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN IDTABLETYPE,
p_MyLocationIDList IN IDTABLETYPE,
p_MyOriginatorIDList IN IDTABLETYPE,
p_MyStatusIDList IN IDTABLETYPE
)
RETURN sys_refcursor
AS
v_refcur sys_refcursor;
BEGIN
OPEN v_refcur FOR
SELECT *
FROM TRX_EVENTS ev
WHERE (ev.EVENT_SOURCE_ID MEMBER OF p_MySourceIDList OR IDTABLETYPE(p_MySourceIDList) = IDTABLETYPE())
AND (ev.EVENT_LOCATION_ID MEMBER OF p_MyLocationIDList OR IDTABLETYPE(p_MyLocationIDList) = IDTABLETYPE())
AND (ev.EVENT_ORIGINATOR_ID MEMBER OF p_MyOriginatorIDList OR IDTABLETYPE(p_MyOriginatorIDList) = IDTABLETYPE())
AND (ev.EVENT_STATUS_ID MEMBER OF p_MyStatusIDList OR IDTABLETYPE(p_MyStatusIDList) = IDTABLETYPE());
RETURN(v_refcur);
END FUNC_GET_EVENTS;
END;
/
But as I said, you don't need that PL/SQL, just use:
SELECT *
FROM TRX_EVENTS ev
WHERE (ev.EVENT_SOURCE_ID MEMBER OF IDTABLETYPE(p_MySourceIDList-elements) OR IDTABLETYPE(p_MySourceIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_LOCATION_ID MEMBER OF IDTABLETYPE(p_MyLocationIDList-elements) OR IDTABLETYPE(p_MyLocationIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_ORIGINATOR_ID MEMBER OF IDTABLETYPE(p_MyOriginatorIDList-elements) OR IDTABLETYPE(p_MyOriginatorIDList-elements) = IDTABLETYPE())
AND (ev.EVENT_STATUS_ID MEMBER OF IDTABLETYPE(p_MyStatusIDList-elements) OR IDTABLETYPE(p_MyStatusIDList-elements) = IDTABLETYPE())
/
SY.
[Updated on: Mon, 04 January 2016 16:30] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646609 is a reply to message #646578] |
Tue, 05 January 2016 07:44 |
Solomon Yakobson
Messages: 3275
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Oops,
I missed one more occurrence of IDTABLETYPE. Shuold be:
REATE OR REPLACE
TYPE IDTABLETYPE
AS TABLE OF NUMBER;
/
CREATE OR REPLACE PACKAGE EVENT_VIEW_PACKAGE
AS
TYPE intTableType IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN IDTABLETYPE,
p_MyLocationIDList IN IDTABLETYPE,
p_MyOriginatorIDList IN IDTABLETYPE,
p_MyStatusIDList IN IDTABLETYPE
)RETURN sys_refcursor;
END;
/
CREATE OR REPLACE PACKAGE BODY EVENT_VIEW_PACKAGE
AS
FUNCTION FUNC_GET_EVENTS
(
p_MySourceIDList IN IDTABLETYPE,
p_MyLocationIDList IN IDTABLETYPE,
p_MyOriginatorIDList IN IDTABLETYPE,
p_MyStatusIDList IN IDTABLETYPE
)
RETURN sys_refcursor
AS
v_refcur sys_refcursor;
BEGIN
OPEN v_refcur FOR
SELECT *
FROM TRX_EVENTS ev
WHERE (ev.EVENT_SOURCE_ID MEMBER OF p_MySourceIDList OR p_MySourceIDList = IDTABLETYPE())
AND (ev.EVENT_LOCATION_ID MEMBER OF p_MyLocationIDList OR p_MyLocationIDList = IDTABLETYPE())
AND (ev.EVENT_ORIGINATOR_ID MEMBER OF p_MyOriginatorIDList OR p_MyOriginatorIDList = IDTABLETYPE())
AND (ev.EVENT_STATUS_ID MEMBER OF p_MyStatusIDList OR p_MyStatusIDList = IDTABLETYPE());
RETURN(v_refcur);
END FUNC_GET_EVENTS;
END;
/
SY.
|
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646623 is a reply to message #646609] |
Tue, 05 January 2016 15:43 |
|
Sam D.
Messages: 17
Registered: December 2015
Location: USA
|
Junior Member |
|
|
Thank you very much! It compiles now so we're getting closer.
I called the function from a C# app and get PLS-00306: wrong number or types of arguments in call to 'FUNC_GET_EVENTS'
Don't we need to define an associative array type for each column in the table?
I am desperate and confused.
List<int> lst = GetEvents();
public List<int> GetEvents()
{
List<int> sourceIds = new List<int> { 17, 46 };
List<int> locationIds = new List<int> { 11, 21, 5, 8 };
List<int> originatorIds = new List<int> { 1, 2 };
List<int> statusIds = new List<int> { 1, 2 };
List<int> events = new List<int>();
using (OracleConnection oracleConn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString))
{
oracleConn.Open();
using (OracleCommand oracleComm = new OracleCommand())
{
oracleComm.Connection = oracleConn;
oracleComm.CommandType = CommandType.StoredProcedure;
oracleComm.CommandText = "EVENT_VIEW_PACKAGE.FUNC_GET_EVENTS";
//oracleComm.ArrayBindCount = 4;
oracleComm.BindByName = true;
OracleParameter cursor = new OracleParameter("Cursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
oracleComm.Parameters.Add(cursor);
OracleParameter source_ids = new OracleParameter("p_MySourceIDList", OracleDbType.Int32, ParameterDirection.Input)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = sourceIds.ToArray(),
Size = sourceIds.Count
};
OracleParameter location_ids = new OracleParameter("p_MyLocationIDList", OracleDbType.Int32, ParameterDirection.Input)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = locationIds.ToArray(),
Size = locationIds.Count
};
OracleParameter originator_ids = new OracleParameter("p_MyOriginatorIDList", OracleDbType.Int32, ParameterDirection.Input)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = originatorIds.ToArray(),
Size = originatorIds.Count
};
OracleParameter status_ids = new OracleParameter("p_MyStatusIDList", OracleDbType.Int32, ParameterDirection.Input)
{
CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = statusIds.ToArray(),
Size = statusIds.Count
};
oracleComm.Parameters.Add(source_ids);
oracleComm.Parameters.Add(location_ids);
oracleComm.Parameters.Add(originator_ids);
oracleComm.Parameters.Add(status_ids);
oracleComm.ExecuteNonQuery();
using (OracleDataReader dr = ((OracleRefCursor)cursor.Value).GetDataReader())
{
while (dr.Read())
{
Debug.WriteLine(dr["EVENT_ID"].ToString());
}
}
}
}
return events;
}
|
|
|
|
|
|
| Re: Joining on optional TABLE OF NUMBER parameters [message #646670 is a reply to message #646654] |
Wed, 06 January 2016 12:55 |
|
Sam D.
Messages: 17
Registered: December 2015
Location: USA
|
Junior Member |
|
|
Really appreciate your help.
I think I can see the light at the end of the tunnel.
I got this error: OCI-22303: type ""."IDTABLETYPE" not found
Then I ran these grants in similar way as others (DBA is our DBAadmin user, ABCDBU is our user under which the code connects):
CREATE OR REPLACE SYNONYM "ABCDBU"."IDTABLETYPE" FOR "IDTABLETYPE";
/
GRANT EXECUTE ON "ABCDBU"."IDTABLETYPE" TO "ABC_DB_EXEC";
/
GRANT EXECUTE ON "ABCDBU"."IDTABLETYPE" TO "ABCDBU";
/
GRANT EXECUTE ON "IDTABLETYPE" TO "ABCDBU";
/
Now I am getting
Custom type mapping for 'dataSource='(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.10.10.10)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ABC)))' schemaName='DBA' typeName='IDTABLETYPE'' is not specified or is invalid
What am I doing wrong?
public List<int> GetEvents()
{
List<int> sourceIds = new List<int> { 17, 46 };
List<int> locationIds = new List<int> { 11, 21, 5, 8 };
List<int> originatorIds = new List<int> { 1, 2 };
List<int> statusIds = new List<int> { 1 };
List<int> events = new List<int>();
using (OracleConnection oracleConn = new OracleConnection(ConfigurationManager.ConnectionStrings["OracleConnection"].ConnectionString))
{
oracleConn.Open();
using (OracleCommand oracleComm = new OracleCommand())
{
oracleComm.Connection = oracleConn;
oracleComm.CommandType = CommandType.StoredProcedure;
oracleComm.CommandText = "EVENT_VIEW_PACKAGE.FUNC_GET_EVENTS";
//oracleComm.ArrayBindCount = 4;
oracleComm.BindByName = true;
OracleParameter cursor = new OracleParameter("Cursor", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
oracleComm.Parameters.Add(cursor);
OracleParameter source_ids = new OracleParameter("p_MySourceIDList", OracleDbType.Array, ParameterDirection.Input)
{
//CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = sourceIds.ToArray(),
Size = sourceIds.Count,
UdtTypeName = "IDTABLETYPE"
};
OracleParameter location_ids = new OracleParameter("p_MyLocationIDList", OracleDbType.Array, ParameterDirection.Input)
{
//CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = locationIds.ToArray(),
Size = locationIds.Count,
UdtTypeName = "IDTABLETYPE"
};
OracleParameter originator_ids = new OracleParameter("p_MyOriginatorIDList", OracleDbType.Array, ParameterDirection.Input)
{
//CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = originatorIds.ToArray(),
Size = originatorIds.Count,
UdtTypeName = "IDTABLETYPE"
};
OracleParameter status_ids = new OracleParameter("p_MyStatusIDList", OracleDbType.Array, ParameterDirection.Input)
{
//CollectionType = OracleCollectionType.PLSQLAssociativeArray,
Value = statusIds.ToArray(),
Size = statusIds.Count,
UdtTypeName = "IDTABLETYPE"
};
oracleComm.Parameters.Add(source_ids);
oracleComm.Parameters.Add(location_ids);
oracleComm.Parameters.Add(originator_ids);
oracleComm.Parameters.Add(status_ids);
oracleComm.ExecuteNonQuery();
using (OracleDataReader dr = ((OracleRefCursor)cursor.Value).GetDataReader())
{
while (dr.Read())
{
Debug.WriteLine(dr["EVENT_ID"].ToString());
}
}
}
}
return events;
}
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Sun Jun 02 23:48:31 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
