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  |
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 #420416 is a reply to message #420335] |
Sat, 29 August 2009 06:40   |
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 #420462 is a reply to message #420429] |
Sun, 30 August 2009 01:02   |
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 #420465 is a reply to message #420462] |
Sun, 30 August 2009 02:05   |
flyboy
Messages: 1903 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.
|
|
|
|
Goto Forum:
Current Time: Fri Jul 11 03:42:56 CDT 2025
|