Oracle Object inside object inside table

Question

I have problem with oracle objects. I am writing a Function. which have SELECT:

CURSOR cResultValues (p_vrc_mnemo VARCHAR2,
                      p_pdt_mnemo VARCHAR2,
                      p_table t_crt_list_prdt_conf_tab) IS
SELECT pdt_grp_mnemo,
       pdt_mnemo,
       pdt_variant,
  FROM TABLE(p_table)
 WHERE pdt_mnemo = p_pdt_mnemo AND
       pdt_variant = p_vrc_mnemo;

and to make more clear global types:

CREATE OR REPLACE TYPE t_pdt_config_rec IS OBJECT( 
  pdt_grp_mnemo                VARCHAR2(30),
  pdt_mnemo                    VARCHAR2(30),
  pdt_variant                  VARCHAR2(30), 
/
CREATE OR REPLACE TYPE t_pdt_config_tab IS TABLE OF t_pdt_config_rec
/
-------------------------------------------------------------------------------------
CREATE OR REPLACE TYPE t_list_conf_rec IS OBJECT(
  pdt_conf   t_pdt_config_rec,  -- product info
  pdt_childs t_pdt_config_tab)  -- products compinations
/
CREATE OR REPLACE TYPE t_list_conf_tab IS TABLE OF t_list_conf_rec
/

And so on. Before changes there was only t_pdt_config_tab and I have no problems.

How could I reach information inside pdt_conf object?

Answer 1

It is not clear what your problem is. I can only guess that you changed the type of the procedure parameter from t_pdt_config_rec to a more complex object type t_list_conf_rec and now you can't access the values in it.

Let's build a similar example:

SQL> CREATE OR REPLACE TYPE t_pdt_config_rec IS OBJECT(
  2    pdt_mnemo                    VARCHAR2(30),
  3    pdt_variant                  VARCHAR2(30))
  4  /

Type created

SQL> CREATE OR REPLACE TYPE t_pdt_config_tab IS TABLE OF t_pdt_config_rec
  2  /

Type created

SQL> CREATE OR REPLACE TYPE t_list_conf_rec IS OBJECT(
  2    pdt_conf   t_pdt_config_rec,  -- product configuration and info
  3    pdt_childs t_pdt_config_tab)   -- similar or same products cobinations
  4  /

Type created

SQL> CREATE OR REPLACE TYPE t_list_conf_tab IS TABLE OF t_list_conf_rec
  2  /

Type created

Accessing sub-objects in PL/SQL is not unlike java:

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2     l_conf_1     t_pdt_config_rec := t_pdt_config_rec('conf 1','A');
  3     l_conf_2     t_pdt_config_rec := t_pdt_config_rec('conf 2','B');
  4     l_child_1    t_pdt_config_rec := t_pdt_config_rec('conf 1 old', 'AA');
  5     l_child_2    t_pdt_config_rec := t_pdt_config_rec('conf 1 old old','AB');
  6     l_children_1 t_pdt_config_tab := t_pdt_config_tab(l_child_1, l_child_2);
  7     l_children_2 t_pdt_config_tab := t_pdt_config_tab();
  8     l_obj_1      t_list_conf_rec  := t_list_conf_rec(l_conf_1, l_children_1);
  9     l_obj_2      t_list_conf_rec  := t_list_conf_rec(l_conf_2, l_children_2);
 10     l_tab        t_list_conf_tab  := t_list_conf_tab(l_obj_1, l_obj_2);
 11  BEGIN
 12     FOR cc IN (SELECT o.pdt_conf.pdt_mnemo    pdt_mnemo, 
 13                       o.pdt_conf.pdt_variant pdt_variant 
 14                  FROM TABLE(l_tab) o
 15                 WHERE o.pdt_conf.pdt_mnemo = 'conf 1'
 16                   AND o.pdt_conf.pdt_variant = 'A') LOOP
 17        dbms_output.put_line('record found');
 18     END LOOP;
 19  END;
 20  /

record found

PL/SQL procedure successfully completed

Answer 2

Before changes there was only t_pdt_config_tab and I have no problems.

Of course.

You now have a NESTED array. so your outer TABLE(p_table) will be selecting the rows of t_crt_list_prdt_conf_tab (whatever that is, did you mean to type t_list_conf_tab??).

ill answer assuming you meant t_list_conf_tab and not t_crt_list_prdt_conf_tab. if t_crt_list_prdt_conf_tab is a type that contains t_list_conf_tab, then you'll need another level:

select list_conf.id list_conf_id, 
       list_conf.pdt_conf.pdt_grp_mnemo,
       list_conf.pdt_conf.pdt_mnemo,
       list_conf.pdt_conf.pdt_name,
       list_conf.pdt_conf.pdt_variant,
       list_conf.pdt_conf.det_info_xsr_id ,
       list_conf.pdt_conf.det_info_view_template_name ,
       list_conf.pdt_conf.det_info_download_xsl_id,
       list_conf.pdt_conf.det_info_ctrl_url,
       list_conf.pdt_conf.det_info_ctrl_action,
       list_conf.pdt_conf.create_ctrl_url,
       list_conf.pdt_conf.create_ctrl_action,
       list_conf.pdt_conf.change_contract_name_enabled,
       list_conf.pdt_conf.period_selector,
       list_conf.pdt_conf.period_selector_hide_all_opt,
       pdt_child.pdt_grp_mnemo,
       pdt_child.pdt_mnemo,
       pdt_child.pdt_name,
       pdt_child.pdt_variant,
       pdt_child.det_info_xsr_id,
       pdt_child.det_info_view_template_name,
       pdt_child.det_info_download_xsl_id,
       pdt_child.det_info_ctrl_url,
       pdt_child.det_info_ctrl_action,
       pdt_child.create_ctrl_url,
       pdt_child.create_ctrl_action,
       pdt_child.change_contract_name_enabled,
       pdt_child.period_selector,
       pdt_child.period_selector_hide_all_opt,
       pdt_child.downloads
  from (SELECT rownum id,
               pdt_conf,
               pdt_childs
          FROM TABLE(p_table)) list_conf,
       table(list_conf.pdt_childs) pdt_child;

sql fiddle example: http://sqlfiddle.com/#!4/2eee6/1