CODEWITHSUNDEEP
oracle-databasestored-proceduresuser-defined-types
gogic1
gogic1

Reputation: 111

Oracle varray , invalid data type

I have this functon 

TYPE TEST_DATA IS VARRAY(100) OF NUMBER;
function TEST(p IN NUMBER)
return TEST_DATA ;

and in a body of a function

function TEST(p IN NUMBER)
return TEST_DATA is
test_exit TEST_DATA;

begin
   test_izlaz := TEST_DATA();
    test_izlaz.extend;
    test_izlaz(1) := 5;    
return test_izlaz;
end TEST;

when i try to call function from command line i get "ORA-00902: invalid datatype" ? What am i doing wrong ? I need varray cos this will be getting complex data out.

Upvotes: 0

Views: 873

Answers (1)

DazzaL
DazzaL

Reputation: 21973

Please clarify how you are calling this. "command line" is SQLPLUS? as if so, then it works for me:

SQL> create or replace package foo
  2  as
  3    TYPE TEST_DATA IS VARRAY(100) OF NUMBER;
  4    function TEST(p IN NUMBER)
  5      return TEST_DATA ;
  6  end;
  7  /

Package created.

SQL> create or replace package body foo
  2  as
  3    function TEST(p IN NUMBER)
  4    return TEST_DATA
  5    is
  6      test_izlaz TEST_DATA;
  7    begin
  8      test_izlaz := TEST_DATA();
  9      test_izlaz.extend;
 10      test_izlaz(1) := 5;
 11      return test_izlaz;
 12    end TEST;
 13  end;
 14  /

Package body created.

SQL> set serverout on
SQL> declare
  2    t foo.test_data;
  3  begin
  4    t := foo.test(1);
  5    for idx in 1..t.count
  6    loop
  7      dbms_output.put_line(t(idx));
  8    end loop;
  9  end;
 10  /
5

PL/SQL procedure successfully completed.

SQL>

Upvotes: 1

Related Questions