Hi All,
I am new in Groovy and stuck on writing in script.
I have a oracle stored proc and that proc using the Arry type input parameter
Type Parameter :
create or replace TYPE "ARR_HP_STU_CERT_USERNAME" as table of varchar2(32767);
PROC:
1. create or replace PROCEDURE sp_hp_stu_cert_status_chk (
2. in_track_id IN NUMBER,
3. in_username IN arr_hp_stu_cert_username,
4. in_certification_id IN VARCHAR2,
5. out_details OUT SYS_REFCURSOR,
6. out_status_code OUT NUMBER,
7. out_status_desc OUT VARCHAR2)
8. AS
9. v_prc_name VARCHAR2 (50) := 'sp_hp_stu_cert_status_chk';
10. v_count NUMBER;
11. v_user_id VARCHAR2 (500);
12. required_fields EXCEPTION;
13. BEGIN
14. {
15. ;;;;; doing stuff ;;;;;;
16. }
I have written a groovy code to execute that proc but its giving error about
casting :
Groovy Script :
1. /*import org.apache.commons.io.IOUtils
2. import org.apache.nifi.controller.ControllerService
3. import org.apache.nifi.processor.io.StreamCallback */
4. import java.nio.charset.*
5. import groovy.sql.OutParameter
6. import groovy.sql.InParameter
7. import groovy.sql.Sql
8. import oracle.jdbc.OracleTypes
9. import java.sql.ResultSet
10. //import java.sql.*
11.
12.
13.
14.
15. def sql =
Sql.newInstance('jdbc:oracle:thin:@//XXXXX.itcs.entsvcs.net:1525/XXXX', 'XXXX',
'XXXXX$', 'oracle.jdbc.OracleDriver')
16. in_track_id='1798'
17. in_certification_id='crtfy000000000003144'
18.
19.
20.
21.
22. OutParameter CURSOR_PARAMETER = new OutParameter() {
23. public int getType() {
24. return OracleTypes.CURSOR;
25. }
26. };
27.
28.
29. InParameter ArrayUserName = new InParameter() {
30. public int getType() {
31. return OracleTypes.ARRAY;
32. }
33. public Object getValue(){
34.
35.
36. }
37. };
38.
39.
40.
41.
42. def data = []
43. String sqlString ="""{call sp_hp_stu_cert_status_chk(?, ?, ?, ?, ?, ?)}""";
44. def status_desc
45. def status_code
46. def status_data
47. def testID
48. def parametersList = [in_track_id, ArrayUserName, in_certification_id,
CURSOR_PARAMETER, Sql.NUMERIC ,Sql.VARCHAR];
49.
50.
51. sql.call(sqlString, parametersList) {out_details,
out_status_code,out_status_desc ->
52. status_desc = out_status_desc
53. status_code = out_status_code
54. };
55.
56.
57. Print(status_desc)
58. Print(status_code)
And this scripting giving error to of arry.type "ArrayUserName" could any help
where I am making incorrect thing.
Thanks & Regards
Shekh Shakeel Ahmad,
