having a bit of an issue with creating a table function (my first time)
declare
type r_test is record (p_number number,
p_string varchar2(50),
p_date date);
type t_test is table of r_test;
function test_table_function
return t_test
is
v_test t_test := t_test();
begin
v_test.extend(3);
v_test(1).p_number := 5;
v_test(1).p_string := 'boo';
v_test(1).p_date := to_date('01-01-2007','dd-mm-yyyy');
v_test(2).p_number := 42;
v_test(2).p_string := 'thingo';
v_test(2).p_date := to_date('01-02-2007','dd-mm-yyyy');
v_test(3).p_number := 1554;
v_test(3).p_string := 'yay';
v_test(3).p_date := to_date('01-03-2007','dd-mm-yyyy');
return(v_test);
end;
-------------------------------------------------------------------
begin
for v_cur in (select * from table(test_table_function)) loop
dbms_output.put_line(v_cur.p_number||' '||v_cur.p_string||' '||v_cur.p_date);
end loop;
end;
the error I'm getting is:
ORA-06550: line 34, column 34:
PLS-00231: function 'TEST_TABLE_FUNCTION' may not be used in SQL
ORA-06550: line 34, column 34:
PL/SQL: ORA-00904: "TEST_TABLE_FUNCTION": invalid identifier
ORA-06550: line 34, column 14:
PL/SQL: SQL Statement ignored
ORA-06550: line 36, column 24:
PLS-00364: loop index variable 'V_CUR' use is invalid
ORA-06550: line 36, column 3:
PL/SQL: Statement ignored
anyone have any Idea what's going wrong?