tag:blogger.com,1999:blog-106449775438946661.post4883989263457009004..comments2024-03-19T05:14:38.879-07:00Comments on Overcoming Fear: Spring, Hibernate and Oracle Stored ProceduresTim Myerhttp://www.blogger.com/profile/01514263555690798238noreply@blogger.comBlogger43125tag:blogger.com,1999:blog-106449775438946661.post-30999905539978250892015-09-18T06:20:30.849-07:002015-09-18T06:20:30.849-07:00Hi,i am getting this error "org.hibernate.Que...Hi,i am getting this error "org.hibernate.QueryException: Expected positional parameter count: 1, actual parameters: [] [CALL usp_LoginDetails(?, :p_login)]"<br /><br />My SP file:<br />create or replace PROCEDURE usp_LoginDetails<br /><br />(<br /> <br /> p_out IN OUT SYS_REFCURSOR,<br /> p_login IN VARCHAR2<br /> <br />)<br />AS<br />login VARCHAR2(50);<br />BEGIN<br /><br />--login := p_login;<br /><br /><br />open p_out for<br />select ms.moduleId,ms.parentModule,ms.headorder,ms.menuname,ms.clsname,ms.headId,ms.parentId,ms.sessiontime,ms.menuorder<br />,ms.ctx,ms.toolbartype,ms.css,ms.windowsize,ms.webclsname<br />from usermapping um inner join modulestructure ms on um.moduleId=ms.moduleId<br />where um.loginname=p_login and ms.status='A';<br /><br />END;<br /><br />My HBM file <br /><br /><br /> <br /> <br /> <br /> <br /><br /><br />My Dao implimentation file :<br /><br /> public LoginProcedure getLoginProcedureByNamedQuery(String p_login) {<br /> //throw new UnsupportedOperationException("Not supported yet."); //To change body of generated methods, choose Tools | Templates.<br /> <br /> try {<br /> ServiceRegistryBuilder().applySettings(configuration.getProperties()).build();<br /><br /> sessionFactory.openSession().getNamedQuery("dd").setParameter("p_login", p_login);<br /><br /> Query lquery = getHibernateTemplate().getSessionFactory().openSession().getNamedQuery("dd");<br /> lquery.setParameter("p_login", p_login);<br /> <br /> List ll= lquery.list();<br /> <br /> System.out.println("value of ll"+ll);<br /> <br /> <br /> <br /> <br /> }catch(Exception e){<br /> e.printStackTrace();<br /> }<br /><br /> return null;<br /> }<br /> <br /> <br /> <br />}Manishhttps://www.blogger.com/profile/05518652757256588559noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-4355711984619757582010-11-15T09:27:13.814-08:002010-11-15T09:27:13.814-08:00Hi,
My requirement is to pass an blob object to an...Hi,<br />My requirement is to pass an blob object to an oracle procedure.<br />I am able to save & retrieve a blob object using hibernate in a table when the blob is inside a Entity.<br />However when i try to pass a blob to a procedure i get the exception<br />"java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB"<br /><br />My code consist of:<br />session.doWork(new Work() {<br />public void execute(Connection connection) throws SQLException {<br />CallableStatement call = connection.prepareCall("{call ORACLE_STORED_PROCEDURE(?)}");<br />call.setBlob(1, Hibernate.createBlob(byteData));// byteData is a byte[]<br />call.execute();<br />}<br />});<br /><br />I am using Hibernate 3.3.2.GA & oracle 10g-ojdbc14.jar.<br /><br />The stacktrace is:<br />java.lang.ClassCastException: org.hibernate.lob.SerializableBlob incompatible with oracle.sql.BLOB<br />at oracle.jdbc.driver.OraclePreparedStatement.setBlobInternal(OraclePreparedStatement.java:5749)<br />at oracle.jdbc.driver.OracleCallableStatement.setBlob(OracleCallableStatement.java:4361)<br />at com.MyDAO$1.execute(MyDAO.java:1437)<br />at org.hibernate.impl.SessionImpl.doWork(SessionImpl.java:1858)<br />at MyDAO.method1(MyDAO.java:1434)<br /><br />Pls provide a resolution.Anonymoushttps://www.blogger.com/profile/03195343906372275118noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-33099935939906946622010-11-14T21:45:37.513-08:002010-11-14T21:45:37.513-08:00cool, very informative post! i also wanna share th...cool, very informative post! i also wanna share this very spoon fed version of Spring + Hibernate + JPA combo<br /><br /><a href="http://www.adobocode.com/spring/spring-with-hibernate-annotations" rel="nofollow">http://www.adobocode.com/spring/spring-with-hibernate-annotations</a><br /><br />tnxbisayahttps://www.blogger.com/profile/17319012708811201882noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-79705260969828827522010-09-28T14:25:28.193-07:002010-09-28T14:25:28.193-07:00Hi Vijji,
I imagine you would use the same proces...Hi Vijji,<br /><br />I imagine you would use the same process as in this blog posting to call the SQL Server stored procedure. The annotated Java syntax should be similar. As for the stored procedure syntax, it has been quite a while since I have written T-SQL, so I am sure you could find more knowledgeable resources out there.<br /><br />Best of luck!<br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-83746779085859886352010-09-28T11:52:24.048-07:002010-09-28T11:52:24.048-07:00continuation to above question..
i am using SQL s...continuation to above question..<br /><br />i am using SQL server 2000 DBUnknownhttps://www.blogger.com/profile/07418360820887588098noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-4990915044712603592010-09-28T11:45:39.305-07:002010-09-28T11:45:39.305-07:00Need to have the sample code to call stored proced...Need to have the sample code to call stored procedure in DAO(hibernate) and in mapping file tooUnknownhttps://www.blogger.com/profile/07418360820887588098noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-83296620400079576462010-09-28T11:44:39.122-07:002010-09-28T11:44:39.122-07:00I need to call stored procedure using hibernate
Co...I need to call stored procedure using hibernate<br />Could you please give the sample code to be written in DAO and maping fileUnknownhttps://www.blogger.com/profile/07418360820887588098noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-11913536862079311412010-08-18T08:00:11.992-07:002010-08-18T08:00:11.992-07:00Hi joanoj,
Thanks for the feedback and for the fo...Hi joanoj,<br /><br />Thanks for the feedback and for the follow-up!<br /><br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-20818200528491481482010-08-18T07:32:23.647-07:002010-08-18T07:32:23.647-07:00At last, I'm using:
Long count = (Long) (getS...At last, I'm using:<br /><br />Long count = (Long) (getSession().createQuery("select count(*) from Locator where valuetype = "+type).uniqueResult());<br /><br />And works fine!joanojhttps://www.blogger.com/profile/05638621899941114339noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-48949890693617812042010-08-18T04:00:51.904-07:002010-08-18T04:00:51.904-07:00Great post, but why if I need to call a oracle fun...Great post, but why if I need to call a oracle function that returns a NUMBER?<br />I've been looking for an examples with no luck, so what do you think about?<br />Any idea will be wellcomed.<br />Thanksjoanojhttps://www.blogger.com/profile/05638621899941114339noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-41550444895982721112010-06-10T20:26:18.251-07:002010-06-10T20:26:18.251-07:00Hi Kwame,
That is an interesting question and I i...Hi Kwame,<br /><br />That is an interesting question and I imagine the answer would be a bit more straightforward than using Hibernate. This resource should provide you a good starting point to using the Spring JDBC Template: http://static.springsource.org/spring/docs/2.0.x/reference/jdbc.html<br /><br />I hope that helps!<br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-11268888785133971782010-06-09T14:08:40.412-07:002010-06-09T14:08:40.412-07:00Very nice tuto. Thank you. But if I had to use spr...Very nice tuto. Thank you. But if I had to use spring jdbc instead of hibernate, please can you tell me how I can do it?<br />Best regards,<br />Kwamekwamehttps://www.blogger.com/profile/08569772561825110808noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-77966931370323600992010-05-24T05:50:24.633-07:002010-05-24T05:50:24.633-07:00Hi NoMansLand,
Thanks for the question.
Have you t...Hi NoMansLand,<br />Thanks for the question.<br />Have you taken a look at this thread:<br /><br />http://forums.oracle.com/forums/thread.jspa?threadID=698746&tstart=-2<br /><br />Or at this Hibernate issue:<br /><br />http://opensource.atlassian.com/projects/hibernate/browse/HHH-3159<br /><br />Which versions of the Oracle driver and Hibernate are you using, and are you referencing a deprecated type?<br /><br />I hope that helps,<br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-55944445746876083702010-05-24T02:22:07.884-07:002010-05-24T02:22:07.884-07:00"hi, sorry to bother you, but i always get a
..."hi, sorry to bother you, but i always get a<br />org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value<br /><br />what exactly is wrong with it ?"<br /><br />I mhaving the same problem, what exactly is wrong with it ?NoMansLandhttps://www.blogger.com/profile/18445668210964183678noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-10251289890633236852010-05-19T10:15:10.715-07:002010-05-19T10:15:10.715-07:00Hi Harishankar,
Thank you for the feedback and fo...Hi Harishankar,<br /><br />Thank you for the feedback and for the question. I am not entirely clear on what you are looking to do. If you would like to have Hibernate call a stored procedure that uses more than one OUT parameter, then I believe this is not supported, but I might be incorrect. Perhaps the information you seek is here:<br />http://docs.jboss.org/hibernate/core/3.3/reference/en/html/querysql.html#sp_query<br /><br />I hope that helps!<br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-27998393881713003242010-05-19T01:43:58.964-07:002010-05-19T01:43:58.964-07:00Hi Tim,
I have one doubt in calling a oracle store...Hi Tim,<br />I have one doubt in calling a oracle stored procedure which is returning more than one out_parameter(sys_refcursor), I would like to know how we will use it in hibernate for calling this procedure or how can we do it in jav with the help of hibernate.Harishankar Naganathanhttps://www.blogger.com/profile/09486530846560359696noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-63169990477802287452010-03-14T20:38:13.709-07:002010-03-14T20:38:13.709-07:00hi, sorry to bother you, but i always get a
org.s...hi, sorry to bother you, but i always get a <br />org.springframework.orm.hibernate3.HibernateSystemException: Problem while trying to load or access OracleTypes.CURSOR value; nested exception is org.hibernate.HibernateException: Problem while trying to load or access OracleTypes.CURSOR value<br /><br />what exactly is wrong with it ?Unknownhttps://www.blogger.com/profile/00884807407179457234noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-41514744380198657162010-01-07T15:04:44.997-08:002010-01-07T15:04:44.997-08:00Hi I am new to hibernate... I just tried one examp...Hi I am new to hibernate... I just tried one example.<br /><br />Mapping File : <br /> <br /> <br /><br /><br /> <br /> { call clr0051_merch_profit_summary(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) }<br /> <br /> <br /> {? = call APP_SECURITY.WS$$HIERARCHY.GETWHERECLAUSEFORUSER(?, ?, ?, ?, ?) }<br /><br /><br />Java Class<br /><br />package de.laliluna.example;<br /><br />import java.util.ArrayList;<br />import java.util.List;<br /><br />import org.hibernate.JDBCException;<br />import org.hibernate.Query;<br />import org.hibernate.Session;<br />import org.hibernate.Transaction;<br />import org.slf4j.Logger;<br />import org.slf4j.LoggerFactory;<br /><br />import de.laliluna.hibernate.SessionFactoryUtil;<br /><br />public class TestExample {<br /><br /> final static Logger logger = LoggerFactory.getLogger(TestExample.class);<br /><br /> /**<br /> * @param args<br /> */<br /> public static void main(String[] args) {<br /><br /> Transaction tx = null;<br /> Session session = SessionFactoryUtil.getInstance().openSession();<br /> try {<br /> tx = session.beginTransaction();<br /> List merList = new ArrayList();<br /> Query query = session.getNamedQuery("excelGetter");<br /> query.setLong(0, 336790);<br /> query.setLong(1, 845);<br /> query.setString(2, "NORTH");<br /> query.setString(3, "BUSINESS");<br /> query.setString(4, null);<br /> merList = query.list();<br /> System.out.println("Size" + merList.size());<br /> tx.commit();<br /> } catch (JDBCException e) {<br /> System.out.println(e.getMessage());<br /> } catch (Exception e) {<br /> System.out.println(e.getMessage());<br /> } finally {<br /> session.close();<br /> }<br /><br /> }<br />}<br /><br /><br />When I run it I am getting :<br /><br />org.hibernate.exception.SQLGrammarException: could not execute query<br /><br /><br />ORA-06550: line 1, column 13:<br />PLS-00201: identifier 'APP_SECURITY.WS$$HIERARCHY' must be declared<br />ORA-06550: line 1, column 7:<br />PL/SQL: Statement ignored<br /><br />Can You please help me finding where I am wrong ?Unknownhttps://www.blogger.com/profile/02411478736350335684noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-48882790494775890392009-12-30T07:16:30.600-08:002009-12-30T07:16:30.600-08:00Hi Puteri,
Thanks very much for the feedback. Yo...Hi Puteri,<br /><br />Thanks very much for the feedback. You are quite right about the junit library omission. Of course, now, I would probably recommend using a maven2 setup rather than downloading and copying the jars directly. In that case, adding the junit dependency would just be a matter of adding a few lines to the pom.<br /><br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-89811231211883597532009-12-29T22:30:26.786-08:002009-12-29T22:30:26.786-08:00hi...
thanks for sharing the info...
really help ...hi...<br /><br />thanks for sharing the info...<br />really help me lot.<br />but i think you miss i lib...<br />JUnit Lib...Balqis Umairshttps://www.blogger.com/profile/00432595381521717153noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-70916263156518094242009-07-29T07:33:18.491-07:002009-07-29T07:33:18.491-07:00Hi Seam,
Thank you for the feedback.
Could you p...Hi Seam,<br />Thank you for the feedback. <br />Could you please be more specific about the issue that you are seeing and perhaps provide an example?<br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-35202069696504136182009-07-29T07:14:51.482-07:002009-07-29T07:14:51.482-07:00this tuto isn't correct!!!!!!!!!!!!!!!!
how we...this tuto isn't correct!!!!!!!!!!!!!!!!<br />how we can give de second parametre to our procedure <br />there is a probleme with number and type of parametresaberchanehttps://www.blogger.com/profile/16265239510095142649noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-67019193268636685642009-06-28T15:59:18.160-07:002009-06-28T15:59:18.160-07:00For an example of how to map a hibernate entity to...For an example of how to map a hibernate entity to something other than a table, please see this post:<br /><br />http://timezra.blogspot.com/2009/05/mapping-hibernate-entities-to-views.html<br /><br />---Tim---Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-47523501719268556482009-05-05T11:35:00.000-07:002009-05-05T11:35:00.000-07:00This comment has been removed by the author.Tim Myerhttps://www.blogger.com/profile/01514263555690798238noreply@blogger.comtag:blogger.com,1999:blog-106449775438946661.post-29093033044047806302009-05-05T09:07:00.000-07:002009-05-05T09:07:00.000-07:00Could you email me the example that you have?Could you email me the example that you have?Kayhttps://www.blogger.com/profile/09799518490330819510noreply@blogger.com