OraFAQ Forum: SQL & PL/SQL » NESTED COLLECTIONS

Home » SQL & PL/SQL » SQL & PL/SQL » NESTED COLLECTIONS (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

NESTED COLLECTIONS [message #627194]

Sat, 08 November 2014 09:45

vicktorz
Messages: 26
Registered: March 2013

Junior Member

Hi Everyone,

I'm beginner for pl/sql, i'm trying to insert and print some values inside nested values.

but i couldn't achive it..seeking your valuable help.. kindly help me to solve my issues. Thanks in advance..

MY COLLECTION DETAILS:
--------------------------------				
create or replace TYPE                "TYPE_PARAM_DETAILS" AS OBJECT
                                      (
                         [code][/code]             PARAM_NAME VARCHAR2(100), 
                                        PARAM_DESC VARCHAR2(1000),         
                                        IN_OUT VARCHAR2(50),               
                                        PARAM_TYPE VARCHAR2(500),          
                                        PARAM_LENGTH NUMBER,
                                        LOV_DETAILS  CDR_VALS_COLL
                                          );  
                                          
CREATE OR REPLACE TYPE                "COLL_PARAMETER_DETAIL" AS TABLE OF TYPE_PARAM_DETAILS; 
                                          

										  
										  
CREATE OR REPLACE TYPE        "CDR_VAL_OBJ_TYPE" as object 
		(                                        
			POSITION	NUMBER(22),                      
			VALUE       VARCHAR2(4000)               
		);  

	CREATE OR REPLACE TYPE        "CDR_VALS_COLL" AS TABLE OF CDR_VAL_OBJ_TYPE ;	
										  
MY PROCEDURE:
-------------										  
										  
CREATE OR REPLACE PROCEDURE PR_TEST_COLL ( PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL)
IS 
--PI_VAL COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,LOV_DETAILS(1,'VK'));
L_CDRVALSCOLL             CDR_VALS_COLL  := CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL));
L_CDRVALOBJ                CDR_VAL_OBJ_TYPE := CDR_VAL_OBJ_TYPE(NULL, NULL);
BEGIN
FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST 
LOOP

  FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST
	LOOP
--L_COUNT:=L_COUNT+1;
        L_CDRVALSCOLL.EXTEND;
        L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
        L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;
	END LOOP;	
        
     
for i in L_CDRVALSCOLL.first..L_CDRVALSCOLL.last
loop
dbms_output.put_line('POSITION:='||L_CDRVALSCOLL(i).position);
dbms_output.put_line('VALUE:='||L_CDRVALSCOLL(i).value);
end loop;
        END LOOP;

END;
/
SHOW ERRORS


i'm getting below error:
PROCEDURE PR_TEST_COLL compiled
Errors: check compiler log
10/44          PLS-00302: component 'FIRST' must be declared
10/3           PL/SQL: Statement ignored;

Thanks,
Vijay

Report message to a moderator

Re: NESTED COLLECTIONS [message #627195 is a reply to message #627194]

Sat, 08 November 2014 10:20

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

vicktorz wrote on Sat, 08 November 2014 10:45

i'm getting below error:

I doubted:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> CREATE OR REPLACE TYPE        "CDR_VAL_OBJ_TYPE" as object
  2             (
  3                     POSITION        NUMBER(22),
  4                     VALUE       VARCHAR2(4000)
  5             );
  6  /

Type created.

SQL> CREATE OR REPLACE TYPE        "CDR_VALS_COLL" AS TABLE OF CDR_VAL_OBJ_TYPE ;
  2  /

Type created.

SQL> create or replace TYPE                "TYPE_PARAM_DETAILS" AS OBJECT
  2                                        (
  3  PARAM_NAME VARCHAR2(100),
  4                                          PARAM_DESC VARCHAR2(1000),
  5                                          IN_OUT VARCHAR2(50),
  6                                          PARAM_TYPE VARCHAR2(500),
  7                                          PARAM_LENGTH NUMBER,
  8                                          LOV_DETAILS  CDR_VALS_COLL
  9                                            );
 10  /

Type created.

SQL> CREATE OR REPLACE TYPE                "COLL_PARAMETER_DETAIL" AS TABLE OF TYPE_PARAM_DETAILS;
  2  /

Type created.

SQL> CREATE OR REPLACE PROCEDURE PR_TEST_COLL ( PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL)
  2  IS
  3  --PI_VAL COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,LOV_DETAILS(1,'VK'));
  4  L_CDRVALSCOLL             CDR_VALS_COLL  := CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL));
  5  L_CDRVALOBJ                CDR_VAL_OBJ_TYPE := CDR_VAL_OBJ_TYPE(NULL, NULL);
  6  BEGIN
  7  FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST
  8  LOOP
  9
 10    FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST
 11     LOOP
 12  --L_COUNT:=L_COUNT+1;
 13          L_CDRVALSCOLL.EXTEND;
 14          L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
 15          L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;
 16     END LOOP;
 17
 18
 19  for i in L_CDRVALSCOLL.first..L_CDRVALSCOLL.last
 20  loop
 21  dbms_output.put_line('POSITION:='||L_CDRVALSCOLL(i).position);
 22  dbms_output.put_line('VALUE:='||L_CDRVALSCOLL(i).value);
 23  end loop;
 24          END LOOP;
 25
 26  END;
 27  /

Procedure created.

SQL> SHOW ERRORS
No errors.
SQL>

SY.
P.S. Procedure logic though, doesn't make much sense. Please explain in words what are you trying to achieve.

[Updated on: Sat, 08 November 2014 10:22]

Report message to a moderator

Re: NESTED COLLECTIONS [message #627196 is a reply to message #627195]

Sat, 08 November 2014 10:28

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

It looks like you meant:

CREATE OR REPLACE
  PROCEDURE PR_TEST_COLL(
                         PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL
                        )
    IS 
    BEGIN
        FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST LOOP
          FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST LOOP
            dbms_output.put_line('POSITION:=' || PI_PARAM_DETAILS(I).LOV_DETAILS(j).position);
            dbms_output.put_line('VALUE:=' || PI_PARAM_DETAILS(I).LOV_DETAILS(j).value);
          END LOOP;
        END LOOP;
END;
/

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC PR_TEST_COLL(COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(1,'VK')))));
POSITION:=1
VALUE:=VK

PL/SQL procedure successfully completed.

SQL>

SY.

Report message to a moderator

Re: NESTED COLLECTIONS [message #627211 is a reply to message #627196]

Sun, 09 November 2014 05:12

vicktorz
Messages: 26
Registered: March 2013

Junior Member

Thank you Solomon for your help...

I could print the values which i'm passing.. But i want to insert some values into the collection and trying to print the inserted values like below..

In this i'm getting " ORA-06530: Reference to uninitialized composite" error.. What is this means and how to resolve it in the below code. Pls advice me..

CREATE OR REPLACE PROCEDURE PR_TEST_COLL ( PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL)
    IS
    --PI_VAL COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,LOV_DETAILS(1,'VK'));
    L_CDRVALSCOLL             CDR_VALS_COLL  := CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL));
    L_CDRVALOBJ                CDR_VAL_OBJ_TYPE := CDR_VAL_OBJ_TYPE(NULL, NULL);
 BEGIN
    FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST
    LOOP

     FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST
      LOOP
   --L_COUNT:=L_COUNT+1;
           L_CDRVALSCOLL.EXTEND;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;
      END LOOP;
 

   for i in L_CDRVALSCOLL.first..L_CDRVALSCOLL.last
   loop
   dbms_output.put_line('POSITION:='||L_CDRVALSCOLL(i).position);
   dbms_output.put_line('VALUE:='||L_CDRVALSCOLL(i).value);
   end loop;
           END LOOP;
 
   END;
   /
   show errors

Thanks,
Vijay

Report message to a moderator

Re: NESTED COLLECTIONS [message #627214 is a reply to message #627211]

Sun, 09 November 2014 06:18

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST

Is PI_PARAM_DETAILS allocated, initialized and set?

[Updated on: Sun, 09 November 2014 06:19]

Report message to a moderator

Re: NESTED COLLECTIONS [message #627216 is a reply to message #627196]

Sun, 09 November 2014 06:59

vicktorz
Messages: 26
Registered: March 2013

Junior Member

Hi Michel..

Thanks for reply..

You mean need to declare PI_PARAM_DETAILS again in the declaration part like below.

 PI_PARAM_DETAILS          COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS(null,null,null,null,null,CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL))));

If means it will be duplicate declaration,, right??

Kindly refet collection type declaration in the above thread...

Thanks,
Vijay

Report message to a moderator

Re: NESTED COLLECTIONS [message #627217 is a reply to message #627216]

Sun, 09 November 2014 08:05

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

No I mean initialized and set BEFORE calling the procedure, it is an IN parameter.

Report message to a moderator

Re: NESTED COLLECTIONS [message #627219 is a reply to message #627211]

Sun, 09 November 2014 09:07

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

vicktorz wrote on Sun, 09 November 2014 06:12

But i want to insert some values into the collection and trying to print the inserted values like below..

Why do you need to copy existing collection into another one just to print it??? I already showed you how to do it without auxiliary (L_CDRVALSCOLL in your case) collection. So I'll assume this is for learning purposes. Anyway:

L_CDRVALSCOLL.EXTEND;

adds a NULL element L_CDRVALSCOLL(L_CDRVALSCOLL.LAST) to collection. While

           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;

are trying to assign attribute values to an object of CDR_VAL_OBJ_TYPE. That's why you are gettingt error. You should either use initialize it using:

           L_CDRVALSCOLL.EXTEND;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST) := CDR_VAL_OBJ_TYPE(NULL,NULL);
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;

or better construct and assign whole object:

           L_CDRVALSCOLL.EXTEND;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST) := CDR_VAL_OBJ_TYPE(PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION,PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE);

But in your case you don't even need to construct object since you already have it. It is PI_PARAM_DETAILS(I).LOV_DETAILS(J):

           L_CDRVALSCOLL.EXTEND;
           L_CDRVALSCOLL(L_CDRVALSCOLL.LAST) := PI_PARAM_DETAILS(I).LOV_DETAILS(J);

SQL> CREATE OR REPLACE PROCEDURE PR_TEST_COLL ( PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL)
  2      IS
  3      --PI_VAL COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,LOV_DETAILS(1,'VK'));
  4      L_CDRVALSCOLL             CDR_VALS_COLL  := CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL));
  5      L_CDRVALOBJ                CDR_VAL_OBJ_TYPE := CDR_VAL_OBJ_TYPE(NULL, NULL);
  6   BEGIN
  7      FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST
  8      LOOP
  9
 10       FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST
 11        LOOP
 12     --L_COUNT:=L_COUNT+1;
 13             L_CDRVALSCOLL.EXTEND;
 14             L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).POSITION:= PI_PARAM_DETAILS(I).LOV_DETAILS(J).POSITION;
 15             L_CDRVALSCOLL(L_CDRVALSCOLL.LAST).VALUE    := PI_PARAM_DETAILS(I).LOV_DETAILS(J).VALUE;
 16        END LOOP;
 17
 18
 19     for i in L_CDRVALSCOLL.first..L_CDRVALSCOLL.last
 20     loop
 21     dbms_output.put_line('POSITION:='||L_CDRVALSCOLL(i).position);
 22     dbms_output.put_line('VALUE:='||L_CDRVALSCOLL(i).value);
 23     end loop;
 24             END LOOP;
 25
 26     END;
 27     /

Procedure created.

SQL> SET SERVEROUTPUT ON
SQL> EXEC PR_TEST_COLL(COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(1,'VK')))));
BEGIN PR_TEST_COLL(COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(1,'VK'))))); END;

*
ERROR at line 1:
ORA-06530: Reference to uninitialized composite
ORA-06512: at "SCOTT.PR_TEST_COLL", line 14
ORA-06512: at line 1


SQL> CREATE OR REPLACE PROCEDURE PR_TEST_COLL ( PI_PARAM_DETAILS IN COLL_PARAMETER_DETAIL)
  2      IS
  3      --PI_VAL COLL_PARAMETER_DETAIL:=COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,LOV_DETAILS(1,'VK'));
  4      L_CDRVALSCOLL             CDR_VALS_COLL  := CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(NULL,NULL));
  5      L_CDRVALOBJ                CDR_VAL_OBJ_TYPE := CDR_VAL_OBJ_TYPE(NULL, NULL);
  6   BEGIN
  7      FOR I IN PI_PARAM_DETAILS.FIRST..PI_PARAM_DETAILS.LAST
  8      LOOP
  9
 10       FOR J IN PI_PARAM_DETAILS(I).LOV_DETAILS.FIRST..PI_PARAM_DETAILS(I).LOV_DETAILS.LAST
 11        LOOP
 12     --L_COUNT:=L_COUNT+1;
 13             L_CDRVALSCOLL.EXTEND;
 14             L_CDRVALSCOLL(L_CDRVALSCOLL.LAST) := PI_PARAM_DETAILS(I).LOV_DETAILS(J);
 15     END LOOP;
 16
 17
 18  for i in L_CDRVALSCOLL.first..L_CDRVALSCOLL.last
 19  loop
 20  dbms_output.put_line('POSITION:='||L_CDRVALSCOLL(i).position);
 21  dbms_output.put_line('VALUE:='||L_CDRVALSCOLL(i).value);
 22  end loop;
 23          END LOOP;
 24
 25  END;
 26  /

Procedure created.

SQL> EXEC PR_TEST_COLL(COLL_PARAMETER_DETAIL(TYPE_PARAM_DETAILS('ABC','BCD','EFG','HIK',20,CDR_VALS_COLL(CDR_VAL_OBJ_TYPE(1,'VK')))));
POSITION:=
VALUE:=
POSITION:=1
VALUE:=VK

PL/SQL procedure successfully completed.

SQL>

But again, you don't need that auxiliary collection L_CDRVALSCOLL.

SY.

Report message to a moderator

Re: NESTED COLLECTIONS [message #627224 is a reply to message #627219]

Sun, 09 November 2014 10:01

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

NEVER do in PL/SQL that which can be done in plain SQL

Report message to a moderator

Re: NESTED COLLECTIONS [message #627225 is a reply to message #627224]

Sun, 09 November 2014 10:42

vicktorz
Messages: 26
Registered: March 2013

Junior Member

Thanks Solomon,Michel and blackswan for your guidance and explanation..

i got it now...

@solomon: Why initial values are null.. i need to use EXTEND method before the loop start..

is any other way to avoid null values at beginning..

Thanks,
Vijay

Report message to a moderator

Re: NESTED COLLECTIONS [message #627226 is a reply to message #627225]

Sun, 09 November 2014 10:56

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

vicktorz wrote on Sun, 09 November 2014 11:42

Why initial values are null.. is any other way to avoid null values at beginning

EXTEND

This procedure has three forms. EXTEND appends one null element to a collection. EXTEND(n) appends n null elements to a collection. EXTEND(n,i) appends n copies of the ith element to a collection.

SY.

Report message to a moderator

Previous Topic:	SQL Case statement with Regular Expression
Next Topic:	Conver SYBASE to Oracle

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 23:15:22 CDT 2024