Home » SQL & PL/SQL » SQL & PL/SQL » How to insert records in Mutilevel nested table (Oracle 10g,10.1.0.2,Windows)
How to insert records in Mutilevel nested table [message #420328] Fri, 28 August 2009 05:54 Go to next message
amit210001
Messages: 14
Registered: August 2009
Location: India
Junior Member
Hi,

Can anyone will provide the insert and update query for the following Mutilevel nested table


CREATE OR REPLACE TYPE test1_type AS OBJECT
(
  SOURCE_ID NUMBER(10),
  ENTITY_ID_CODE   VARCHAR2(3 Byte)
)

CREATE OR REPLACE TYPE test1_type _tbl
AS TABLE OF test1_type;

CREATE OR REPLACE TYPE test2_type AS OBJECT
( 
  RECEIVER_ID NUMBER(10),
  SOURCE_ID NUMBER(10)
)

CREATE OR REPLACE TYPE test2_type _tbl
AS TABLE OF test2_type;

CREATE OR REPLACE TYPE wrapper_type AS OBJECT
(
  test_nested_input1   test1_type_tbl,
  test_nested_input2   test2_type_tbl
)

CREATE OR REPLACE TYPE wrapper_type _tbl
AS TABLE OF wrapper_type;

create table REALMED_CORP_NESTED_TABLE
 (
  id INTEGER Primary Key,
  Company_Name VarChar2(20),
  wrapper_rec wrapper_type_tbl
 )
 NESTED TABLE wrapper_rec STORE AS nested_wrapper_rec
(NESTED TABLE test_nested_input1 STORE AS nested_Input1_tab
 NESTED TABLE test_nested_input2 STORE AS nested_Input2_tab);



I am using the below sql to insert the record in the above table but i am failed to insert the records in the table , may be i m not able to write the correct insert statement

insert into REALMED_CORP_NESTED_TABLE 
values
(001,
'c12',
 wrapper_type_tbl(wrapper_type(test1_type_tbl(test1_type(12,'t'))),test2_type_tbl(test2_type(12,12)))));


Please provide the insert and update statement for this table.
Re: How to insert records in Mutilevel nested table [message #420333 is a reply to message #420328] Fri, 28 August 2009 06:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
The parenthesis mismatch.
SQL> insert into REALMED_CORP_NESTED_TABLE 
  2  values
  3  (001,
  4  'c12',
  5   wrapper_type_tbl(
  6     wrapper_type(
  7       test1_type_tbl(test1_type(12,'t')),
  8       test2_type_tbl(test2_type(12,12))
  9     ))
 10  );

1 row created.

Regards
Michel

[Updated on: Fri, 28 August 2009 06:59]

Report message to a moderator

Re: How to insert records in Mutilevel nested table [message #420335 is a reply to message #420333] Fri, 28 August 2009 07:17 Go to previous messageGo to next message
amit210001
Messages: 14
Registered: August 2009
Location: India
Junior Member
Thanks Michel
Smile
Regards
-Amit
Re: How to insert records in Mutilevel nested table [message #420416 is a reply to message #420335] Sat, 29 August 2009 06:40 Go to previous messageGo to next message
amit210001
Messages: 14
Registered: August 2009
Location: India
Junior Member
Hi Michel,

I m using the below SP with OUT Paramter of Nested type to select the records from the same table that was already mention in my previous post but i m getting the following error

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at "JDBC_TEST1.SP_REGION_TAB_SELECT", line 9
ORA-06512: at line 11


CREATE OR REPLACE PROCEDURE SP_REGION_TAB_SELECT
(
 OUT_P1 OUT NUMBER,
 OUT_P2 OUT VARCHAR2,
 OUT_P3 OUT nt_country_typ
 )
AS
BEGIN 
Select region_id ,region_name,countries into OUT_P1,OUT_P2,OUT_P3 from region_tab;
End;


Can you please provide some input like is it possible to select the records by using the above SP ?

Thanks
-Amit
Re: How to insert records in Mutilevel nested table [message #420429 is a reply to message #420416] Sat, 29 August 2009 09:26 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the same table, this is not the same types, I can't know why there is an error.
Be sure there is only one row in your table.

Use SQL*Plus and copy and paste your session.

Regards
Michel
Re: How to insert records in Mutilevel nested table [message #420462 is a reply to message #420429] Sun, 30 August 2009 01:02 Go to previous messageGo to next message
amit210001
Messages: 14
Registered: August 2009
Location: India
Junior Member
Hi Michel,

My apologies.

Here are the correct SP related to the table that was already in post-


CREATE OR REPLACE PROCEDURE SP_REALMED_CORP_SELECT
(
 OUT_P1 OUT INTEGER,
 OUT_P2 OUT VARCHAR2,
 OUT_P3 OUT wrapper_type_tbl
 )
AS
BEGIN 
Select id,Company_Name,wrapper_rec into OUT_P1,OUT_P2,OUT_P3 from REALMED_CORP_NESTED_TABLE;
End;

SQL> Execute SP_REALMED_CORP_SELECT;
BEGIN SP_REALMED_CORP_SELECT; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'SP_REALMED_CORP_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

SQL> select * from REALMED_CORP_NESTED_TABLE;

        ID COMPANY_NAME
---------- --------------------
WRAPPER_REC(TEST_NESTED_INPUT1(SOURCE_ID, ENTITY_ID_CODE), TEST_NESTED_INPUT2(RE
--------------------------------------------------------------------------------
       123 t
WRAPPER_TYPE_TBL(WRAPPER_TYPE(TEST1_TYPE_TBL(TEST1_TYPE(12, 't')), TEST2_TYPE_TB
L(TEST2_TYPE(123, 124))))

         1 c12
WRAPPER_TYPE_TBL(WRAPPER_TYPE(TEST1_TYPE_TBL(TEST1_TYPE(1, 'a')), TEST2_TYPE_TBL
(TEST2_TYPE(1, 1))))



This table contains two records .

Can you please point out the error ?

Thanks
-Amit
Re: How to insert records in Mutilevel nested table [message #420464 is a reply to message #420462] Sun, 30 August 2009 01:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Can you please point out the error ?

2 rows cannot be placed into scalar variables.
You have to either restrict to one row either use array variables or either loop among the rows.
This is baseic PL/SQL, please read:
PL/SQL User's Guide and Reference
Application Developer's Guide - Fundamentals

Regards
Michel

[Updated on: Sun, 30 August 2009 01:45]

Report message to a moderator

Re: How to insert records in Mutilevel nested table [message #420465 is a reply to message #420462] Sun, 30 August 2009 02:05 Go to previous messageGo to next message
flyboy
Messages: 1831
Registered: November 2006
Senior Member
And in addition to the actually posted error (which again differs from previous posts)
amit210001 wrote on Sun, 30 August 2009 08:02
SQL> Execute SP_REALMED_CORP_SELECT;
BEGIN SP_REALMED_CORP_SELECT; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to
'SP_REALMED_CORP_SELECT'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored


Seems quite self-explanative. Procedure SP_REALMED_CORP_SELECT is created with 3 OUT parameters. However it is called without any parameter.
Do you not see the difference?

Of course, this will not take away the problem with passing multiple values into one variable. For its elimination, follow Michel's advice.
Re: How to insert records in Mutilevel nested table [message #420466 is a reply to message #420465] Sun, 30 August 2009 02:27 Go to previous message
Michel Cadot
Messages: 63812
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
And in addition to the actually posted error (which again differs from previous posts)

Oops! stayed in the previous error and didn't see the new point.
Thanks for having fixed the point.

Regards
Michel

[Updated on: Sun, 30 August 2009 02:28]

Report message to a moderator

Previous Topic: Complex Timeline / Calendar Question
Next Topic: group data by 6 months
Goto Forum:
  


Current Time: Fri Sep 30 07:21:17 CDT 2016

Total time taken to generate the page: 0.05606 seconds