10 Replies Latest reply: Jan 26, 2011 2:45 PM by 834098 RSS

    Is it possible to query a function returning a plsql collection?

    834098
      Im trying develop a function which returns a nested table of records to an sql query but I get
      ORA-00932: inconsistent datatypes: expected - got -
      ORA-06512: at "CDB_WEB.PIPE_RESULTS", line 38

      The original version of this function (which works correctly) is a pipelined function can no longer be used as we have hit an oracle bug with pipelined functions in our production server and need to attempt a workaround ...

      This is what Ive done:

      1. create record type and nested table in schema:

      create type TXR_RECORD IS OBJECT
      ( external_id VARCHAR2(25),
      source CHAR(3),
      info1 VARCHAR2(4000),
      info2 VARCHAR2(4000),
      info3 VARCHAR2(4000),
      info4 VARCHAR2(4000));
      /
      CREATE type txr_table IS TABLE OF txr_record;
      /

      2. compiled plsql function to populate the nested table:

      CREATE OR REPLACE FUNCTION pipe_results (p_external_id VARCHAR2,
      p_source VARCHAR2,
      p_from INTEGER,
      p_to INTEGER)
      RETURN txr_table
      IS

      l_txr txr_table := txr_table();
      rettab txr_table := txr_table();
      l_from INTEGER := 0;
      l_to INTEGER := 5;
      sqlstr varchar2(4000) := NULL;


      TYPE cv_type IS REF CURSOR;
      cv1 cv_type;

      BEGIN

      sqlstr := 'SELECT external_id, source, info1, info2, info3, info4
      FROM xrefs_sptr
      WHERE source = : x (this reads : x without the gap in the code but it appears as a smily face if I repeat it here)
      AND external_id = :y
      ORDER BY info4';


      OPEN cv1 for sqlstr USING p_source, p_external_id;
      FETCH cv1 BULK COLLECT INTO l_txr;
      CLOSE cv1;

      l_from := p_from;
      l_to := p_to;

      FOR i IN p_from .. p_to
      LOOP
      IF l_txr(i) IS NOT NULL
      THEN
      rettab.extend;
      rettab(i) := l_txr(i);
      END IF;
      END LOOP;

      return rettab;
      END;
      /


      3. so far all seems ok but when I try and call it from sql:

      select external_id, source, info1, info2, info3, info4
      from table(pipe_results('PMC1687509', 'PMC', 1, 5));

      I get the error:

      from     table(pipe_results('PMC1687509', 'PMC', 1, 5))
      *
      ERROR at line 2:
      ORA-00932: inconsistent datatypes: expected - got -
      ORA-06512: at "CDB_WEB.PIPE_RESULTS", line 38


      Im sure Im overlooking something basic despite rereading the docs and plsql programming Im a bit stumped .... is it possible to do what I am trying without using a pipelined function?
        • 1. Re: Is it possible to query a function returning a plsql collection?
          Boneist
          You need to cast the results of the function into a table type, eg:
          select external_id, source, info1, info2, info3, info4 
          from table(CAST(pipe_results('PMC1687509', 'PMC', 1, 5) AS txr_table)); 
          ETA: why do you need to bother using a collection in the first place? The function has a ref cursor in it; you could simply pass out the ref cursor and then the front end code could simply loop through that directly?

          Edited by: Boneist on 26-Jan-2011 11:05
          • 2. Re: Is it possible to query a function returning a plsql collection?
            afd
            Hi,

            your query
            SELECT external_id, source, info1, info2, info3, info4
            FROM xrefs_sptr
            WHERE source = : x (this reads : x without the gap in the code but it appears as a smily face if I repeat it here)
            AND external_id = :y
            ORDER BY info4
            must return TXR_RECORDs, as in
            SELECT TXR_RECORD(external_id, source, info1, info2, info3, info4)
            FROM xrefs_sptr
            WHERE source = : x (this reads : x without the gap in the code but it appears as a smily face if I repeat it here)
            AND external_id = :y
            ORDER BY info4
            Also, I don't see why you use dynamic SQL?

            Hope this helps,
            Tony
            • 3. Re: Is it possible to query a function returning a plsql collection?
              834098
              thanks guys these are very helpful answers, Im just working through them atm (btw no special reason to use dynamic sql as far as im aware, cursor would do just fine (you think it may perform better too i guess?)
              • 4. Re: Is it possible to query a function returning a plsql collection?
                afd
                Yes, you should avoid dynamic SQL whenever possible.

                And you should "paginate" (filter the p_from..p_to rows) in SQL, i.e. :
                CREATE OR REPLACE FUNCTION pipe_results (p_external_id VARCHAR2,
                p_source VARCHAR2,
                p_from INTEGER,
                p_to INTEGER)
                RETURN txr_table
                IS
                
                rettab txr_table := txr_table();
                BEGIN
                
                SELECT txr_record(external_id, source, info1, info2, info3, info4)
                bulk collect into rettab
                FROM 
                (select external_id, source, info1, info2, info3, info4, rownum rn
                 from
                (select 
                external_id, source, info1, info2, info3, info4
                from 
                xrefs_sptr
                 WHERE source = p_source
                 AND external_id = p_external_id
                ORDER BY info4
                ) where rownum <= p_to
                ) where rn >= p_from
                ;
                
                return rettab;
                END;
                • 5. Re: Is it possible to query a function returning a plsql collection?
                  Billy~Verreynne
                  A comment about this code and approach in general.

                  Firstly, it is not a pipeline. A pipeline table function looks and work different (and should have a much smaller PGA memory footprint).

                  Secondly, this approach does not really make sense. Why is SQL data copied from the SGA's buffer cache and the complete data set stored in very expensive PGA (private process) memory?

                  If that PL/SQL function is now used via SQL, then all that data in the PGA needs to be copied, again, to the SQL engine as a bind variable. This makes no sense at all - PL/SQL and PGA make a very poor buffer cache for SQL data. It is not designed for that purpose. No amount of PL/SQL code and l33t coding skillz will ever duplicate the robustness, flexibility, performance and scalability of the db buffer cache using PGA based collections.

                  It is very likely that this approach is just plain wrong - and that the correct approach would be for that function to instead open a ref cursor and return that to the caller. Allowing the caller to bypass PL/SQL to get to the data - and instead get the data directly from the SQL engine and the db buffer cache. Keep the number of moving parts to a minimum.
                  • 6. Re: Is it possible to query a function returning a plsql collection?
                    afd
                    I agree with all your points and suggestions, and of course this is not a pipelined function.

                    But, as stated in the original post:
                    user4208249  wrote:
                    The original version of this function (which works correctly) is a pipelined function can no longer be used as we have hit an oracle bug with pipelined functions in our
                    production server and need to attempt a workaround ...
                    Obviously it would be best if this "workaround" could use straight SQL and avoid PL/SQL. So, we need a clarification from the o.p.: how do you use the current pipelined function?
                    • 7. Re: Is it possible to query a function returning a plsql collection?
                      834098
                      thanks these are good points. Iam aware it is not a pipelined function (any more), the rationale behind keeping the name (and calling as plsql function) is to avoid changing production code until such time as we can return to the orignal pipelined function. But you make some good points, this is an opportunity to improve the code also thanks

                      Edited by: user4208249 on 26-Jan-2011 04:56
                      • 8. Re: Is it possible to query a function returning a plsql collection?
                        Billy~Verreynne
                        user4208249 wrote:
                        Iam aware it is not a pipelined function (any more), the rationale behind keeping the name (and calling as plsql function) is to avoid changing production code until such time as we can return to the orignal pipelined function.
                        Sorry for pointing out the obvious - simply that a pipeline function should have a significant smaller PGA footprint than a function returning a collection. :-)

                        The former use the pipe row() interface to pass data to the caller (the SQL engine in this case). The latter requires the entire collection to be created in local heap space, then the stack is popped by the PL/SQL engine and that entire collection is then copied into the heap space of the caller.

                        Not only very inefficient when dealing with a large data struct, but also expensive ito memory. Which is why passing collections in PL/SQL is best done by reference and not value - using the IN OUT NOCOPY syntax. And this automatically excludes the ability to pass data struct as a function result - and thus also of being (incorrectly) used in SQL. Which is perhaps a good thing as there is a clear and definitive border drawn around the code and its impact on run-time resources.
                        • 9. Re: Is it possible to query a function returning a plsql collection?
                          834098
                          thanks for clarifying that Bill, it wasn't obvious to me and i have learnt something valuable ;+)

                          ideally we would stick with the pipelined function, but we have hit an error when running it recently
                          ERROR at line 2:
                          ORA-06553: PLS-801: internal error [hshuid:LU invalid]

                          when executing this - looks from metalink and some discussion ive had with an oracle support person that this is related to a bug, so far though our DBA's have not managed to resolve it ... hence this attempt ;+)

                          Edited by: user4208249 on 26-Jan-2011 06:07
                          • 10. Re: Is it possible to query a function returning a plsql collection?
                            834098
                            Hi

                            This is the solution I have got to work, suggested by AFD but many thank you's to Billy for some really useful insights, and to everyone who answered, very helpful.

                            1. records and types created in schema as in OP

                            2. sql wrapped in function to avoid breaking prod code( credit to AFD):


                            CREATE OR REPLACE FUNCTION pipe_results (p_external_id VARCHAR2,
                            p_source VARCHAR2,
                            p_from INTEGER,
                            p_to INTEGER)
                            RETURN txr_table
                            IS

                            rettab txr_table := txr_table();
                            BEGIN

                            SELECT txr_record(external_id, source, info1, info2, info3, info4)
                            BULK COLLECT INTO rettab
                            FROM (SELECT external_id, source, info1, info2, info3, info4, rownum rn
                            FROM (SELECT external_id, source, info1, info2, info3, info4
                            FROM xrefs_sptr
                            WHERE source = p_source
                            AND external_id = p_external_id
                            ORDER BY info4)
                            WHERE ROWNUM <= p_to)
                            WHERE rn >= p_from;
                            RETURN rettab;
                            END;
                            /


                            3. called from sql
                            select external_id, source, info1, info2, info3, info4
                            from table(pipe_results('10022826', 'MED', 1, 5));

                            gives the corretly paginated results.

                            Thanks guys, I was pulling my hair out for days over this ;+)

                            Edited by: user4208249 on 26-Jan-2011 06:45