Create type table of variable How do I set the field name?-CodePudding

create or replace type TableOfNumber as table of number;

I created a table of a number to be used as the output of a function

create or replace function GetItems()
return TableOfNumber as
Res TableOfNumber;
  sqlstr varchar2(500);
begin
  -- Test statements here
    sqlstr:=  'select swid from ITEMS i' ;
     dbms_output.put_line (sqlstr);
  EXECUTE IMMEDIATE sqlstr
  BULK COLLECT INTO Res ;
 return(Res);
end ;

I want to change the field name from column_value hg To which ITid when creating the type And change the query clause from

select column_value from GetItems()

select ITid from GetItems()

create or replace type TableOfNumber  as table of number ''' name ITid ''';

CodePudding user response：

column_value is a pseudocolumn and - as far as I can tell - you can't avoid it if you're doing what you're doing.

But, if you switch to the following example, that's another story. You'll need two types:

SQL> create or replace type t_row as object (deptno number);
  2  /

Type created.

SQL> create or replace type t_tab as table of t_row;
  2  /

Type created.

Function (without dynamic SQL. Why did you use it? There's nothing dynamic in your code):

SQL> create or replace function getitems
  2    return t_tab
  3  as
  4    res t_tab;
  5  begin
  6    select t_row(deptno)
  7      bulk collect
  8      into res
  9      from dept;
 10    return res;
 11  end;
 12  /

Function created.

Finally: column name is deptno, not column_value:

SQL> select * from table(getitems);

    DEPTNO
----------
        10
        20
        30
        40

SQL>