Home » SQL & PL/SQL » SQL & PL/SQL » pl/sql object and type collection (10g windows)
pl/sql object and type collection [message #433158] Mon, 30 November 2009 06:43 Go to next message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
CREATE OR REPLACE
TYPE test_obj3 AS object
            (t1 NUMBER(5), t2 VARCHAR2(30),t3 VARCHAR2(3000));
/


CREATE OR REPLACE
TYPE test_type_3 AS varray(10)
            OF test_obj3;
/


CREATE OR REPLACE PROCEDURE obj_typ (p_obj_array  IN   test_type_3,
												result_cursor     OUT types.ref_cursor
												)
	AS
TYPE table_type2 IS VARRAY (3) OF test_type_3;
   r_tab           table_type2      := table_type2 ();
  BEGIN
 FOR i IN 1 .. p_obj_array.COUNT
   LOOP
     DBMS_OUTPUT.put_line (p_obj_array (i).t1 || p_obj_array (i).t2 || p_obj_array (i).t3);
	  r_tab.EXTEND ();
      r_tab (i):= p_obj_array(i).c1;
   END LOOP;
   OPEN result_cursor FOR
     SELECT r_tab (1), r_tab (2)
        FROM DUAL;
END;
/


i am getting error

12/7 PL/SQL: Statement ignored
12/34 PLS-00382: expression is of wrong type


can any one please let us know where we are going wrong and suggest
Re: pl/sql object and type collection [message #433160 is a reply to message #433158] Mon, 30 November 2009 06:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's because your locally defined Type table_type2 is not the same as the externally defined type test_type3.

They're both VARRAYs of the same object, but that doesn't make them the same type.
Re: pl/sql object and type collection [message #433237 is a reply to message #433160] Mon, 30 November 2009 22:22 Go to previous message
oracle_coorgi
Messages: 185
Registered: September 2006
Location: INDIA-karnataka
Senior Member
ok

You meant
test_type_3 AS varray(10)
and 
TYPE table_type2 IS VARRAY (3)


ok got it fixed thnxs
Previous Topic: Oracle a multi dimentional database?
Next Topic: Count of tables
Goto Forum:
  


Current Time: Tue Sep 27 02:30:46 CDT 2016

Total time taken to generate the page: 0.34351 seconds