Search code examples
sqloracleplsqlora-06550

Table as a variable Oracle

Is it possible to retrive table name from variable in Oracle?

I'd like to do something like that:

DECLARE
    v_tab VARCHAR2(200) := adm.t4_to@wtaa;
    cnt   NUMBER;
BEGIN
    SELECT Count(*)
    INTO   cnt
    FROM   v_tab;
END;

When I tried to run that block I had an error:

Error report -
ORA-06550: linia 3, kolumna 24:
PLS-00224: object 'adm.t4_to@wtaa' must be of type function or array to be used this way
Solution

  • Use this

    DECLARE
  v_tab VARCHAR2(200) := 'adm.t4_to@wtaa' ;
  cnt   NUMBER;
BEGIN
  Execute immediate 'SELECT Count(*) from ' || v_tab 
  INTO   cnt;
END;