How to call a function and get return rows

2004-11-17
2013-04-11
  • I have a function the content as below:

    CREATE OR REPLACE function display_dao_test  (
       in_var IN dao_test.int_tst%TYPE )
        RETURN cursor_types.ref
    IS
        l_rec cursor_types.ref;
    BEGIN
        OPEN l_rec FOR
        SELECT int_tst,boolean_tst,string_tst,float_tst,long_tst,date_tst
         FROM dao_test
        WHERE int_tst=in_var;

        RETURN l_rec;
    END;
    /

    How to call this function and get return data? Pls list the sqlmap details. thx

     
    • Brandon Goodin
      Brandon Goodin
      2004-11-22

      throw a store procedure on the front of your function and call it using the ibatis stored-procedure support.

      Brandon

       
    • Skimmy
      Skimmy
      2004-11-29

      I am trying to do the same thing.

      How do you call the stored function using the format
      { ? = call pkg.foo(?) }
      assuming that foo has the side effect of updating tables  and also returns a string.

      Appreciate your help in advance.

      [I know one way to handle the situation if there is no side effect of update and that is to do the following (works on oracle)
      select pkg.foo(? ) from dual
      I need information for cases where there is a side effect as the above select does not work.
      ]

       
      • Clinton Begin
        Clinton Begin
        2004-12-03

        How would you do this in JDBC?

        I'd imagine you'd use a CallableStatement with registered output parameters.  With iBATIS, the mapping is something like:

        <procedure...>
        { ? = call pkg.foo(?) }
        </procedure>

        Then either register parameters externally with mode="OUT" for the first parameter, or use inline parameters like #propName,mode=OUT#.

        Cheers,
        Clinton

        PS: THIS FORUM HAS MOVED TO THE APACHE MAILING LISTS. 

        http://www.ibatis.com/support.html