CODEWITHSUNDEEP
oracle-databasefunctionplsql
user2506680
user2506680

Reputation: 23

Oracle function with table as input parameter

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?

Upvotes: 2

Views: 10913

Answers (1)

WoMo
WoMo

Reputation: 7266

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;

Upvotes: 2

Related Questions