CODEWITHSUNDEEP
oracle-databaseplsql
Blood-HaZaRd
Blood-HaZaRd

Reputation: 2138

Create Type based on an exiting Table

As the title said : I want to create a type in oracle based on an existing Table.

I did as follow :

create or replace type  MY_NEW_TYPE as object( one_row EXISTING_TABLE%rowtype);

The Aim is to be able to use this into a function which will return a table containing sample row of the table EXISTING_TABLE : 

create or replace function OUTPUT_FCT() return MY_NEW_TYPE AS
...

Upvotes: 0

Views: 7576

Answers (2)

Aleksej
Aleksej

Reputation: 22959

If you only need to create a function that returns a row from your table, you could try something like the following, without creating types.

setup:

create table EXISTING_TABLE( a number, b varchar2(100));
insert into EXISTING_TABLE values (1, 'one');

function:

create or replace function OUTPUT_FCT return EXISTING_TABLE%rowtype AS
    retVal EXISTING_TABLE%rowType;
begin
    select *
    into retVal
    from EXISTING_TABLE
    where rownum = 1;
    --
    return retVal;
end;

function call

SQL> begin
  2      dbms_output.put_line(OUTPUT_FCT().a);
  3      dbms_output.put_line(OUTPUT_FCT().b);
  4  end;
  5  /
1
one

However, I would not recommend such an approach, because things like select * can be really dangerous; I would much prefer defining a type with the fields I need, and then explicitly query my table for the needed columns.

Upvotes: 3

Alex Poole
Alex Poole

Reputation: 191435

No, you can't do that, you'll get a compilation error:

create or replace type my_new_type as object(one_row t42%rowtype);
/

Type MY_NEW_TYPE compiled
Errors: check compiler log

show errors

Errors for TYPE STACKOVERFLOW.MY_NEW_TYPE:

LINE/COL ERROR
-------- -----------------------------------------------------------------------
0/0      PL/SQL: Compilation unit analysis terminated
1/36     PLS-00329: schema-level type has illegal reference to MYSCHEMA.T42

You will need to specify each field in the object type, and you will have to specify the data types manually too - you can't use table.column%type either.

You could create the type dynamically based on column and data type information from the data dictionary, but as this will (hopefully) be a one-off task and not something you'd do at runtime, that doesn't really seem worth it.

You can create a PL/SQL table type based on your table's rowtype, but you would only be able to call a function returning that from PL/SQL, not from plain SQL - so you couldn't use it in a table collection expression for example. If you were only returning a single sample row you could return a record rather than a table, but the same applies. You can also have a function that returns a ref cursor which could match the table's structure, but you wouldn't be able to treat that as a table either.

Read more about object type creation in the documentation. Specifically the attribute and datatype sections.

Upvotes: 2

Related Questions