How to add where condition in a dynamic sql query?

Question

I am trying to write a procedure that uses a dynamic query to fetch records in different tables.Now I want to add a where condition to this clause. The procedure takes an input parameter and I want to use this parameter to compare the value of the same parameter present within the table. The following code doesn't work and I am not sure how to get it to work.

SQL> create or replace procedure p_count(x IN varchar2) as
  2    type   arr is varray(5) of varchar2(30);
  3    tables arr := arr('tb1', 'tb2', 'tb3');
  4    cnt    number;
  5  begin
  6     for i in 1 .. tables.count loop
  7       execute immediate 'select count(*) from ' || tables(i) ||' where tables(i).column_name ='||x into cnt;
  8       insert into tb6 values(tables(i), cnt);
  9     end loop;
 10  end;
 11  /

What is the "X" parameter? What will you pass through it? What is TABLES(I).COLUMN_NAME? Which column name? Could you post sample data and desired result? (BTW, there's a space missing in front of WHERE in line #7). — Littlefoot
– Littlefoot, Commented Apr 20, 2022 at 8:06
fixed the space. so the column name is CATEGORY which is a varchar field consisting of values like 'A' , 'B' , etc. X is again the CATEGORY name which I am passing to the procedure. I wish to compare the value of X with the values already present in the CATEGORY column of the table so as to filter my result by CATEGORY. — My thoughts
– My thoughts, Commented Apr 20, 2022 at 8:11
Does that then evaluate to e.g. select count(*) from tb1 where tb1.category = 'A'? — Littlefoot
– Littlefoot, Commented Apr 20, 2022 at 8:15

MT0 · Accepted Answer · 2022-04-20 08:24:54Z

1

You can use:

create or replace procedure p_count(x IN varchar2)
as
  type   arr is varray(5) of varchar2(30);
  tables arr := arr('tb1', 'tb2', 'tb3');
  cnt    number;
begin
  for i in 1 .. tables.count loop
    execute immediate 'select count(*)
                       from   ' || DBMS_ASSERT.SIMPLE_SQL_NAME(tables(i)) || '
                       where  column_name = :1'
                 INTO  cnt
                 USING x;
    insert into tb6 values(tables(i), cnt);
  end loop;
end;
/

Which, for the sample data:

CREATE TABLE tb1 (column_name) AS
SELECT 'aaa' FROM DUAL;

CREATE TABLE tb2 (column_name) AS
SELECT 'bbb' FROM DUAL;

CREATE TABLE tb3 (column_name) AS
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'aaa' FROM DUAL UNION ALL
SELECT 'bbb' FROM DUAL;

CREATE TABLE tb6 (
  table_name VARCHAR2(30),
  cnt        NUMBER(10,0)
);

Then after:

BEGIN
  p_count('aaa');
END;
/

tb6 contains:

TABLE_NAME CNT

tb1 1

tb2 0

tb3 2

db<>fiddle here

answered Apr 20, 2022 at 8:24

MT0

173k12 gold badges70 silver badges136 bronze badges

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

3 Comments

My thoughts Over a year ago

Can you please help me understand how is the line select count(*) from ' || DBMS_ASSERT.SIMPLE_SQL_NAME(tables(i)) || ' where column_name = :1 working?

My thoughts Over a year ago

I need to understand what does DBMS_ASSERT.SIMPLE_SQL_NAME and column_name= :1 do?

MT0 Over a year ago

@Mythoughts DBMS_ASSERT.SIMPLE_SQL_NAME is documented here (and is used to prevent SQL injection attacks) and :1 is a bind variable that is passed in from the USING clause of EXECUTE IMMEDIATE and will contain the x variable (again, a bind variable will help prevent SQL injection attacks).

Littlefoot · Accepted Answer · 2022-04-20 08:24:40Z

0

Based on comments you posted, that would be like this:

Sample table (tb1 has only one column: category; yours probably has many more columns; didn't feel like creating other tables so I used exception handling section within the loop which doesn't raise an error and lets you loop until the end).

SQL> SET SERVEROUTPUT ON
SQL>
SQL> SELECT * FROM tb1;

CATEGORY
--------
A
B
B

SQL>

Procedure:

SQL> CREATE OR REPLACE PROCEDURE p_count (x IN VARCHAR2)
  2  AS
  3     TYPE arr IS VARRAY (5) OF VARCHAR2 (30);
  4
  5     tables  arr := arr ('tb1', 'tb2', 'tb3');
  6     cnt     NUMBER;
  7     l_str   VARCHAR2 (500);
  8  BEGIN
  9     FOR i IN 1 .. tables.COUNT
 10     LOOP
 11        BEGIN
 12           l_str :=
 13                 'select count(*) from '
 14              || tables (i)
 15              || ' where category ='
 16              || DBMS_ASSERT.enquote_literal (x);
 17
 18           EXECUTE IMMEDIATE l_str
 19              INTO cnt;
 20
 21           INSERT INTO tb6
 22                VALUES (tables (i), cnt);
 23        EXCEPTION
 24           WHEN OTHERS
 25           THEN
 26              DBMS_OUTPUT.put_line (tables (i) || ': ' || SQLERRM);
 27        END;
 28     END LOOP;
 29  END;
 30  /

Procedure created.

Testing:

SQL> EXEC p_count('A');
tb2: ORA-00942: table or view does not exist
tb3: ORA-00942: table or view does not exist

PL/SQL procedure successfully completed.

SQL>

Result:

SQL> SELECT * FROM tb6;

NAME        CNT
---- ----------
tb1           1

SQL>

answered Apr 20, 2022 at 8:24

Littlefoot

144k15 gold badges42 silver badges65 bronze badges

2 Comments

My thoughts Over a year ago

Thanks alot this actually works do you mind elaborating why does this work though is it because of this statement that you added DBMS_ASSERT.enquote_literal (x)?

Littlefoot Over a year ago

You're welcome. It works because it is correctly written. DBMS_ASSERT is here just to prevent SQL injection; code would work well without it.

Collectives™ on Stack Overflow

How to add where condition in a dynamic sql query?

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related