Announcement Announcement Module
Collapse
No announcement yet.
call oracle function from hibernate Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • call oracle function from hibernate

    Imagine I have this function oracle

    CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
    RETURN VARCHAR2 IS

    BEGIN
    outparm := 'Coming out';
    RETURN 'return param';
    END inout_fn;

    and to call it in oracle

    SET serveroutput ON

    DECLARE
    retval VARCHAR2(20);
    ioval VARCHAR2(20) := 'Going in';
    BEGIN
    DBMS_OUTPUT.put_line('In: ' || ioval);
    retval := inout_fn(ioval);
    DBMS_OUTPUT.put_line('Out: ' || ioval);
    DBMS_OUTPUT.put_line('Return: ' || retval);
    END;



    How can I call the function from hibernate, and the returned parameter i read it in my program. Any help plz?



    something similar to in sqquery.hbm.xml

    <sql-query name="mySqlQuery">

    select cou_name as couName , iso_code as isocode from ot_country person
    </sql-query>


    how can i call a function in the xml file, and then in my .java file get the returned value. plz give me example, this is urgent

  • #2
    i tried the following

    Code:
    <sql-query name="inout_fn" callable="true">
    {? = call inout_fn(?)}
    </sql-query>
    
    
    public class runquery {
        public static void main (String[] args) throws InterruptedException
        {
        //SQLQuery q1 = ( SQLQuery ) TestUtilities.getHSession().getNamedQuery( "select_address" );  
                // System.out.println(q1); 
                 SessionFactory HibernateSupport = new Configuration().configure() .buildSessionFactory();
                 Session s = HibernateSupport.openSession();
                 Query q = s.getNamedQuery("inout_fn");
       
                  q.setString(0,"going in");
            List l = q.list(); 
            for(Iterator it = l.iterator();it.hasNext();)
            {
            
    
             Object obj3 = (Object) it.next();
    
            System.out.println("State: " + obj3);
            }
    }
    I get error of Hibernate: {? = call inout_fn(?)}
    (util.JDBCExceptionReporter 77 ) SQL Error: 6550, SQLState: 65000
    (util.JDBCExceptionReporter 78 ) ORA-06550: line 1, column 13:
    PLS-00382: expression is of wrong type
    ORA-06550: line 1, column 7:
    PL/SQL: Statement ignored

    Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query

    Comment


    • #3
      here is inout_fn

      CREATE OR REPLACE FUNCTION inout_fn (outparm IN OUT VARCHAR2)
      RETURN VARCHAR2 IS

      BEGIN
      outparm := 'Coming out';
      RETURN 'return param';
      END inout_fn;


      SET serveroutput ON

      DECLARE
      retval VARCHAR2(20);
      ioval VARCHAR2(20) := 'Going in';
      BEGIN
      DBMS_OUTPUT.put_line('In: ' || ioval);
      retval := inout_fn(ioval);
      DBMS_OUTPUT.put_line('Out: ' || ioval);
      DBMS_OUTPUT.put_line('Return: ' || retval);
      END;

      if i call it like that it works..

      but how can i use the keyword call in oracle..

      this gives me an error when i try running in oracle


      call inout_fn('Going on');
      ORA-06576: not a valid function or procedure name

      Comment


      • #4
        Here is what am doing now

        Code:
        <sql-query name="inout_fn" callable="true">
        begin ? := inout_fn(?); end;
        </sql-query>
        Code:
                     SessionFactory HibernateSupport = new Configuration().configure() .buildSessionFactory();
                     Session s = HibernateSupport.openSession();
                     Query q = s.getNamedQuery("inout_fn");
                       // q.registerOutParameter(0,OracleTypes.CURSOR);
                      q.setString(1,"going in");
                      q.setString(2,"yes");
                List l = q.list(); 
                for(Iterator it = l.iterator();it.hasNext();)
                {
                
        
                 Object obj3 = (Object) it.next();
        
                System.out.println("State: " + obj3);
                }
        I get error

        (impl.SessionFactoryImpl 363 ) Error in named query: inout_fn
        org.hibernate.QueryException: Space is not allowed after parameter prefix ':' 'begin ? := inout_fn(?); end;'

        Comment


        • #5
          Did you find a solution to this problem? I have the same issue. Would much appreciate a solution. Serious blocker.

          Comment


          • #6
            Please, move your question to the StackOverflow - we are going to close this forum soon and rely on SO.
            We need to clean this forum (old unanswered question) before close it.

            Thanks for understanding

            Comment

            Working...
            X