Home » SQL & PL/SQL » SQL & PL/SQL » how to copy a record type's values to a object type
how to copy a record type's values to a object type [message #356615] Fri, 31 October 2008 09:15 Go to next message
wu_horizon
Messages: 4
Registered: March 2007
Junior Member
type rec is record(
item varchar2(30),
desc_ varchar2(50)
);
type t_rec is table of rec;
tt t_rec ;

type obj is object(
item varchar2(30),
desc_ varchar2(50)
);
type b_rec is table of obj;
bb b_rec ;

now tt have some values ,how to put tt's value into bb.
I don't want to use bb.item = tt.item
is there have another method?
thanks
Re: how to copy a record type's values to a object type [message #356617 is a reply to message #356615] Fri, 31 October 2008 09:25 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter) and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Post a test case: create types and al statements along with the result you want with these data.

Regards
Michel
Re: how to copy a record type's values to a object type [message #356622 is a reply to message #356617] Fri, 31 October 2008 09:43 Go to previous messageGo to next message
wu_horizon
Messages: 4
Registered: March 2007
Junior Member
I am so sorry
That is my first question in this forum
Re: how to copy a record type's values to a object type [message #356623 is a reply to message #356622] Fri, 31 October 2008 09:57 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
OK, but now do it, post a test case as well as your Oracle version (with 4 decimals).

Regards
Michel

[Updated on: Fri, 31 October 2008 09:57]

Report message to a moderator

Re: how to copy a record type's values to a object type [message #356631 is a reply to message #356623] Fri, 31 October 2008 10:28 Go to previous messageGo to next message
wu_horizon
Messages: 4
Registered: March 2007
Junior Member
First I created
Connected to Personal Oracle9i Release 9.2.0.1.0 
Connected as test

SQL> create type item_rec is object(item varchar2(20),desc_ varchar2(30));
  2  /

Type created

SQL> create type item_list is type of item_rec;
  2  /

Warning: Type created with compilation errors

SQL> create type item_list is table of item_rec;
  2  /

Type created

SQL> 

second In a procedure
CREATE OR REPLACE PROCEDURE tst_bom
     (t_Result  OUT ITEM_LIST)
IS
  i         NUMBER;
  TYPE rec_Item IS RECORD(Item VARCHAR2(20),
                           desc_ VARCHAR2(30));
  TYPE rec_Tab IS TABLE OF REC_ITEM;
  Item_Tab  REC_TAB;
BEGIN
  FOR i IN 1.. 10 LOOP
    Item_Tab(i).Item := 'item'
                        ||To_Char(i);
    
    Item_Tab(i).desc_ := 'description'
                         ||To_Char(i);
  END LOOP;
END tst_bom;
 

can i put the Item_Tab's data into t_Result(out parm)?
Re: how to copy a record type's values to a object type [message #356637 is a reply to message #356631] Fri, 31 October 2008 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64121
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not possible with PL/SQL type but you can use your types:
SQL> DECLARE
  2    i         NUMBER;
  3    Item_Tab  ITEM_LIST := item_list();
  4    t_Result  ITEM_LIST;
  5  BEGIN
  6    FOR i IN 1.. 10 LOOP
  7      item_tab.extend;
  8      item_tab(i) := item_rec('item'||To_Char(i),'description'||To_Char(i));
  9    END LOOP;
 10  
 11    select item_rec(item,desc_) bulk collect into t_result from table(item_tab);
 12    for i in 1..item_tab.count loop
 13      dbms_output.put_line(t_result(i).item||' '||t_result(i).desc_);
 14    end loop;
 15  END;
 16  /
item1 description1
item2 description2
item3 description3
item4 description4
item5 description5
item6 description6
item7 description7
item8 description8
item9 description9
item10 description10

PL/SQL procedure successfully completed.

Regards
Michel
Re: how to copy a record type's values to a object type [message #356676 is a reply to message #356637] Fri, 31 October 2008 20:01 Go to previous message
wu_horizon
Messages: 4
Registered: March 2007
Junior Member
thanks a lot.
but I want put record table Item_Tab to t_Result,how to do that.
CREATE OR REPLACE PROCEDURE tst_bom
     (t_Result  OUT ITEM_LIST)
IS
  i         NUMBER;
  TYPE rec_Item IS RECORD(Item VARCHAR2(20),
                           desc_ VARCHAR2(30));
  TYPE rec_Tab IS TABLE OF REC_ITEM;
  Item_Tab  REC_TAB
BEGIN
  FOR i IN 1.. 10 LOOP
    Item_Tab(i).Item := 'item'
                        ||To_Char(i);
    
    Item_Tab(i).desc_ := 'description'
                         ||To_Char(i);
  END LOOP;
END tst_bom;
 
Previous Topic: Date Range Data Extraction using SQL
Next Topic: Help!!!!...Error with a Simple Cursor
Goto Forum:
  


Current Time: Wed Dec 07 03:13:48 CST 2016

Total time taken to generate the page: 0.12619 seconds