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 <shekh-shakeel.ah...@dxc.com>
:
> 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' <comm...@groovy.apache.org>; '
> us...@groovy.apache.org' <us...@groovy.apache.org>; 'dev@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:@//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,*
>
>
>
--
Jorge Aguilera Gonzalez
CEO PuraVida Software
http://www.puravida-software.com
