sqlexception: invalid name pattern: myUserName:recordTypeName

[Mohamed Iqzas]

This is the java code i use:

                                  Class.forName ("oracle.jdbc.driver.OracleDriver");
	  	
	  	      // Connect to the database  	  
	  	      Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@dbaddrs:1522:sid","myUserName", "Password");
		int commit = 1;
		Vector vector = new Vector();


		Object[] attr = new Object[4];
		
		attr[0] = (Object) new String("NP");
		attr[1] = (Object) new String("L5");
		attr[2] = (Object) new BigDecimal(0);
		attr[3] = (Object) new String("7");

		
		

		StructDescriptor structdesc = StructDescriptor.createDescriptor("recordTypeName",conn);
		vector.add((Object)new STRUCT(structdesc, conn, attr));


		ArrayDescriptor arraydesc = ArrayDescriptor.createDescriptor("TableOfRecordTypeName",conn);

		Object obj_array[] = vector.toArray();
		ARRAY array = new ARRAY(arraydesc,conn,obj_array);

		CallableStatement cstmt = conn.prepareCall("{ call PKG_Name.Func_Name(?,?,?,?,?) }");
		((OracleCallableStatement)cstmt).setARRAY(5, array);
		cstmt.setInt(3, 1);
		cstmt.setString(1, "NP");
		cstmt.setString(2, "H2");
		cstmt.setString(4, "4");
		cstmt.execute();
		System.out.println("Please check database");

		}
		catch(Exception e)
		{
		System.err.println("dothis method exception: " + e.getMessage());
		}

I'm getting error in this line:

"StructDescriptor.createDescriptor("recordTypeName",conn);"

In Oracle Db, i've something like this:
....

TYPE recordTypeName IS RECORD (firstVar varchar2(50),secondVar varchar2(50),aNumber number,thirdVar varchar2(50));

TYPE TableOfRecordTypeName IS TABLE OF recordTypeName INDEX BY BINARY_INTEGER;

.....

Why I'm getting error in naming pattern like this? dbuserName:recordTypeName .. I have no idea what this is about? I'm just trying the code extracted from some website. I want to pass in a table of records as an argument to a PL\SQL function from JAVA...

Please help me..

below is the stacktrace:

java.sql.SQLException: invalid name pattern: myUserName:recordTypeName
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:527)
at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:407)
at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:249)
at oracle.sql.StructDescriptor.<init>(StructDescriptor.java:204)
at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:138)
at Utilities.DBArrayType2.main(DBArrayType2.java:39)... -----------DBArrayType2 is the class I use

[Martin Vashko]

Please edit your post and put the code into code tags. It will be much more readable.

This page contains advice on how to use collections of Oracle objects in Java. That page also contains links to relevant parts of Oracle documentations you really should read, and also a link to a thread in which one of our fellow ranchers successfully implemented such functionality.

[Mohamed Iqzas]

Thanks Martin.

That linked really helped but not for a complete solution on how to pass PL\SQL table of records arguments from JAVA.

But my question in this thread is resolved.

Tips that helped me: (Might not be helpful for everyone)

- the pl\sql type was defined inside a package. While creating the Struct Descriptor if the type name is not prefixed with the packageName using a dot( . ), then the error throws with DB USerName Prefixed with typeName. If I use the package prefix while creating descriptor, then also i get the same exception but correctly with package name prefix instead of username in the stack trace.

- Now if the type is declared globally in the DB we connect, i.e., outside the package, then i'm able to create the struct or Array descriptor without any exceptions. Also note that the type name used for creating descriptor in JAVA code should be in Upper Case no matter how it is defined in PL\SQL statements(thats how it worked for me). Also use the schema prefix if any.

Here I have used Struct descriptor for the PL\SQL type declared as RECORD, and an ArrayDescriptor for the PL\SQL type TABLE OF that record type. Is this the correct approach? Will that work?

[Martin Vashko]

Mohamed Iqzas wrote:Also note that the type name used for creating descriptor in JAVA code should be in Upper Case no matter how it is defined in PL\SQL statements(thats how it worked for me). Also use the schema prefix if any.

Actually, all SQL identifiers (tables, coumns, packages, types and so on) are converted to upper case by Oracle, unless you enclose them in double quotes. When you use double quotes to create a mixed-case or lower-case identifier, you need to always specify it that way (ie. in double quotes) everywhere - in select queries and so on, so it is very rarely done.

When registering the type from Java, the conversion to upper case is apparently not done. I'll add that to the HowTo page when I get some time, or you an add it there yourself (there is an Edit button on the page).

Did you already have a look at this thread referenced from the HowTo page? There are also links directly to relevant chapters of Oracle documentation there (if all else fails ).

I'm not sure I'll be able to help, I haven't ever actually tried to pass tables of structures from Java to PL/SQL, but perhaps I'll be able to spot something if you post both the SQL code and Java code here.

[VamsiKrisha Yaganti]

Yes,its working !!!