anybody used array yet inside sqlunit?
I need to build an array of integer and pass it to
a pl/sql proc as an IN parameter..
Sujit, shed some light please :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
As I understand it, there isn't currently a way to specify an array value for an input parameter. Arrays as output parameters are handled, but their value is their "stringified" value.
I'm interested in being able to handle arrays of UDTs as output.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sujit & Douglas,
I have a simple requirement of picking up a query out put into an array,
like..
select p_id from table1 where rownum<=100
and I should be able
1. to piick the above 100 ids in to a single array variable.
and
2. pass it to a stored procedure call as an IN parameter(which you said sqlunit won't support)
..
but atleast would it support the first one ?I tried doing it but it gave some error message
saying DATATYPE ARRAY(2003) not supported etc.
It would have been great if the User Guide had some example on using array.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
will be ggreat to be able to handle arrays as input and output.It will be a valuable feature and will be very useful to test the procedure that uses bulk collect feature of Oracle
Thanks Sujit for giving it a thought,
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I checked the code, and you can represent Arrays as a String in SQLUnit. So you could return an Array object from a PL/SQL procedure and represent what it would look like as a String. But you cannot provide a string representation and have it turn into a java.sql.Array object. The reason is that support for Array implementations vary very widely among vendors (PostgreSQL has partial support and Oracle has more extensive support, and some others have no support). There is no default implementation, I took a look at the implementation in PostgreSQL's JDBC driver and that depends on having a reference to the database connection.
MockRunner provides a mock array implementation however. If you post a snippet of Java code to show how you are using Arrays in Java code (both input and output) as well as some PL/SQL code (again, both input and output), I could take a look and see if I can build some code and test with MockRunner. Cant guarantee, but there is a fair chance that it may just work with Oracle.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have the same issue of testing a stord procedure which has a bulk insert with couple of parameters which has array as input parameter. Is it still true that input for array type is still not supported in SQLUNIT?
Not sure if you were able to test a similar code in MockRunner before, but here is the stored procedure we would like to test. Let me know if this will be enough info for now or you would like some more.
IF (ARG_DOCUMENT_LIST IS NOT NULL) AND (ARG_DOCUMENT_LIST.COUNT != 0) THEN
BEGIN
SELECT
RESULT_ID
INTO
LOCAL_RESULT_ID
FROM
SEARCH_QUERY_RESULTS
WHERE
SEARCH_QUERY_RESULTS.RESULT_ID = ARG_SEARCH_RESULT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT
INTO
SEARCH_QUERY_RESULTS
(RESULT_ID, RECORDED_ON)
VALUES
(ARG_SEARCH_RESULT_ID, SYSDATE);
END;
FORALL
I
IN
ARG_DOCUMENT_LIST.FIRST..ARG_DOCUMENT_LIST.LAST
INSERT
INTO
SEARCH_QUERY_DOCUMENTS
(RESULT_ID,DOCUMENT_ID,RESULT_SEQ)
VALUES
(ARG_SEARCH_RESULT_ID,ARG_DOCUMENT_LIST(I),ARG_RESULT_SEQ(I));
END IF;
END;
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
anybody used array yet inside sqlunit?
I need to build an array of integer and pass it to
a pl/sql proc as an IN parameter..
Sujit, shed some light please :)
Hi Sathi,
I am not sure if anyone has used it, which is why I did not reply to the original post. Is there a problem with it?
-sujit
As I understand it, there isn't currently a way to specify an array value for an input parameter. Arrays as output parameters are handled, but their value is their "stringified" value.
I'm interested in being able to handle arrays of UDTs as output.
Sujit & Douglas,
I have a simple requirement of picking up a query out put into an array,
like..
select p_id from table1 where rownum<=100
and I should be able
1. to piick the above 100 ids in to a single array variable.
and
2. pass it to a stored procedure call as an IN parameter(which you said sqlunit won't support)
..
but atleast would it support the first one ?I tried doing it but it gave some error message
saying DATATYPE ARRAY(2003) not supported etc.
It would have been great if the User Guide had some example on using array.
Thanks to Doug for pointing out that this is not supported at the moment.
Its been a while since I looked at the code, but let me check and see if this can be done and get back to you.
-sujit
will be ggreat to be able to handle arrays as input and output.It will be a valuable feature and will be very useful to test the procedure that uses bulk collect feature of Oracle
Thanks Sujit for giving it a thought,
Hi Sathi,
I checked the code, and you can represent Arrays as a String in SQLUnit. So you could return an Array object from a PL/SQL procedure and represent what it would look like as a String. But you cannot provide a string representation and have it turn into a java.sql.Array object. The reason is that support for Array implementations vary very widely among vendors (PostgreSQL has partial support and Oracle has more extensive support, and some others have no support). There is no default implementation, I took a look at the implementation in PostgreSQL's JDBC driver and that depends on having a reference to the database connection.
MockRunner provides a mock array implementation however. If you post a snippet of Java code to show how you are using Arrays in Java code (both input and output) as well as some PL/SQL code (again, both input and output), I could take a look and see if I can build some code and test with MockRunner. Cant guarantee, but there is a fair chance that it may just work with Oracle.
-sujit
Hi Sujit,
I have the same issue of testing a stord procedure which has a bulk insert with couple of parameters which has array as input parameter. Is it still true that input for array type is still not supported in SQLUNIT?
Not sure if you were able to test a similar code in MockRunner before, but here is the stored procedure we would like to test. Let me know if this will be enough info for now or you would like some more.
PROCEDURE BULK_INSERT_SEARCH_RESULTS(ARG_SEARCH_RESULT_ID INTEGER, ARG_DOCUMENT_LIST NUMBER_COLLECTION,ARG_RESULT_SEQ NUMBER_COLLECTION)
IS
LOCAL_RESULT_ID INTEGER := 0;
BEGIN
IF (ARG_DOCUMENT_LIST IS NOT NULL) AND (ARG_DOCUMENT_LIST.COUNT != 0) THEN
BEGIN
SELECT
RESULT_ID
INTO
LOCAL_RESULT_ID
FROM
SEARCH_QUERY_RESULTS
WHERE
SEARCH_QUERY_RESULTS.RESULT_ID = ARG_SEARCH_RESULT_ID;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT
INTO
SEARCH_QUERY_RESULTS
(RESULT_ID, RECORDED_ON)
VALUES
(ARG_SEARCH_RESULT_ID, SYSDATE);
END;
FORALL
I
IN
ARG_DOCUMENT_LIST.FIRST..ARG_DOCUMENT_LIST.LAST
INSERT
INTO
SEARCH_QUERY_DOCUMENTS
(RESULT_ID,DOCUMENT_ID,RESULT_SEQ)
VALUES
(ARG_SEARCH_RESULT_ID,ARG_DOCUMENT_LIST(I),ARG_RESULT_SEQ(I));
END IF;
END;