Применить предикат к all_tab_cols.data_default в Oracle
В системном представлении Oracle all_tab_cols есть столбец data_default. Этот столбец объявлен как LONG, но при запросе представления значение возвращается в виде строки, если значением по умолчанию для столбца является выражение. Например:
CREATE TABLE tab1 (
col1 NUMBER DEFAULT mysequence.nextval
);
Запрос SELECT data_default FROM all_tab_cols WHERE table_name = 'TAB1' AND column_name = 'COL1'; возвращает строку «MYUSER».»MYSEQUENCE».»NEXTVAL».
Если я изменю предикат на WHERE table_name = 'TAB1' AND data_default LIKE '"MYUSER"%', запрос завершится с ошибкой ORA-00932 «Несовместимые типы данных: ожидаемый CHAR получил LONG»
Как я могу получить значение по умолчанию в форме o, чтобы применить к нему предикат?
Помимо описанной выше попытки, я также попробовал привести столбец к типу символов. Я получаю ту же ошибку. Анализ вывода DBMS_METADATA.GET_DDL слишком дорог и подвержен ошибкам, чтобы его можно было использовать.
Ответы 2
Вы можете использовать PL/SQL для преобразования LONG в varchar2 просто путем присваивания, а затем вызвать его из SQL или обернуть вокруг него представление и запросить представление:
CREATE OR REPLACE FUNCTION f_dba_tab_columns_data_default(in_owner IN varchar2, in_table_name IN varchar2, in_column_Name IN varchar2)
RETURN varchar2
AS
var_data_default varchar2(4000);
BEGIN
FOR rec_expr IN (SELECT *
FROM dba_tab_columns
WHERE owner = in_owner
AND table_name = in_table_name
AND column_name = in_column_name)
LOOP
var_data_default := rec_expr.data_default;
END LOOP;
RETURN var_data_default;
END;
/
CREATE OR REPLACE VIEW all_tab_columns_ext
AS
SELECT owner,
table_name,
column_name,
data_type,
data_type_mod,
data_type_owner,
data_length,
data_precision,
data_scale,
nullable,
column_id,
default_length,
f_dba_tab_columns_data_default(owner,table_name,column_name) AS data_default,
num_distinct,
low_value,
high_value,
density,
num_nulls,
num_buckets,
last_analyzed,
sample_size,
character_set_name,
char_col_decl_length,
global_stats,
user_stats,
avg_col_len,
char_length,
char_used,
v80_fmt_image,
data_upgraded,
histogram,
default_on_null,
identity_column,
evaluation_edition,
unusable_before,
unusable_beginning,
collation
FROM all_tab_columns
ORDER BY owner,table_name,column_id
Теперь запрос:
SELECT data_default FROM all_tab_columns_ext WHERE table_name = 'TAB1' AND data_default LIKE '"MYUSER"%'
Это работает до тех пор, пока размер по умолчанию умещается в 4 КБ, но я никогда не видел, чтобы по умолчанию это не делалось. То же самое можно использовать в dba_tab_cols, dba_ind_expressions, dba_tab_partitions и везде, где LONG без необходимости используется для размера, который никогда не превысит 4 КБ. Функция выше использует представления dba_*, но, конечно, вы можете адаптировать ее для all_*, если у вас нет привата select any dictionary.
@ArtKaufmann Если вы не можете создавать объекты, вы можете внести небольшие изменения в это решение и использовать его как один запрос. Вместо создания функции вы можете определить функцию в предложении WITH, а затем использовать эту функцию в запросе. Окончательный запрос будет выглядеть так: WITH function f_dba_tab_columns_data_default ... END; SELECT owner, ....
В Oracle 23c был добавлен новый столбец data_default_vc, который описан в документации как:
Этот столбец может обрезать значение по умолчанию.
Итак, из этой версии вы можете сделать:
SELECT data_default FROM all_tab_cols
WHERE table_name = 'TAB1' AND data_default_vc LIKE '"MYUSER"%';
(Предположительно, не актуально для ФП, но включено для будущих посетителей...)
Рад слышать, что они наконец-то прислушались. Трудно представить, что они столько лет навязывали нам long...
Спасибо. К сожалению, мое приложение должно работать с 12c, 18c, 19c и 21c!
Спасибо. Возможно, я не смогу использовать это, поскольку БД принадлежит клиенту, и я не думаю, что смогу вносить в нее изменения. В любом случае, это дало мне некоторые идеи о том, как я могу справиться с этим в коде приложения.