java.sql.SQLException: invalid name pattern: pranaka.pre_hook_out_list

[Manju Krishna]

I am trying to call a stored procedure from java using JDBC. The stored procedure is having table type out parameter.

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Struct;
import java.sql.Types;
import java.sql.Array;
import java.sql.ResultSetMetaData;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.StructDescriptor;

public class TestTableTypeCall {

	/**
	 * @param args
	 */
	public static void main(String[] args) {
		Connection connection = null;
		String query = "call test_table_type_only_out(?,?)";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka");
			

			
			// Get a description of your type (Oracle specific)
			final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
			final ResultSetMetaData metaData = structDescriptor.getMetaData();
			
			OracleCallableStatement  statement = null;
			statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}");
			
		
			statement.setString(1, "Manju");
			statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList");			
			statement.execute();
			
			ARRAY simpleArray = statement.getARRAY(1);

		    System.out.println ("Array is of type " +
		                         simpleArray.getSQLTypeName());

		    System.out.println ("Array element is of type code " +
		                         simpleArray.getBaseType());

		    System.out.println ("Array is of length " +
		                         simpleArray.length());


		    String[] values = (String[])simpleArray.getArray();

		    for( int i = 0; i < values.length; i++ )
		        System.out.println( "row " + i + " = '" +
		                             values[i] + "'" );
		    

			statement.close();		
			connection.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

The Stored procedure is,
create or replace PROCEDURE test_table_type_only_out ( inParam IN VARCHAR2 ,outParam OUT pre_hook_out_paramList ) IS i NUMBER; BEGIN outParam := pre_hook_out_paramList(); outParam.extend(2); outParam(1) := inParam; outParam(2) := inParam || ' Second Element'; END test_table_type_only_out;

and created the type like,
create or replace type pre_hook_out_paramList is table of varchar2(1000);

When I run my jave class I get an SQLException,
java.sql.SQLException: invalid name pattern: pranaka.pre_hook_out_list

I tried going through existing forums, but did not get an answer to my current problem. Please help

[Martin Vashko]

We have a brief FAQ page on this subject.

Where does the typeName on line 30 come from? It's value is not shown. My guess is that the name is wrong (pre_hook_out_list instead of pre_hook_out_paramList), but you might also be missing a schema name there. I may be wrong, though, my experience in this area is limited.

[Manju Krishna]

I posted a different class's exception wrongly,

the exception is java.sql.SQLException: invalid name pattern: PRANAKA.pre_hook_out_paramList

and these two lines part of earlier code is not there,

final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
			final ResultSetMetaData metaData = structDescriptor.getMetaData();

Actual Class

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;

public class Approach1 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		Connection connection = null;
		String query = "call test_table_type_only_out(?,?)";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka");
			
			OracleCallableStatement  statement = null;
			statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}");
			statement.setString(1, "Manju");
			statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList");			
			statement.execute();
			
			ARRAY simpleArray = statement.getARRAY(1);

		    System.out.println ("Array is of type " +
		                         simpleArray.getSQLTypeName());

		    System.out.println ("Array element is of type code " +
		                         simpleArray.getBaseType());

		    System.out.println ("Array is of length " +
		                         simpleArray.length());


		    String[] values = (String[])simpleArray.getArray();

		    for( int i = 0; i < values.length; i++ )
		        System.out.println( "row " + i + " = '" +
		                             values[i] + "'" );
		    

			statement.close();		
			connection.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

[Martin Vashko]

Could you post the code - as you run it - again? I really cannot figure out how the code snippet in your last post relates to the rest of your code. And please make sure to show how the typeName variable is initialized, it is crucial to this question.

[Manju Krishna]

Per my last comment, the typeName variable is not used in my java class.

Re-posting all the java and SQL code

package test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;

public class Approach1 {

	/**
	 * @param args
	 */
	public static void main(String[] args) {

		Connection connection = null;
		String query = "call test_table_type_only_out(?,?)";
		try {
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka");
			
			OracleCallableStatement  statement = null;
			statement = (OracleCallableStatement)connection.prepareCall("{call test_table_type_only_out(?,?)}");
			statement.setString(1, "Manju");
			statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList");			
			statement.execute();
			
			ARRAY simpleArray = statement.getARRAY(1);

		    System.out.println ("Array is of type " +
		                         simpleArray.getSQLTypeName());

		    System.out.println ("Array element is of type code " +
		                         simpleArray.getBaseType());

		    System.out.println ("Array is of length " +
		                         simpleArray.length());


		    String[] values = (String[])simpleArray.getArray();

		    for( int i = 0; i < values.length; i++ )
		        System.out.println( "row " + i + " = '" +
		                             values[i] + "'" );
		    

			statement.close();		
			connection.close();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

	}

}

The type created as
create or replace type pre_hook_out_paramList is table of varchar2(1000);

Stored Procedure ,
create or replace PROCEDURE test_table_type_only_out ( inParam IN VARCHAR2 ,outParam OUT pre_hook_out_paramList ) IS i NUMBER; BEGIN outParam := pre_hook_out_paramList(); outParam.extend(2); outParam(1) := inParam; outParam(2) := inParam || ' Second Element'; END test_table_type_only_out;

Let me know od any further details

[Manju Krishna]

I changed the type Name used in below line ,

 statement.registerOutParameter(2,OracleTypes.ARRAY,"pre_hook_out_paramList");  

to upper case and it worked. I have the complete code which takes both IN and OUT param as a table type.

Can someone let me know on where I can post this code so that others can benefit from the same.

Cheers

[Martin Vashko]

I'm glad you worked it out, and thanks for sharing the solution!

If you want to post the code you've got now, you might do so it here.

If you want to help even more, you might update our wiki pages - either the How to use arrays of Oracle SQL objects, or add a new section to the Oracle FAQ page. Our Wiki FAQ says how to create a new page, should you need one. It's OK to include a code in the Wiki too, preferably a polished one, ie. SSCCE.

If you get into problems with editing the wiki, let me know - I'll be glad to help.

[Manju Krishna]

Posting the solution
Java Code,

package test;

import java.sql.Array;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSetMetaData;
import java.sql.Struct;
import java.sql.Types;

import oracle.jdbc.OracleCallableStatement;
import oracle.jdbc.OracleTypes;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.StructDescriptor;

public class TableTypeWithObject {



	public static void main(String[] args) {
		new TableTypeWithObject().execute();
	}
	
	public void execute(){

		Connection connection = null;
		oracle.sql.ARRAY array =null;		
		ArrayDescriptor arrayDescriptor = null;  
	    OracleCallableStatement cStmt = null; 
		try{
			Class.forName("oracle.jdbc.driver.OracleDriver");
			connection = DriverManager.getConnection("jdbc:oracle:thin:@msi-vmpl3db2:1521:pl11gdb","pranaka","pranaka");
			
			arrayDescriptor = ArrayDescriptor.createDescriptor("ORG_HOOK_LIST", connection);
			
			InputBean input[]= new InputBean[2];  
	          
	        input[0]=new InputBean();  
	        input[0].setParamName("moving_node");  
	        input[0].setParamVal("100000");  
	          
	        input[1]=new InputBean();  
	        input[1].setParamName("parent_node");  
	        input[1].setParamVal("200000");  
	        
	        Object[]  objDetailList=null;   
	        objDetailList=new Object[input.length];  
	        for (int i = 0; i < objDetailList.length; i++){
	        	getInputArray(input[i]);
	        }    
			array = new oracle.sql.ARRAY(arrayDescriptor, connection,objDetailList);
			
			final String typeName = "ORG_HOOK_OBJECT";
			final String typeTableName = "ORG_HOOK_LIST";
			final StructDescriptor structDescriptor = StructDescriptor.createDescriptor(typeName.toUpperCase(), connection);		
			final ResultSetMetaData metaData = structDescriptor.getMetaData();
			
			cStmt =(OracleCallableStatement)connection.prepareCall("{call test_table_type_with_object(?,?)}");
            cStmt.setArray(1, array); 
            cStmt.registerOutParameter(2, Types.ARRAY, typeTableName);
            cStmt.execute();
            
            Object[] data = (Object[]) ((Array) cStmt.getObject(2)).getArray();
    		for(Object tmp : data) {
    			Struct row = (Struct) tmp;
    			// Attributes are index 1 based...
    			int idx = 1;
    			for(Object attribute : row.getAttributes()) {				
    				System.out.println(metaData.getColumnName(idx) + " = " + attribute);											
    				++idx;
    			}
    			System.out.println("---");
    		}
    		
            cStmt.close();
            connection.close();
            
		}catch (Exception ex){
			ex.printStackTrace();
		}
	
	}

    public class InputBean {  
        private String param_name;  
        private String param_val;  
          
        public InputBean(String param_name,String param_val){     
         this.param_name=param_name;  
         this.param_val=param_val;  
        }  
          
        public InputBean(){ }  

     /** 
      * @return the canal 
      */  
     public String getParamVal() {  
         return this.param_val;  
     }  

     /** 
      * @param canal the canal to set 
      */  
     public void setParamVal(String paramVal) {  
         this.param_val = paramVal;  
     }  

     /** 
      * @return the mapKey 
      */  
     public String getParamName() {  
         return this.param_name;  
     }  

     /** 
      * @param mapKey the mapKey to set 
      */  
     public void setParamName(String paramName) {  
         this.param_name = paramName;  
     }  
       
}  

  public static Object[] getInputArray(InputBean it){  
      Object[] objArr = new Object[2];         
      objArr[0]=it.getParamName();  
      objArr[1]=it.getParamVal();                  
      return objArr;  
    } 
}

SQL Table Types -

create or replace TYPE org_hook_object AS OBJECT ( param_name varchar2(1000), param_val varchar2(1000) ); create or replace TYPE org_hook_list IS TABLE OF org_hook_object;

SQL Procedure,

create or replace PROCEDURE test_table_type_with_object (inParam IN org_hook_list,outParam OUT org_hook_list) IS i NUMBER; name1 VARCHAR2(1000); val1 VARCHAR2(10000); BEGIN outParam := org_hook_list(); outParam.extend(2); FOR i IN inParam.FIRST .. inParam.LAST LOOP name1 := inParam(i).param_name || ' Out Param Name '|| i; val1 := inParam(i).param_val || ' Out Param Val '|| i; insert into test123 (name1,value1) values (name1,val1); outParam(i) := org_hook_object(name1,val1); END LOOP; END test_table_type_with_object;