Oracle function with table as input parameter

Question

How would you create a function in Oracle that has a table as an input parameter and return a string? Here is my attempt but is returning an error:

create or replace type temp_table as object (col_name varchar(100));
/
create or replace type col_table as TABLE of temp_table;
/
create or replace FUNCTION COLUMN_HEADERS
  (col_name in col_table)
  RETURN VARCHAR2 
is
  return_string VARCHAR2(4096);
BEGIN
  return_string := '';
  for i in 1 .. col_name.count loop
    return_string := return_string || ' ''' || col_name(i) || ''' as ' || regexp_replace(col_name(i), '[ \+]', '_');
    if i < col_name.count then
      return_string := return_string || ',';
    end if;
  end loop;
  RETURN return_string;
END;

I get the following:

Error(9,9): PL/SQL: Statement ignored
Error(9,26): PLS-00306: wrong number or types of arguments in call to '||'

Which points to this line:

return_string := return_string || ' ''' || col_name(i) || ''' as ' || regexp_replace(col_name(i), '[ \+]', '_');

My guess is that col_name(i) doesn't return a string but using VALUE() or TO_CHAR() gives me other errors. Does anyone know how to debug this?

WoMo · Accepted Answer · 2013-06-20 20:43:13Z

2

When you reference an index in the table using col_name(i), you then also need to reference the object property of that table index as in col_name(i).col_name. In your case you used col_name both as the object property and as your function argument. For clarity you might change that. This compiled for me:

create or replace type temp_table is object (col_name varchar(100));
/
create or replace type col_table is TABLE of temp_table;
/
create or replace FUNCTION COLUMN_HEADERS
  (col_name in col_table)
  RETURN VARCHAR2 
is
  return_string varchar2(4096);
BEGIN
  return_string := '';
  for i in 1 .. col_name.count loop
    return_string := return_string || ' ''' || col_name(i).col_name || ''' as ' || regexp_replace(col_name(i).col_name, '[ \+]', '_');
    if i < col_name.count then
      return_string := return_string || ',';
    end if;
  end loop;
  return return_string;
END;

answered Jun 20, 2013 at 20:43

WoMo

7,2862 gold badges31 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2506680 Over a year ago

Thanks! I think I ended up confusing myself with the nomenclature.

Collectives™ on Stack Overflow

Oracle function with table as input parameter

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related