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

3

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?

    
asked by anonymous 17.03.2017 / 19:51

2 answers

0
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);     
    
17.03.2017 / 20:05
0

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

    
27.07.2017 / 00:09