CODEWITHSUNDEEP
sqloracle-databasedynamiccount
user3663500
user3663500

Reputation: 3

Dynamic SQL Query using loops

I have 4 tables tab_1, tab_2, tab_3 and tab_4 how can i get the count of all the 4 tables using one single dynamic query?

expected result:

count of tab_1 = 
count of tab_2 =
count of tab_3 = 
count of tab_4 =

Thanks in Advance

Upvotes: 0

Views: 52

Answers (3)

Arielhhs
Arielhhs

Reputation: 106

Although I fail to see why you would want this, you ask, we answer.

To squish this all into 1 single query, you could try using 3 scalar subqueries in 1 main query from dual.

Select (Select Count() From tab_1) As CountOfTab1, (Select Count() From tab_2) As CountOfTab2, (Select Count() From tab_3) As CountOfTab3, (Select Count() From tab_4) As CountOfTab4 Into countOfTab1, countOfTab2, countOfTab3, countOfTab4 From Dual

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231661

It sounds like you want to run four separate queries, not a single query. It sounds like you're describing something like

DECLARE
  TYPE tbl_list IS TABLE OF VARCHAR2(30);
  l_tables tbl_list := tbl_list( 'table_1', 'table_2', 'table_3', 'table_4' );

  l_cnt pls_integer;
BEGIN
  FOR i IN 1 .. l_tables.count
  LOOP
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || l_tables(i)
       INTO l_cnt;
    dbms_output.put_line( 'Count of ' ||  l_tables(i) || ' = ' || l_cnt );
  END LOOP;
END;

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please try:

SET SERVEROUTPUT ON
DECLARE
  result1 NUMBER; result2 NUMBER; result3 NUMBER; result4 NUMBER;
BEGIN
    select count(*) into result1 from tab_1;
    select count(*) into result2 from tab_2;
    select count(*) into result3 from tab_3;
    select count(*) into result4 from tab_4;

    DBMS_OUTPUT.PUT_LINE('count of tab_1=' || result1);
    DBMS_OUTPUT.PUT_LINE('count of tab_2=' || result2);
    DBMS_OUTPUT.PUT_LINE('count of tab_3=' || result3);
    DBMS_OUTPUT.PUT_LINE('count of tab_4=' || result4);

END;

Upvotes: 0

Related Questions