Skip to main content

This content has been archived and is no longer being updated. Links may not function; however, this content may be relevant to outdated versions of the product.

Support Article

Oracle Data Dictionary Query not cached

SA-39157

Summary


PEGA0005 alerts generated frequently when executing system-level SQL in Oracle.

Query:


SELECT NULL AS table_cat, t.owner AS table_schem, t.table_name AS table_name, t.column_name AS column_name, decode (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93, 'RAW', 3, 'LONG RAW', 4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', 13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', 101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', 102, 'INTERVAL YEAR(2) TO MONTH', 103, 'INTERVAL DAY(2) TO SECOND(6)', 104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, t.data_type AS type_name, decode (t.data_precision, NULL, decode (t.data_type, 'CHAR', t.char_length, 'VARCHAR', t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2', t.char_length, t.data_length), t.data_precision) AS column_size, 0 AS buffer_length, t.data_scale AS decimal_digits, 10 AS num_prec_radix, decode (t.nullable, 'N', 0, 1) AS nullable, NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, decode (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t WHERE UPPER(t.table_name) = ? AND UPPER(t.owner) = ? ) UNION ALL ( SELECT NULL AS table_cat, s.owner AS table_schem, s.synonym_name AS table_name, t.column_name AS column_name, decode (t.data_type, 'CHAR', 1, 'VARCHAR2', 12, 'NUMBER', 3, 'LONG', -1, 'DATE', 93, 'RAW', 3, 'LONG RAW', 4, 'BLOB', 2004, 'CLOB', 2005, 'BFILE', 13, 'FLOAT', 6, 'TIMESTAMP(6)', 93, 'TIMESTAMP(6) WITH TIME ZONE', 101, 'TIMESTAMP(6) WITH LOCAL TIME ZONE', 102, 'INTERVAL YEAR(2) TO MONTH', 103, 'INTERVAL DAY(2) TO SECOND(6)', 104, 'BINARY_FLOAT', 100, 'BINARY_DOUBLE', 101, 1111) AS data_type, t.data_type AS type_name, decode (t.data_precision, NULL, decode (t.data_type, 'CHAR', t.char_length, 'VARCHAR', t.char_length, 'VARCHAR2', t.char_length, 'NVARCHAR2', t.char_length, t.data_length), t.data_precision) AS column_size, 0 AS buffer_length, t.data_scale AS decimal_digits, 10 AS num_prec_radix, decode (t.nullable, 'N', 0, 1) AS nullable, NULL AS remarks, t.data_default AS column_def, 0 AS sql_data_type, 0 AS sql_datetime_sub, t.data_length AS char_octet_length, t.column_id AS ordinal_position, decode (t.nullable, 'N', 'NO', 'YES') AS is_nullable FROM all_tab_columns t INNER JOIN all_synonyms s ON (s.table_name = t.table_name AND s.table_owner = t.owner) WHERE UPPER(s.synonym_name) = ? AND UPPER(s.owner) = ? ) ORDER BY table_schem, table_name, ordinal_position


Error Messages



Not Applicable


Steps to Reproduce



Any user action that processes the query.


Root Cause



The query is used to retrieve the list of columns for a given table. The column information is included in the TableInformation which can be reused instead of executing the query repeatedly.


Resolution

Apply HFix-34887.


Suggest Edit

Published November 3, 2017 - Updated October 8, 2020

Did you find this content helpful? Yes No

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.

We'd prefer it if you saw us at our best.

Pega Community has detected you are using a browser which may prevent you from experiencing the site as intended. To improve your experience, please update your browser.

Close Deprecation Notice
Contact us