1 Reply Latest reply: Mar 31, 2014 11:46 AM by Zabo RSS

    Calling PLSQL Procedure with out struct parameters.

    Zabo

      Hello,

       

      I try to use the JDBC using STRUCT Type to retrieve user defined Type from PL SQL Procedures.

       

      I succeeded in retrieving Date, Timestamp, number but I can't retrieve string (I got Question marks). Indeed I got the following result :

      run:

      [???, ???, ???]

      BUILD SUCCESSFUL (total time: 1 second)

       

      Can anybody help me ?

       

      Thanks

       

       

      Here is a simple example of what I wrote a

       

       

      CREATE OR REPLACE TYPE    "R_TEST" IS OBJECT

      (C1 CHAR(100),

      C2 VARCHAR(100),

      C3 VARCHAR2(100));

      /

       

      CREATE OR REPLACE package Test_Struct

      is

       

       

      procedure My_Proc(O_DATA  OUT NOCOPY R_TEST);

       

      end;

      /

       

      CREATE OR REPLACE package body Test_Struct

      is

       

      procedure My_Proc(O_DATA  OUT NOCOPY R_TEST)

      is

      begin

      O_DATA := R_TEST('1','2','3');

      end;

      end;

      /

       

      /*

      * To change this license header, choose License Headers in Project Properties.

      * To change this template file, choose Tools | Templates

      * and open the template in the editor.

      */

       

      package structora;

       

      import java.sql.Connection;

      import java.sql.DriverManager;

      import java.sql.ResultSet;

      import java.sql.Struct;

      import java.util.Arrays;

      import oracle.jdbc.OracleCallableStatement;

      import oracle.jdbc.OracleTypes;

       

      public class Structora {

       

       

          public static void main(String[] args) {

              String url = "jdbc:oracle:thin:@//my_server:1521/My_DB";

              String userName = "my_login";

              String pwd = "my_pwd";

       

              try{

                  Connection conn = DriverManager.getConnection(url, userName, pwd);          

                  OracleCallableStatement cs = null;          

                  String storedProc = "{call Test_Struct.My_Proc(?)}";

                  ResultSet rs = null;

       

                  cs = (OracleCallableStatement)conn.prepareCall(storedProc);

       

                  cs.registerOutParameter(1, OracleTypes.STRUCT, "R_TEST");

       

                  cs.execute();

       

                  Struct struct = (Struct) cs.getObject(1);

       

                  Object[] attrs = struct.getAttributes();

                

                  System.out.println(Arrays.asList(attrs));

       

            

              }

              catch(Exception e)

              {

                  System.out.println(e.getMessage());

                

              }    }

        

      }