Table Literals

Table literals are used to pass the name of a table or a placeholder value (instead of a table name) to a query. Table literals appear in the FROM clause of a SQL statement and consist of either the table name, or a SQL variable or API bind variable in place of the table name.

In this Topic:

Syntax

TABLE( <table_name> | <session_variable> | <bind_variable> )
table_name

An identifier for a table:

  • The identifier can be fully-qualified in the form of:

    db_name.schema_name.table_name
    schema_name.table_name
  • Double quotes are supported for individual object identifiers that are case-sensitive or contain spaces and special characters.

  • The entire identifer string must be enclosed in single quotes or $$. For example:

    'mytable' or $$mytable$$
    'mydb.myschema.mytable' or $$mydb.myschema.mytable$$
    '"DB 1"."Schema 1".mytable' or $$"DB 1"."Schema 1".mytable$$
session_variable
A SQL variable that has been set for the session.
bind_variable
A bind variable, in the standard form of ? or :name, for use with APIs that support bindings (Java, Python, etc.).

Usage Notes

  • Table literals are supported in the FROM clause only.
  • When a bind variable is used to prepare a statement, table metadata is not available after preparing the statement.

Examples

Query the table mytable using a table literal (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE('mytable');

SELECT * FROM TABLE($$mytable$$);

Query the table mytable in the schema myschema and the database mydb using a table literal (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE('mydb."myschema"."mytable"');

SELECT * FROM TABLE($$mydb."myschema"."mytable"$$);

Set a session variable that references a table name and then query the table using the variable passed as a table literal:

SET myvar = 'mytable';

SELECT * FROM TABLE($myvar);

Prepare a statement with a binding that represents a table (note that the following two examples are syntactically equivalent):

SELECT * FROM TABLE(?);

SELECT * FROM TABLE(:binding);