4 Replies Latest reply: Jun 20, 2014 3:16 AM by BluShadow RSS

    Can u store a SQL in a REF_CURSOR and reuse it?

    user12240205

      This is related to my 1st post SQL gurus, can you create an SQL to do this?

       

      So, Now I can create the SQL, but I have a problem.  My new query looks like this:

       

       

      WITHoriginal_queryAS

      (  

      VERY BIG ORIGINAL QUERY

      )

      ,   got_room_grp   AS

      (

      SELECT  SUBSTR (room_desc, INSTR (room_desc, ' - ') + 3)  AS room_grp
      ,   detail_count
      FROMoriginal_query

      )

      SELECTNVL (room_grp, 'Rooms')   AS grp_label
      ,     SUM (detail_count)    AS detail_count
      FROM  got_room_grp

      GROUP BY  ROLLUP (room_grp);

       

      The original query is HUGE.  in order to be easy in understanding and readability I want to place the big original query in to a ref_cursor.

      And then in the above quer,y put a simple SELECT * FROM ref_cursor;

       

      Is this possible??

        • 1. Re: Can u store a SQL in a REF_CURSOR and reuse it?
          RobbR

          Do you mean a view?

           

          CREATE VIEW

          Use the CREATEVIEW statement to define a view, which is a logical table based on one or more tables or views. A view contains no data itself. The tables upon which a view is based are called base tables.

          You can also create an object view or a relational view that supports LOBs, object types, REF datatypes, nested table, or varray types on top of the existing view mechanism. An object view is a view of a user-defined type, where each row contains objects, each object with a unique updatableobject identifier.

          You can also create XMLType views, which are similar to an object views but display data from XMLSchema-based tables of XMLType.

          • 2. Re: Can u store a SQL in a REF_CURSOR and reuse it?
            rp0428

            The original query is HUGE.  in order to be easy in understanding and readability I want to place the big original query in to a ref_cursor.

            And then in the above quer,y put a simple SELECT * FROM ref_cursor;

            That makes NO sense.

             

            A query does NOT become easy to read and understand simply because you move it from one location to a new location.

             

            You need to add comments and document what the query does and explain any complex predicates or constructs that the query uses.

            My new query looks like this: 

             

            WITH original_query AS

            (  

            VERY BIG ORIGINAL QUERY

            )

            ,   got_room_grp   AS

            (

            SELECT  SUBSTR (room_desc, INSTR (room_desc, ' - ') + 3)  AS room_grp
            ,   detail_count
            FROM original_query

            )

            SELECT NVL (room_grp, 'Rooms')   AS grp_label
            ,     SUM (detail_count)    AS detail_count
            FROM  got_room_grp

            GROUP BY  ROLLUP (room_grp);

            So, as already suggested, replace those 'in-line' views with actual views:

            create view vw_original_query as VERY BIG ORIGINAL QUERY;

            create view vw_got_room_grp as

            (

            SELECT  SUBSTR (room_desc, INSTR (room_desc, ' - ') + 3)  AS room_grp
            ,   detail_count
            FROM vw_original_query

            );

            Then just write your query to use the second view

            SELECT NVL (room_grp, 'Rooms')   AS grp_label
            ,     SUM (detail_count)    AS detail_count
            FROM  vw_got_room_grp

            GROUP BY  ROLLUP (room_grp);

            That last query looks 'readable' to me.

             

            But the view will NOT be easy to read or understand until you provide appropriate comments and documentation.

            • 3. Re: Can u store a SQL in a REF_CURSOR and reuse it?
              user12240205

              rp0428 wrote:

              That makes NO sense.

               

              A query does NOT become easy to read and understand simply because you move it from one location to a new location.

               

              You need to add comments and document what the query does and explain any complex predicates or constructs that the query uses.

              Well, to start with there are absolutely no comments. So I have no idea what it is doing. The original_query returns 2 columns but has 50+ lines.

               

              Now, I am using this query as input to another query which uses ROLLUP.

               

              So, that is why I want to make the 2nd query easy to read (with rollup).

               

              So I have something like this;

              WITH    original_query    AS

               

              (

                 --  Instead of VERY BIG ORIGINAL QUERY

                 SELECT * FROM ref_cursor;

               

              )

               

              ,   got_room_grp   AS

               

              (

                  SELECT  SUBSTR (room_desc, INSTR (room_desc, ' - ') + 3)  AS room_grp

                  ,       detail_count

                  FROM    original_query

               

              )

              SELECT    NVL (room_grp, 'Rooms')   AS grp_label

              ,         SUM (detail_count)        AS detail_count

              FROM      got_room_grp

               

              GROUP BY  ROLLUP (room_grp);

               

              Also, user does not want me or does not let me create new objects like view. That is my other problem. I need to do it without creating any new DB objects.

              • 4. Re: Can u store a SQL in a REF_CURSOR and reuse it?
                BluShadow

                Well a view is the appropriate tool for the job if you're not going to just include it in the query itself.

                 

                You cannot do:

                 

                SELECT * FROM ref_cursor;

                 

                That indicates you do not understand what a ref cursor is.

                 

                Please read: PL/SQL 101 : Understanding Ref Cursors