Снежинка — проблема при использовании переменной Bind

пройдя через код:

CREATE OR REPLACE PROCEDURE sp_1(table_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
    full_table_name VARCHAR DEFAULT CONCAT('db.schema.', :table_name);
begin
    return full_table_name;
end;
$$;

call sp_1('aa');

счастлив, хорошо. Одна из точек зрения Ника заключается в том, что это может быть:

full_table_name VARCHAR DEFAULT CONCAT('db.schema.', table_name);

CREATE OR REPLACE PROCEDURE sp_2(table_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
    full_table_name VARCHAR DEFAULT CONCAT('db.schema.',:table_name);
    select_statement VARCHAR;
begin
    select_statement := 'SELECT SUM(NVL(flag,0)) flg_sum FROM ' || IDENTIFIER(:full_table_name);
    return select_statement;
end;
$$;

Ошибка: переменная привязки для объекта «full_table_name» не установлена ​​(строка 20).

так что это можно изменить на

CREATE OR REPLACE PROCEDURE sp_3(table_name VARCHAR)
RETURNS VARCHAR NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
    full_table_name VARCHAR DEFAULT CONCAT('db.schema.',:table_name);
    select_statement VARCHAR;
begin
    select_statement := 'SELECT SUM(NVL(flag,0)) flg_sum FROM ' || full_table_name;
    return select_statement;
end;
$$;

и поэтому:

call sp_3('aa');

Итак, дальше немного фейковых данных в моем test.test:

create table aa(flag number);
insert into aa values(1),(3);

CREATE OR REPLACE PROCEDURE sp_5(table_name VARCHAR)
RETURNS table(text) NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
    full_table_name VARCHAR DEFAULT CONCAT('test.test.',:table_name);
    select_statement VARCHAR;
    result_select_statement RESULTSET;
begin
    select_statement := 'SELECT SUM(NVL(flag,0))::text flg_sum FROM ' || full_table_name;
    result_select_statement := (EXECUTE IMMEDIATE :select_statement);
    return table(result_select_statement);
end;
$$;

call sp_5('aa');

Теперь, если вы хотите использовать IDENTIFIER для обеспечения безопасности, ему необходимо передать строку, поэтому, чтобы получить результаты в построении строки select_statement, необходимо экранировать кавычки:

то есть, если кавычка экранирована, генерируется этот SQL:

SELECT SUM(NVL(flag,0))::text flg_sum FROM IDENTIFIER(test.test.aa)

и это неверно:

Ошибка компиляции SQL: строка синтаксической ошибки 1 в позиции 54, неожиданный «тест».

и должно быть:

SELECT SUM(NVL(flag,0))::text flg_sum FROM IDENTIFIER('test.test.aa')

таким образом, SP должен быть:

CREATE OR REPLACE PROCEDURE sp_6(table_name VARCHAR)
RETURNS table(text) NOT NULL
LANGUAGE SQL
AS
$$
DECLARE
    full_table_name VARCHAR DEFAULT CONCAT('test.test.',:table_name);
    select_statement VARCHAR;
    result_select_statement RESULTSET;
begin
    select_statement := 'SELECT SUM(NVL(flag,0))::text flg_sum FROM IDENTIFIER(''' || full_table_name || ''')';
    result_select_statement := (EXECUTE IMMEDIATE :select_statement);
    return table(result_select_statement);
end;
$$;

call sp_6('aa');