Invalid column index when trying to execute Stored Proc using Hibernate.

[santosh malreddy]

Hello All,

I am getting this below exception when I am trying to execute a Stored procedure using HIbernate from DaoImpl Class.
I am not sure of what is wrong ..I tried all the ways to fix it but did not resolve the issue.
Could anyone please help me ,figure out whats wrong with the code or the mapping file.

Below is the code snippet and also the Mapping file.


Stack Trace:
--------------
Hibernate: CALL some_procedure(?)
org.hibernate.exception.GenericJDBCException: could not execute query
at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:126)
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:114)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2236)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2130)
at org.hibernate.loader.Loader.list(Loader.java:2125)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1724)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at com.initrode.dao.GenericDaoImpl.callProc(GenericDaoImpl.java:44)
at com.initrode.GenericStoredProc.main(GenericStoredProc.java:40)
Caused by: java.sql.SQLException: Invalid column index
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:207)
at oracle.jdbc.driver.OraclePreparedStatement.setStringInternal(OraclePreparedStatement.java:5359)
at oracle.jdbc.driver.OracleCallableStatement.setString(OracleCallableStatement.java:4575)
at org.hibernate.type.StringType.set(StringType.java:49)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:154)
at org.hibernate.type.NullableType.nullSafeSet(NullableType.java:136)
at org.hibernate.loader.Loader.bindNamedParameters(Loader.java:1777)
at org.hibernate.loader.Loader.bindParameterValues(Loader.java:1703)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1592)
at org.hibernate.loader.Loader.doQuery(Loader.java:695)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:258)
at org.hibernate.loader.Loader.doList(Loader.java:2233)
... 8 more


Mapping file:

---------------
[code=xml]
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping auto-import="false">

<sql-query name='some_procedure' callable="true">
<!-- {? = call some_procedure(?,?,?,?,?,?,?,?,?,?,?)}
,:param2,:param3,:param4,:param5,:param6,:param7,:param8,:param9,:param10,:param11)]]>-->
<![CDATA[CALL some_procedure(:param1)]]>
</sql-query>
[/code]


DAOImpl:
------------
[code=java]
public void callProc(String procName, Map paramMap) throws SQLException {
Session session = null;
Transaction tx = null;
try {

session = HibernateUtils.getSessionFactory().getCurrentSession();
tx = session.beginTransaction();
String dbURL = session.connection().getMetaData().getURL().toString();
System.out.println("Conenction DB URL "+ dbURL );
tx.setTimeout(5);
String[] keys = new String[paramMap.size()];
keys = (String[]) paramMap.keySet().toArray(keys);

Query query = session.getNamedQuery(procName);

for (String key : keys) {
//query.setParameter(1, "5512324");
query.setParameter(key, paramMap.get(key));

//System.out.println(query.getQueryString());
}
System.out.println(query.getQueryString());
List result = query.list();
for (int i = 0; i < result.size(); i++) {
// logging the information.
log.info(i);
}
tx.commit();
} catch (RuntimeException exception) {
exception.printStackTrace();
try {
tx.rollback();
} catch (RuntimeException rbe) {
log.error("Couldn’t roll back transaction", rbe);
rbe.printStackTrace();
}
throw exception;
} finally{
if(session !=null){
session.flush();
session.close();
}
}
}
}
[/code]

[santosh malreddy]

COuld anyone help me sorting this out...I am running into this issue from past week,

[santosh malreddy]

Hello All,

I modified the mapping file and also the Implementaion.. but still I am running into exception.

Any help is highly appreciated.

Main :

	public static void main(String[] args) {
		// null checks
		// Check how many arguments were passed in
		String procName = "proc_name";// args[0];
		String params = "param1:500089" ;

DAOImpl:

    GenericDaoImpl implements GenericDao {
	private static final Log log = LogFactory.getLog(GenericDaoImpl.class);

	@SuppressWarnings("unchecked")
	public void callProc(String procName, Map paramMap) throws SQLException {
		Session session = null;
		Transaction tx = null;
		try {
			
			session = HibernateUtils.getSessionFactory().getCurrentSession();
			tx = session.beginTransaction();
			String dbURL = session.connection().getMetaData().getURL().toString();
			System.out.println("Conenction DB URL "+ dbURL );
			tx.setTimeout(5);
			String[] keys = new String[paramMap.size()];
			keys = (String[]) paramMap.keySet().toArray(keys);

			Query query = session.getNamedQuery(procName);
			query.setParameter("param1", 5501010);
									
//			for (String key : keys) {
//				//	query.setParameter(key, paramMap.get(key));
//			//		System.out.println(query.getQueryString());
//			}
			
			List result = query.list();
			System.out.println(query.getQueryString());
			for (int i = 0; i < result.size(); i++) {
				// logging the information.
				log.info(i);
			}
			tx.commit();
		} catch (RuntimeException exception) {
			exception.printStackTrace();
			try {
				tx.rollback();
			} catch (RuntimeException rbe) {
				log.error("Couldn’t roll back transaction", rbe);
				rbe.printStackTrace();
			}
			throw exception;
		} finally{   
				   if(session !=null){   
				      session.flush();      
				      session.close();      
			}
		}
	}
}

Mapping File

<?xml version="1.0"?> <!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN" "http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd"> <hibernate-mapping auto-import="false"> <sql-query name='proc_name' callable="true"> { call SCHEMA.proc_name(?,:param1) } </sql-query>


Stack Trace:


Conenction DB URL jdbc:oracle:thin:@ldap://myhost.initrode.com:port/myDN,cn=OracleContext,dc=initrode,dc=com
Hibernate: { call SCHEMA.proc_name(?,?) }
org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2236)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2130)
at org.hibernate.loader.Loader.list(Loader.java:2125)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1724)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)
at com.initrode.dao.GenericDaoImpl.callProc(GenericDaoImpl.java:43)
at com.initrode.GenericStoredProc.main(GenericStoredProc.java:40)
Caused by: java.sql.SQLException: ORA-06550: line 1, column 7:
-00201: identifier SCHEMA.PROC_NAMEL' must be declared
ORA-06550: line 1, column 7:


at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:111)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:330)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:287)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:744)
at oracle.jdbc.driver.T4CCallableStatement.doOall8(T4CCallableStatement.java:217)
at oracle.jdbc.driver.T4CCallableStatement.executeForRows(T4CCallableStatement.java:968)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1189)
at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3369)
at oracle.jdbc.driver.OraclePreparedStatement.execute(OraclePreparedStatement.java:3475)
at oracle.jdbc.driver.OracleCallableStatement.execute(OracleCallableStatement.java:4399)
at org.hibernate.dialect.Oracle8iDialect.getResultSet(Oracle8iDialect.java:437)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:216)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
at org.hibernate.loader.Loader.doQuery(Loader.java:696)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:258)
at org.hibernate.loader.Loader.doList(Loader.java:2233)
... 8 more