5 Replies Latest reply on Feb 15, 2008 9:29 PM by 623473

    ref cursor in select statement

      I am not sure this is the right forum, but I got no reply from the OTN Sample.

      How do I call a function from a select
      statement ?
      The function returns a ref cursor.

      Here is the function definition:
      FUNCTION get_priority_code_list
      (p_language_id IN VARCHAR2)
      RETURN RefCur
      rcur RefCur;
      OPEN rcur FOR SELECT
      priority_code "priority_code_menu",
      priority_code_desc "priority_code_menu_desc"
      language_id = p_language_id;
      RETURN rcur;

      I would like to wrap a SELECT statement
      around the function and have the same result
      as running the SELECT statement directly.

      Of course it does not seem logical here to
      use a function, but in fact I want the
      function to do more than just a select....

      I tried the syntax

      SELECT package.function(param) FROM dual;
      but that does not work...
      SELECT * FROM package.function(param);
      but that does not work either....

      any idea ?


        • 1. ref cursor in select statement
          You cannot select something that is a CURSOR in a select statement and have it be the same as if you hadn't called the function.

          In one case you're selecting rows that contains a result-set in each row.

          In the other case your just selecting the base results.

          These have different shapes.

          You can bind a JDBC parameter as type OracleTypes.CURSOR and then cast that result to a JDBC ResultSet to fetch the data from the ref cursor.
          • 2. ref cursor in select statement
            Ok Steve,

            Thanks for the reply.

            I was trying to fool an API (C) toolkit
            into thinking that it was getting results
            from a SELECT statement while in fact
            it would call a function that returned a
            ref cursor.

            I think ref cursor are very interesting, but
            I struggle to find good information about them....

            Anyway, thanks for your book and the XSQL servlet also.

            They are really good tools for what I do right now (system integration).

            I take your book home and bring it back to
            work every day....

            • 3. ref cursor in select statement
              Glad the book is proving useful to you.
              • 4. Re: ref cursor in select statement
                I have a stored function that returns a ref cursor and I want to use it in select

                I tried the following code
                for example if the ref cursor returned rows of varchar2
                I will define an object
                CREATE TYPE myType as OBJECT( doc_type VARCHAR2(256));
                created a type as a table of myType
                create or replace type myTable as table of myType;
                If I write a select statement like below

                select * from TABLE(CAST(function_name() AS myTable))
                ERROR at line 1:
                ORA-00932: inconsistent datatypes: expected - got CURSER
                I get this error

                Can anyone give any clue why this does throw an error or if there is a second way to do this,
                • 5. Re: ref cursor in select statement
                  I was trying to do the exact same thing. In my case, the IDE I am using is based on .NET framework, not Java.

                  What book are you guys talking about?