Oracle function dynamic column

Question

I have a function, and I want to determine the name of the column in run time. For this I am passing one variable as an argument, like column_name.

Below is the code with the function:

l_column_name as varchar2(100)

Begin 

If(column_name='emp_name')
  Then 
  l_column_name:=EMPLOYEE.EMP_NAME
End If;

   begin
 select l_column_name from employee
end;

In above code, l_column_name:=EMPLOYEE.EMP_NAME is giving the error

Not allowed in this context.

Any help is much appreicated.

Regards, Chaitu

Thomas Jones-Low · Accepted Answer · 2012-12-19 04:08:47Z

4

As the error says, you can not do this.

You need to look into using the PL/SQL Execute Immediate: http://docs.oracle.com/cd/B14117_01/appdev.101/b10807/13_elems017.htm

declare
  l_column_name as varchar2(100);
  l_column_results as VARCHAR2(100);
begin
  if (column_name = 'emp_name') then
     l_column_name := 'EMPLOYEE.EMP_NAME';
  end if;
  query := 'SELECT ' || l_column_name || ' from employeee';
  EXECUTE IMMEDIATE query INTO l_column_results;
end;

answered Dec 19, 2012 at 4:08

Thomas Jones-Low

7,1812 gold badges35 silver badges37 bronze badges

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

1 Comment

Thomas Jones-Low Over a year ago

yes, it's the only way to get results from the query back into your pl/sql code.

Collectives™ on Stack Overflow

Oracle function dynamic column

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