PL SQL Dynamic SQL

Question

I want to create PL/SQL function recieves table name and column name and condition and then return one value from the table his name was passed in the parameter.

I have created the function like this:

create or replace function get_dynamic
  (tbl_name nvarchar2,col_name nvarchar2 ,cond nvarchar2 )
return nvarchar2 is
  res nvarchar2(30);
  code varchar2(500):='begin select :col_name into :res from :tbl_name where :cond; end;';
begin
  EXECUTE IMMEDIATE code using in col_name , out res , in tbl_name, in cond;
  return res;
end;

the function created without any problem BUT when I call the function using this code:

begin
    DBMS_OUTPUT.PUT_LINE(get_dynamic('EMPLOYEES', 'FIRST_NAME', 'EMPLOYEE_ID=100'));
end;

I get his Error:

ERROR at line 1:
ORA-06550: line 1, column 51:
PL/SQL: ORA-00903: invalid table name ORA-06550: line 1, column 7:
PL/SQL: SQL Statement ignored ORA-06512: at "HR.GET_DYNAMIC", line 7 ORA-06512: at line 2

although the table name, column name and condition all are correct.

Help Please!..

Petr Pribyl · Accepted Answer · 2016-09-17 16:52:21Z

4

You cannot use bind variables to construct SQL statement. You can use it for passing data only. Rewrite your procedure in this way:

create or replace function get_dynamic
  (tbl_name nvarchar2,col_name nvarchar2 ,cond nvarchar2 )
return nvarchar2 is
  res nvarchar2(30);
  code varchar2(500):='begin select '||col_name||' into :res from '||tbl_name||' where '||cond||'; end;';
begin
  EXECUTE IMMEDIATE code using out res;
  return res;
end;

answered Sep 17, 2016 at 16:52

Petr Pribyl

3,6151 gold badge22 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

PL SQL Dynamic SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related