Oracle reference the type of one column the same as the type of another column

Question:

I'm trying to create a table to return in a function. For that, I'm trying to create an object type this:

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

But the error is returning:

PSL-00201: identifier 'TABLE_NAME.COLUMN' must be declared

My question is, am I using %TYPE properly?

Answer:

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 variable based on another one already declared.

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