Indirect identifiers

Specify indirect identifiers in statements by enclosing them in square brackets and back quotes (for example, '[@myVariable]'), where @myVariable is the name of an existing variable that stores the name of the actual object you are operating on.

When an identifier, A, in a statement specifies a variable that contains another identifier, B, identifier A is called an indirect identifier. If you use EXECUTE IMMEDIATE to dynamically construct statements inside procedures (specifically, if you are substituting identifiers in your DML EXECUTE IMMEDIATE statements), then consider using indirect identifiers instead. Indirect identifiers are a safer practice than using EXECUTE IMMEDIATE in your application logic.

Building indirect identifiers into your application logic improves the dynamic capability of your product. For example, suppose your application periodically creates a table using the table creation time stamp information as part of the identifier for the table (for example, CurrentOrders023003032015, where 023003032015 is the time stamp when the table was created). Now suppose that your application has a procedure that needs to query this dynamically named table. You could declare a variable called @currentOrders to store the table name, and then update the variable each time the table is created. Then, you could modify your procedure to include an indirect identifier for the table ('[@currentOrders]'). When the procedure is called, the indirect identifier is replaced with the value of the variable and the procedure runs as though the actual table name was specified.

Indirect identifiers are supported in SELECT statements, procedure and function calls, and DML statements as a substitute for an explicit identifier for the following objects:

• tables
• columns
• mutexes
• semaphores
• user IDs when specified as object owners (for example owner.object-name)

Indirect identifiers are also supported in statements that change the status of mutexes and semaphores (for example, WAITFOR SEMAPHORE statement).

Before a statement is executed, an indirect identifier is replaced by the value stored in the variable being referenced, and privilege checking is performed.

Indirect identifier values have a maximum length of 128 bytes and can be of type CHAR, VARCHAR, or LONG VARCHAR.

For statements where an identifier is required, if the indirect identifier used to specify the name of a column or table is NULL, an empty string, or another invalid name, then the result is in an error. However, an indirect identifier may be NULL if it is used as an optional part of a qualified name, such as the owner of a table. A NULL for an optional part of the identifier is treated as though it is absent.

An indirect identifier replaces one portion of an identifier; it cannot replace the full identifier specification. For example, if you have a variable @var set to 'GROUPO.Employees', then an error is returned if you attempt to perform a SELECT operation on the GROUPO.Employees table by using an indirect identifier (for example, SELECT * FROM '[@var]'). Instead, you must create a variable to store the user portion of the name, and then reference both objects using indirect references (for example, '[@owner]'.'[@var]')

Use of indirect identifiers overlaps with the use of table reference variables; both are ways of indirectly referring to a table. However, a table reference variable can provide access to a table that is not accessible in the current context, whereas an indirect identifier cannot. Additionally, table reference values are resolved at creation time, while indirect identifiers are resolved at run time.

Privileges on the objects being indirectly referenced in a statement are checked at the time that the indirect identifiers are evaluated and are enforced prior to the statement execution.

Many of the examples below show the variables being created using the CREATE VARIABLE statement; this was done to make the examples easy to try in Interactive SQL. However, a more likely scenario is that you would declare a variable (DECLARE statement) within the scope of a procedure or function, and then reference the variable as part of an indirect identifier in a subsequent statement within that procedure, or pass variables in as parameters.

The following example creates a variable called @col to hold the name of a column (Surname) in the GROUPO.Employees table. The SELECT statement queries the contents of the Employees.Surname column by specifying an indirect identifier ('[@col]'):

CREATE OR REPLACE VARIABLE @col LONG VARCHAR = 'Surname';
SELECT E.'[@col]' FROM GROUPO.Employees E;

The following example shows how to use indirect identifiers to query a table:

CREATE OR REPLACE VARIABLE t_owner LONG VARCHAR = 'GROUPO';
CREATE OR REPLACE VARIABLE t_name LONG VARCHAR = 'Employees';
SELECT * FROM '[t_owner]'.'[t_name]';

The following example creates a procedure with an IN parameter (@tableref) that takes a table reference, an IN parameter (@columnname) that takes the name of a column, and an IN parameter (@value) that takes an integer value reflecting the ID of the user to delete. The body of the procedure defines how the parameters will be used to delete the required record from the table.

CREATE PROCEDURE mydelete( IN @tableref TABLE REF,
                           IN @columnname LONG VARCHAR,
                           IN @value INT )
SQL SECURITY INVOKER
NO RESULT SET
BEGIN
    DELETE FROM TABLE REF (@tableref) AS T WHERE T.'[@columnname]' = @value;
END;

CALL mydelete( TABLE REF ( FTEmployee ), 'employee_id', @employee_to_delete);
CALL mydelete( TABLE REF ( FTStudent ), 'student_id',  @student_to_delete);

In the first CALL statement, the database server searches the FTEmployee.employee_id column for a row that matches the value stored in the @employee_to_delete variable, and then deletes the row. In the second CALL statement, the database server searches the FTStudent.student_id column for a row that matches the value stored in the @student_to_delete variable, and then deletes the row.