12.3. RDB$SQL

A package with utility routines to work with dynamic SQL.

12.3.1. Procedure EXPLAIN

A selectable procedure that returns a tabular representation of a query’s plan, without executing the query.

Input parameters
SQL

type BLOB SUB_TYPE TEXT CHARACTER SET UTF8 — query statement

Output parameters
PLAN_LINE

type INTEGER — plan’s line order

RECORD_SOURCE_ID

type BIGINT — record source id

PARENT_RECORD_SOURCE_ID

type BIGINT - parent record source id

LEVEL

type INTEGER — indentation level (may have gaps in relation to parent’s level)

SCHEMA_NAME

type RDB$SCHEMA_NAME — schema name of object (table, procedure)

PACKAGE_NAME

type RDB$PACKAGE_NAME — package name of a stored procedure

OBJECT_NAME

type RDB$RELATION_NAME — object (table, procedure) name

ALIAS

type RDB$SHORT_DESCRIPTION — alias name

RECORD_LENGTH

type INTEGER — record length for the record source

KEY_LENGTH

type INTEGER — key length for the record source

ACCESS_PATH

type RDB$DESCRIPTION — friendly plan description

As SQL statements can contain quotes, it is recommended to use alternative string literals (Q-literals). If your statement text also contains Q-literals, make sure to use another start and end pair.

Example single line 

select *
from RDB$SQL.EXPLAIN('select * from EMPLOYEE where EMP_NO = ?');

Example multi-line (with Q-literal) 

select *
from RDB$SQL.EXPLAIN(q'{
  select *
  from (
    select FULL_NAME NAME from EMPLOYEE
    union all
    select CUSTOMER NAME from CUSTOMER
  )
  where NAME = ?
}');

12.3.2. Procedure PARSE_UNQUALIFIED_NAMES

A selectable procedure that parses a list of unqualified SQL identifiers and returns a row for each identifier. The input must follow syntax rules for names and the output of unquoted names are uppercased.

Input parameters
NAMES

type VARCHAR(8191) CHARACTER SET UTF8 — list of SQL identifiers

Output parameters
NAME

type RDB$RELATION_NAME — a single SQL identifier, normalised to how identifiers are stored in the metadata tables

select *
from RDB$SQL.PARSE_UNQUALIFIED_NAMES(
  'schema1, schema2, "schema3", "schema 4", "schema ""5"""');
 
-- SCHEMA1
-- SCHEMA2
-- schema3
-- schema 4
-- schema "5"