Oracle reference the type of a column equal to the type of another column

Question

I’m trying to create a table to return in a function. For this, I am trying to create a object type thus:

create or replace type relatorio as object (
  planta TABLE_NAME.COLUMN%TYPE,
  resp_0 TABLE_NAME.COLUMN%TYPE
);

But it is returning the error:

PSL-00201: TABLE_NAME.COLUMN identifier must be declared

My question is, am I using the %TYPE appropriately?

Answer 1

A solution follows below. To simplify I considered that its report function would return a table with the fields name and amount.

Solution link running on SQL Feedle: http://sqlfiddle.com/#! 4/4c06c/1/0

1) Create a type Object which 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 type table. Note below that the table is formed by type line which has just been created.

    create or replace type t_tabela_relatorio as table of t_linhas;
    /        

3) Create function by returning type t_tabulat_report that was created in the beginning

    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: http://www.adp-gmbh.ch/ora/plsql/coll/return_table.html

Answer 2

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 trial is still valid.

Could be used to create a variable based on another already stated.

 DECLARE
 v1 VARCHAR2(13) := 'bla bla bla';
 v2 v1%TYPE := UPPER(v1);
 v3 v1%TYPE := LOWER(v2);