dynamically pass table name in oracle for loop

Question

Is it possible to dynamically pass table name in oracle for loop ?

e.g :-

for nm in ('select * from '|| table_name))
     loop 

      dbms_output.put_line('chetan') ;
end loop;

The above code prints out one word multiple times. What are you trying to do? — Burhan Ali
– Burhan Ali, Commented Jan 23, 2012 at 23:57

Florin Ghita · Accepted Answer · 2012-01-23 09:40:36Z

1

You can use a REF CURSOR type cursor. From Oracle Doc: (contains also binds - you don't asked for them...)

CREATE OR REPLACE PROCEDURE query_invoice(
       month VARCHAR2, 
       year VARCHAR2) IS
    TYPE cur_typ IS REF CURSOR;
    c cur_typ;
    query_str VARCHAR2(200);
    inv_num NUMBER;
    inv_cust VARCHAR2(20);
    inv_amt NUMBER;
BEGIN
    query_str := 'SELECT num, cust, amt FROM inv_' || month ||'_'|| year 
      || ' WHERE invnum = :id';
    OPEN c FOR query_str USING inv_num;
    LOOP
        FETCH c INTO inv_num, inv_cust, inv_amt;
        EXIT WHEN c%NOTFOUND;
        -- process row here
    END LOOP;
    CLOSE c;
END;
/

answered Jan 23, 2012 at 9:40

Florin Ghita

17.7k6 gold badges60 silver badges78 bronze badges

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

Collectives™ on Stack Overflow

dynamically pass table name in oracle for loop

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related