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?

Can you build a simpler example? We don't even know what t_crt_list_prdt_conf_tab is ! Also what is your question? What error message are you getting? — Vincent Malgrat
– Vincent Malgrat, Commented Feb 1, 2013 at 9:55
I need to select information which parsed from XML by other function and stored as t_list_conf_tab. I don't know how to select table with object in it. — DARK_A
– DARK_A, Commented Feb 1, 2013 at 10:21
Why is the type of the parameter t_crt_list_prdt_conf_tab ? is it yet another object? — Vincent Malgrat
– Vincent Malgrat, Commented Feb 1, 2013 at 10:24

Vincent Malgrat · Accepted Answer · 2013-02-01 10:23:20Z

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

I tryed your suggestion in first place, does not work. failed to recognise "pdt_conf".

DazzaL · Accepted Answer · 2013-02-01 10:24:00Z

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

@DARK_A not sure what you mean by that comment. did my SQL not show you how to access the sub arrays in the way you want?
Explained what I mean with t_list_conf :) everything with your answer is fine. I solved this problem by changing structure of t_crt_list_prdt_conf_tab type and reducing complexity of procedure. Thank you for your answer.

Collectives™ on Stack Overflow

Oracle Object inside object inside table

2 Answers 2

2 Comments

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Related