I modified an example copied from this community forum while searching for pipelined function, in package spec there is a declaration of cursor I used some columns are not exist in any table,
Is there any way I can define column length while declaring this cursor or I have to use like: 'AAAAAAAAAA' dnam ( 10 times A ) to have length of 10 characters ? can I use LPAD / RPAD or any other ?
SQL> create or replace package pkg2 as
2 cursor dep_cur is (Select 0 dep, 'A' dnam, 0 sal from dual);
3 --
4 type pkg_dep_table_type is table of dep_cur%rowtype;
5 --
6 function get_dep( p_deptno number )
7 return pkg_dep_table_type
8 pipelined;
9 end;
10 /
Package created.
SQL> create or replace package body pkg2 as
2 function get_dep(p_deptno number)
3 return pkg_dep_table_type
4 pipelined
5 is
6 begin
7 for v_dep_rec in (select e.deptno, d.dname, sum(e.sal) sal
8 from emp e, dept d where d.deptno = p_deptno
9 and e.deptno=d.deptno
10 group by e.deptno, d.dname)
11 loop
12 pipe row(v_dep_rec);
13 end loop;
14 end;
15 end;
16 /
Package body created.
SQL> select * from table(pkg2.get_dep(30));
select * from table(pkg2.get_dep(30))
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SCOTT.PKG2", line 12
When modifying in package spec as below, it is working fine.
cursor dep_cur is (Select 0 dep, 'AAAAAAAAAAAAAAAAAAAA' dnam, 0 sal from dual);
regards