I'm trying to create a table to return in a function. For this, I'm trying to create a object type like this:
create or replace type relatorio as object (
planta TABLE_NAME.COLUMN%TYPE,
resp_0 TABLE_NAME.COLUMN%TYPE
);
But you are returning the error:
PSL-00201: identifier 'TABLE_NAME.COLUMN' should be declared
My question is: am I using %TYPE properly?
CREATE TABLE TESTE (
ID INTEGER,
CAMPO_T VACHAR(20))
In PROCEDURE or TRIGGER you would do this:
DECLARE
v_texto TESTE.CAMPO_T%TYPE;
Even if you change the size of the field in the table your procedure is still valid.
It could be used to create a variant based on another one already declared.
DECLARE
v1 VARCHAR2(13) := 'bla bla bla';
v2 v1%TYPE := UPPER(v1);
v3 v1%TYPE := LOWER(v2);
Here's a solution. For simplicity I considered that your report function would return a table with the name and quantity fields.
Solution link working in SQL Feedle: link
1) Create an object type that contains a representation of the columns of the table to be returned by its function.
create or replace type t_linhas as object (
nome varchar2(30),
quantidade number
);
/
2) Create a table type. Note below that the table is made up of the t_lines type that has just been created.
create or replace type t_tabela_relatorio as table of t_linhas;
/
3) Create the function returning the t_table_type that was created at startup
create or replace function fc_relatorio return t_tabela_relatorio as
v_ret t_tabela_relatorio;
cursor c_consulta_base_relatorio is
select 'Joao' as nome_cliente, 10 as qtd from dual union
select 'Pedro' as nome_cilente, 20 as qtd from dual union
select 'Isabel' as nome_ciente, 15 as qtd from dual;
begin
v_ret := t_tabela_relatorio();
for x in c_consulta_base_relatorio loop
v_ret.extend;
v_ret(v_ret.count) := t_linhas(x.nome_cliente,x.qtd);
end loop;
return v_ret;
end fc_relatorio;
/
3) Example of how to call the function:
SELECT * FROM TABLE(FC_RELATORIO())
Source: link