Re: Selecting nvarchar2 from a table function results in ORA-12714

From: <Bobsparks_at_excite.com>
Date: 22 Feb 2006 11:59:39 -0800
Message-ID: <1140638379.010787.11490@g14g2000cwa.googlegroups.com>

Here is concise test case it shows that while the function works from the sql prompt it cannot be compiled into a procedure once the type has a nvarchar2

drop procedure dummytest
/

drop function fn_dummy
/

drop type dummy_type_table
/

CREATE OR REPLACE TYPE dummy_type AS OBJECT (

    sid NUMBER (9),
   object_nm VARCHAR2(1024)
   )
/

CREATE OR REPLACE TYPE dummy_type_table AS TABLE OF dummy_type
/

CREATE OR REPLACE FUNCTION fn_dummy

   RETURN dummy_type_table PIPELINED AS

      out_rec dummy_type := dummy_type(null,null) ;    BEGIN

        out_rec.sid := 1;
        out_rec.object_nm := 'Tommy';
        PIPE ROW ( out_rec);
        out_rec.sid := 2;
        out_rec.object_nm := 'Lee';
        PIPE ROW ( out_rec);
        return;

   end;
/

create or replace procedure dummytest(o_cur OUT TYPES.cursortype)

    as
  begin

  	OPEN o_cur
      FOR
   	select * from table(fn_dummy);

  end;

/

show errors
/

drop procedure dummytest
/

drop function fn_dummy
/

drop type dummy_type_table
/

CREATE OR REPLACE TYPE dummy_type AS OBJECT (

    sid NUMBER (9),
   object_nm NVARCHAR2(1024)
   )
/

CREATE OR REPLACE TYPE dummy_type_table AS TABLE OF dummy_type
/

CREATE OR REPLACE FUNCTION fn_dummy

   RETURN dummy_type_table PIPELINED AS

      out_rec dummy_type := dummy_type(null,null) ;    BEGIN

        out_rec.sid := 1;
        out_rec.object_nm := N'Tommy';
        PIPE ROW ( out_rec);
        out_rec.sid := 2;
        out_rec.object_nm := N'Lee';
        PIPE ROW ( out_rec);
        return;

   end;
/

select * from table(fn_dummy)
/

create or replace procedure dummytest(o_cur OUT TYPES.cursortype)

    as
  begin

  	OPEN o_cur
      FOR
   	select * from table(fn_dummy);

  end;

/

show errors
/
Received on Wed Feb 22 2006 - 13:59:39 CST