Passing table name and column name dynamically to PL/SQL Stored procedure

Question

I am trying to pass table name and column name to a stored procedure in oracle , but it gives me following error: table or view does not exist

Below is the code:

create or replace procedure jz_dynamic_sql_statement
    (p_table_name    in varchar2,
     p_col1_name     in varchar2,
     p_check_result  out integer)

  as

    v_error_cd          est_runtime_error_log.error_cd%type;
    v_error_msg         est_runtime_error_log.error_msg%type;
    v_sql               varchar2(1024);
    v_result            number(10);

  begin
    v_result    := 0;
    v_sql       := 'select  count(*)  from ' || p_table_name ||' WHERE COLUMNNAME=' || p_col1_name;


    execute immediate v_sql into v_result;
    p_check_result := v_result;

  end;

The where clause does not make sense unless you were trying to obfuscate your DB column names on the post. — Andrew
– Andrew, Commented Mar 18, 2016 at 20:52
@GordonLinoff - table could exist but the caller vs definer permissions could be interfering with the visibility of the table. — Andrew
– Andrew, Commented Mar 18, 2016 at 20:54
Well, i need to filter it and i m going to use this idea for other purposes such as update statement — akaminko
– akaminko, Commented Mar 18, 2016 at 20:55

mjf200 · Accepted Answer · 2016-03-18 21:08:03Z

2

If the error coming back says the table does not exist then that means the table you pass in does not exist or the user that the procedure runs under cannot access it.

You could add a dbms_output.put_line statement to display the query that you are building and then try running it yourself, before you attempt the execute immediate. Then you know what errors you need to fix.

dbms_output.put_line('query : '||v_sql);

Be sure to turn on dbms_output.

Also, from what it looks like you are trying to do, you will need to pass the column name AND column value. Unless the tables you are querying will ALWAYS have the column name "COLUMNNAME".

Try this:

v_sql       := 'select  count(*)  from ' || p_table_name ||' WHERE COLUMNNAME=''' || p_col1_name|| '''';

edited Mar 18, 2016 at 21:08

answered Mar 18, 2016 at 21:00

mjf200

385 bronze badges

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

5 Comments

akaminko Over a year ago

this one runs well v_sql := 'select count(*) from ' || p_table_name ||''; but when i inlcude where clasue with the column name then it gives following error above.

mjf200 Over a year ago

does the table you trying to query have the column "COLUMNNAME"?

akaminko Over a year ago

Yes. it has a column called 'COLUMNNAME'

mjf200 Over a year ago

i just noticed you are not surrounding the COLUMNNAME value you passing in in single qutotes. I'll update my answer with more info.

Wernfried Domscheit Over a year ago

Better use

'select  count(*)  from ' || p_table_name ||' WHERE COLUMNNAME= :val'; execute immediate v_sql into v_result using p_col1_name;

Collectives™ on Stack Overflow

Passing table name and column name dynamically to PL/SQL Stored procedure

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related