Re: Need Help to fix the error in Groovy Code
Hi Shekh
As and idea, if ArrayUserName is an OracleTypes.ARRAY, maybe it needs to
return something as a List at `getValue()` method
regards
2018-03-23 8:34 GMT+01:00 Ahmad, Shekh Shakeel
:
> Hi All,
>
> Is that anyone can help ?
>
>
>
>
>
> Thanks & Regards
> *Shekh Shakeel Ahmad, ITIL Certified*
>
>
>
> *From:* Ahmad, Shekh Shakeel
> *Sent:* Thursday, March 22, 2018 4:34 PM
> *To:* 'comm...@groovy.apache.org' ; '
> us...@groovy.apache.org' ; 'dev@groovy.apache.org'
> ; 'notificati...@groovy.apache.org' <
> notificati...@groovy.apache.org>
> *Subject:* Need Help to fix the error in Groovy Code
>
>
>
> 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:@//X.itcs.entsvcs.
> net:1525/', '', 'X$', 'oracle.jdbc.OracleDriver')
>
> 16. in_track_id='1798'
>
> 17. in_certification_id='crtfy0003144'
>
> 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,*
>
>
>
--
Jorge Aguilera Gonzalez
CEO PuraVida Software
http://www.puravida-software.com
RE: Need Help to fix the error in Groovy Code
Hi All,
Is that anyone can help ?
Thanks & Regards
Shekh Shakeel Ahmad, ITIL Certified
From: Ahmad, Shekh Shakeel
Sent: Thursday, March 22, 2018 4:34 PM
To: 'comm...@groovy.apache.org' ;
'us...@groovy.apache.org' ; 'dev@groovy.apache.org'
; 'notificati...@groovy.apache.org'
Subject: Need Help to fix the error in Groovy Code
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:@//X.itcs.entsvcs.net:1525/', '',
'X$', 'oracle.jdbc.OracleDriver')
16. in_track_id='1798'
17. in_certification_id='crtfy0003144'
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,
Need Help to fix the error in Groovy Code
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:@//X.itcs.entsvcs.net:1525/', '',
'X$', 'oracle.jdbc.OracleDriver')
16. in_track_id='1798'
17. in_certification_id='crtfy0003144'
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,
