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, Mar 18 '16 at 20:52
@GordonLinoff - table could exist but the caller vs definer permissions could be interfering with the visibility of the table. — Andrew, Mar 18 '16 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, Mar 18 '16 at 20:55
I am runnnig it under same schema name so it should find the table i guess. — akaminko, Mar 18 '16 at 20:56

mjf200 · Accepted Answer · 2016-03-18T21:08:03.083

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 '16 at 21:08

answered Mar 18 '16 at 21:00

mjf200

38
4

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. – akaminko Mar 18 '16 at 21:02
does the table you trying to query have the column "COLUMNNAME"? – mjf200 Mar 18 '16 at 21:04
Yes. it has a column called 'COLUMNNAME' – akaminko Mar 18 '16 at 21:05
i just noticed you are not surrounding the COLUMNNAME value you passing in in single qutotes. I'll update my answer with more info. – mjf200 Mar 18 '16 at 21:06
Better use `'select count(*) from ' || p_table_name ||' WHERE COLUMNNAME= :val'; execute immediate v_sql into v_result using p_col1_name;` – Wernfried Domscheit Mar 18 '16 at 22:09

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

1 Answers1