I am having issues attempting to generate a TypedView result from a stored procedure with multiple result sets.

I work for a paying customer, please contact me if more details are needed. I need to know if this functionality is available so that I can either move forward with it, or manually map the result sets to get the values into code.

Notes: I have checked the checkbox in the 2nd step of the wizard, and I have also manually selected "2 or more.." from the context menu. However, the option to "reverse engineer..." is greyed out and not available. I have generated the typedviews successfully with another "single result set" SProc, but I can't figure out how to get this thing to work.

Each time I generate or "refresh" the data the checkboxfor this sproc is unchecked on step 2, while the other SProc's default as checked.

Are there specific requirements for a SProc to be able to generate a TypedView?

I have provided the psuedo stored procedure as I cannot post the actual. I have changed the names and it may not compile, however, it follows the same logic as the actual procedure i am attempting to map. *I didnt write it:

PROCEDURE get_something_details ( p_something_id IN NUMBER, p_client_id IN NUMBER, -- p_rset IN OUT sys_refcursor) p_something_header_rset IN OUT sys_refcursor, p_something_attachments_rset IN OUT sys_refcursor, p_something__codes_rset IN OUT sys_refcursor, p_something_line_items_rset IN OUT sys_refcursor, p_something_discount_tax_ship_rset IN OUT sys_refcursor) AS a_bstatus NUMBER; a_module_name VARCHAR2 (60) := 'somethings_1.get_something_details'; BEGIN ppro_utils_1.start_trace (a_module_name);

-- something Header

  OPEN p_something_header_rset FOR
     SELECT something.somethingid, something.somethingnum, something.subject, something.total,
            get_something_status_code (somethingid, bstatus, sstatus) AS status_code,
            get_something_status_date (somethingid, bstatus, sstatus) AS status_date,
            DECODE (bstatus,
                    0, 1,
                    10, 1,
                    11, 1,
                    12, 1,
                    13, 1,
                    21, 1,
                    0) AS is_deleted,
            someoneid AS someone_client_id, supplierid AS supplier_client_id,
            something.privatemessage AS someone_notes,
            something.publicmessage AS supplier_notes,

            --
            someone_addr_vw.address_line_1 AS someone_address_line_1,
            someone_addr_vw.address_line_2 AS someone_address_line_2,
            someone_addr_vw.address_line_3 AS someone_address_line_3,
            someone_addr_vw.somethingstal_zip AS someone_somethingstal_zip,
            someone_addr_vw.city AS someone_city,
            someone_addr_vw.state AS someone_state,
            someone_addr_vw.country AS someone_country,
            someone_addr_vw.telecom_number_1 AS someone_phone,
            someone_addr_vw.electronic_address AS someone_email,
            someone_client.contact AS someone_contact,

            --
            supplier_client.companyname AS supplier_companyname,
            supplier_addr_vw.address_line_1 AS supplier_address_line_1,
            supplier_addr_vw.address_line_2 AS supplier_address_line_2,
            supplier_addr_vw.address_line_3 AS supplier_address_line_3,
            supplier_addr_vw.somethingstal_zip AS supplier_somethingstal_zip,
            supplier_addr_vw.city AS supplier_city,
            supplier_addr_vw.state AS supplier_state,
            supplier_addr_vw.country AS supplier_country,
            supplier_addr_vw.telecom_number_1 AS supplier_phone,
            supplier_addr_vw.electronic_address AS supplier_email,
            supplier_client.contact AS supplier_contact
       FROM dbschema.somethingmain something,
            dbschema.client someone_client,
            dbschema.client supplier_client,
            dbschema.client_address_vw someone_addr_vw,
            dbschema.client_address_vw supplier_addr_vw
      WHERE something.somethingid = p_something_id
        AND something.someoneid = someone_client.clientid
        AND something.someoneid = someone_addr_vw.client_id
        AND something.supplierid = supplier_client.clientid
        AND something.supplierid = supplier_addr_vw.client_id;

-- something Attach

  OPEN p_something_attachments_rset FOR
     SELECT   attachid AS attachment_id, somethingid AS something_id,
              originalfile AS original_file_name, description
         FROM dbschema.attachsomething
        WHERE somethingid = p_something_id
     ORDER BY 1;

-- something Codes

  --655      --tax

--656 --shipping --657 --discount OPEN p_something__codes_rset FOR SELECT _code, _cd_description sub_total_amt, discount_amt, shipping_amt, taxes_amt, sub_total + discount_amt + shipping_amt + taxes_amt AS total_amt FROM (SELECT acct_gc._cd AS _code, acct_gc.description AS _cd_description, SUM (somethingg.total) AS sub_total, (SELECT SUM (amount) FROM dbschema.something__breakout something_b, dbschema.acct__codes acct_gc WHERE something_b.something_id = p_something_id AND something_b._cd_id = acct_gc._cd_id AND breakout_type_id = 657 GROUP BY acct_gc._cd, acct_gc.description) AS discount_amt, (SELECT SUM (amount) FROM dbschema.something__breakout something_b, dbschema.acct__codes acct_gc WHERE something_b.something_id = p_something_id AND something_b._cd_id = acct_gc._cd_id AND breakout_type_id = 656 GROUP BY acct_gc._cd, acct_gc.description) AS shipping_amt, (SELECT SUM (amount) FROM dbschema.something__breakout something_b, dbschema.acct__codes acct_gc WHERE something_b.something_id = p_something_id AND something_b._cd_id = acct_gc._cd_id AND breakout_type_id = 655 GROUP BY acct_gc._cd, acct_gc.description) AS taxes_amt FROM dbschema.somethingmain something, dbschema.somethinggrid somethingg, dbschema.client someone_client, dbschema.acct__codes acct_gc WHERE something.somethingid = p_something_id AND something.somethingid = somethingg.somethingid AND something.someoneid = someone_client.clientid AND someone_client.companyid = acct_gc.company_id AND acct_gc._cd = somethingg.accountingcode GROUP BY acct_gc._cd, acct_gc.description);

-- Line Items

-- Only show current catalog item data if the something is not yet submitted to the supplier. SELECT bstatus INTO a_bstatus FROM dbschema.somethingmain WHERE somethingid = p_something_id;

  IF a_bstatus IN (1, 2, 5) THEN --New, Pending approval, Manager declined
     -- Get the current catalog data
     OPEN p_something_line_items_rset FOR
        SELECT somethingg.lineitem, somethingg.quantity, ph.uom, pp.price AS price,
               somethingg.total, somethingg.itemnum, somethingg.accountingcode AS _code,
               somethingg.description, somethingg.product_type_cd,
               cfp.is_formulary_item
          FROM dbschema.somethingmain something,
               dbschema.somethinggrid somethingg,
               dbschema.productheader ph,
               dbschema.clientfavoritecategories cfc,
               dbschema.clientfavoriteproducts cfp,
               dbschema.productpricing pp,
               dbschema.catalogsomeone cb
         WHERE something.somethingid = p_something_id
           AND something.somethingid = somethingg.somethingid
           AND somethingg.productid = ph.productid
           AND ph.productid = cfp.productid
           AND cfp.favoritecategoryid = cfc.favoritecategoryid
           AND cfc.clientid = something.someoneid
           AND somethingg.productid = pp.productid
           AND somethingg.catalogid = pp.catalogid
           AND somethingg.categoryid = pp.categoryid
           AND pp.catalogid = cb.catalogid
           AND pp.pricegroupid = cb.pricegroupid
           AND cb.someoneid = something.someoneid
        UNION
        -- Get the non-catalog (appended) something item data
        SELECT somethingg.lineitem, somethingg.quantity, somethingg.uom, TO_CHAR (somethingg.price),
               somethingg.total, somethingg.itemnum, somethingg.accountingcode,
               somethingg.description, somethingg.product_type_cd, NULL
          FROM dbschema.somethingmain something, dbschema.somethinggrid somethingg
         WHERE something.somethingid = p_something_id
           AND something.somethingid = somethingg.somethingid
           AND somethingg.productid IS NULL;
  ELSE
     -- Get the static something item data
     OPEN p_something_line_items_rset FOR
        SELECT somethingg.lineitem, somethingg.quantity, somethingg.uom, somethingg.price, somethingg.total,
               somethingg.itemnum, somethingg.accountingcode, somethingg.description,
               somethingg.product_type_cd, cfp.is_formulary_item
          FROM dbschema.somethingmain something,
               dbschema.somethinggrid somethingg,
               dbschema.productheader ph,
               dbschema.clientfavoritecategories cfc,
               dbschema.clientfavoriteproducts cfp
         WHERE something.somethingid = p_something_id
           AND something.somethingid = somethingg.somethingid
           AND somethingg.productid = ph.productid
           AND ph.productid = cfp.productid
           AND cfp.favoritecategoryid = cfc.favoritecategoryid
           AND cfc.clientid = something.someoneid;
  END IF;

-- something D/T/S

  OPEN p_something_discount_tax_ship_rset FOR
     SELECT   breakout_type_id, acct_gc._cd AS _code, percentage,
              amount
         FROM dbschema.something__breakout something_b,
              dbschema.acct__codes acct_gc
        WHERE something_b.something_id = p_something_id
          AND something_b._cd_id = acct_gc._cd_id
     ORDER BY breakout_type_id, acct_gc._cd;

  ppro_utils_1.stop_trace (a_module_name);

EXCEPTION WHEN OTHERS THEN ppro_error_handling.process_error (TO_CHAR (p_client_id), a_module_name); END;

Which Designer version are you using? Could you please attach a repro DB Script and llblgen proj file?

I upgraded to the latest today to see if it would solve this problem.. but to no avail.

Version 3.5 Final: Released On: January 17th, 2013

Is there an e-mail or private message board I can post / send the requested details to? I'd prefer to not post the DB scripts publicly

and you're asking for the results from "Generate Database Schema Create Script (DDL SQL)" correct? Again, if i could send via Private message I'd feel much more comfortable..

Thanks for the prompt reply, I'm looking forward to resolving this issue. I'm sure it's just my unfamiliarity with the Tool.

When you enable the "Resulset retrieval" in the 2nd step of the Retrieval Wizard, the Designer tries to run your SP with NULL parameters, it will rollback that anyway. If, for whatever the SP fails to return a resulset, then the metadata of the SP can't be determined, so you can't reverse it to a TypedView. So, in short: your SP must work with NULL parameters and actually return some data in your cursors. You could make a simpler version of the SP just to refresh the resultset structure.

See this similar thread: http://www.llblgen.com/TinyForum/Messages.aspx?ThreadID=18625&StartAtMessage=0&#104378

BTW, if you need to post sensitive code you could open a thread in the HelpDesk forum which is private.

AWESOME!! got it.. I'm sure that is what is going here, it doesnt like the null values... Thanks for the various infos, very helpful.

Just a quick thought.. is there any whay to define the parameteres which are used to qualify the Stored Proc? maybe some kind of configuration file / assembly? that way I could craft the inputs? ..just wondering... ..would be cool though!

Thanks again!

Just the Designer, and no real time crafting for the parameters.

okay, thanks for the quick response. Great Support!