CODEWITHSUNDEEP
sqloracle-databaseplsqlora-06550
TonyS
TonyS

Reputation: 99

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

Upvotes: 0

Views: 242

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

Use this

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

Upvotes: 1

Related Questions