CODEWITHSUNDEEP
sqloracle-database
user1856981
user1856981

Reputation: 19

SQL Use a Variable as tablename

i would like to set the Tablename as Variable

Why: i go from a oracle db to a mysql db via db link and in the mysql db i have some table-names which includes the date- example: data_20121126

here's my statement :

DECLARE
   tbname   VARCHAR2 (200);
BEGIN
   SELECT   ab.teste
     INTO   tbname
     FROM   (SELECT   '"data_'
                      || REPLACE (TO_CHAR (SYSDATE - 1, 'yyyy.mm.dd'),
                                  '.',
                                  '')
                      || '"@myDB'
                         AS teste
               FROM   DUAL) ab;
   SELECT   * FROM tbname;
END;

could anybody help me please?

thanks in advance

Upvotes: 1

Views: 2880

Answers (2)

Allan
Allan

Reputation: 17429

You can't do this without resorting to dynamic SQL:

DECLARE
   tbname   VARCHAR2 (200) := '"data_' 
                              || TO_CHAR (SYSDATE - 1, 'yyyymmdd')
                              || '"@myDB';
   vMyVariable varchar2(10);
BEGIN
   execute immediate "SELECT MyColumn
                      FROM " || tbname into vMyVariable ;
END;

Of course, this version assumes you'll be returning a single column from a single field into a variable. In all likelihood, you'll really want to return a ref cursor to your calling application.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30765

It's not possible with static SQL (and I doubt that creating a new table for every day in your MySQL database is a sensible design approach).

If you really need to do this, you can

  • use dynamic SQL (EXECUTE IMMEDIATE / OPEN CURSOR FOR ...)
  • create a synonym once per day, pointing to the current table, and use that synonym in your query

Upvotes: 2

Related Questions