Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

java.sql.SQLException: invalid name pattern

929625Dec 24 2015 — edited Dec 24 2015

I have a PLSQL code with the following signature.

create or replace PACKAGE employee_details AS type string_array is table of varchar2(32); end employee_details;


When I execute the below PL/SQL block it works fine in sql developer.


set serveroutput on;

declare  

        p_id ICE10_1_10_NEW.employee_details.string_array;    

begin 

        DBMS_OUTPUT.ENABLE();

        p_id := ICE10_1_10_NEW.employee_details.string_array();  

        p_id.EXTEND(4); 

        p_id(1) := 'kunal'; 

        p_id(2) := 'utpal'; 

        p_id(3) := 'a'; 

        p_id(4) := 'm';

       

        dbms_output.put_line('-----------------------------------------------');

        FOR i in 1 .. p_id.count LOOP

            dbms_output.put_line('row '|| i ||' = ' || p_id(i) );

        END LOOP;

        dbms_output.put_line('-----------------------------------------------');

       

end;

Now when I am trying to call same Block from java using JDBC (odbc14.jar). It is throwing exception as

java.sql.SQLException: invalid name pattern: ICE10_1_10_NEW.EMPLOYEE_DETAILS.STRING_ARRAY

  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)

  at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)

  at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:503)

  at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:406)

  at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1952)

  at oracle.sql.ArrayDescriptor.<init>(ArrayDescriptor.java:199)

  at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:118)

  at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:169)

  at com.ice.test.CallPLSQLBlock.main(CallPLSQLBlock.java:46)

My Java Code is:

import java.sql.Array;

import java.sql.CallableStatement;

import java.sql.Connection;

import java.sql.DriverManager;

import java.sql.PreparedStatement;

import java.sql.ResultSet;

import java.sql.ResultSetMetaData;

import java.sql.Types;

import oracle.jdbc.driver.OracleCallableStatement;

import oracle.jdbc.driver.OracleTypes;

import oracle.sql.ARRAY;

import oracle.sql.ArrayDescriptor;

public class CallPLSQLBlock {

  public static void main(String args[])

  {

  System.out.println("==================== START ======================");

  Connection con = null;

  PreparedStatement ps = null;

  OracleCallableStatement  cs = null;

  ResultSet rs = null;

  try {

  

  Class.forName("oracle.jdbc.driver.OracleDriver");

  con = DriverManager.getConnection("jdbc:oracle:thin:@icebox-pc:1521:orcl", "ICE10_1_10_new", "icedq123");

  

  String plsql = "" +

  " declare " +

  "     p_id ICE10_1_10_NEW.employee_details.string_array;"+

  " begin " +

     "p_id := ICE10_1_10_NEW.employee_details.string_array();  " +

             "p_id.EXTEND(4); " +

             "p_id(1) := 'kunal'; " +

             "p_id(2) := 'utpal'; " +

             "p_id(3) := 'a'; " +

             "p_id(4) := 'm'; "+

             "    ? := p_id; " // return array

  +" end;";

  cs = (OracleCallableStatement)con.prepareCall(plsql);

  cs.registerOutParameter(1, OracleTypes.ARRAY,"ICE10_1_10_NEW.EMPLOYEE_DETAILS.STRING_ARRAY");

  

  cs.execute();

  

  Array simpleArray = cs.getArray(1);

  System.out.println("----------------------Start Array-------------------------");

  ResultSet res = cs.getArray(1).getResultSet();

  

  if (res.next())

  {

  System.out.println("ssssssssssssssssss ===============>>>>>>>>>>>>> "+res.getObject(2));

  

  }

  } catch (Exception e) {

       e.printStackTrace();

  }

     finally {

      try { 

      cs.close();

      rs.close();

      ps.close();

      con.close(); 

         } catch (Exception e) { 

            // e.printStackTrace(); 

         }

     }

     System.out.println("==================== STOP ======================");

  }

}

Please help Thanks in advance.

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 21 2016
Added on Dec 24 2015
0 comments
2,169 views