Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@contrib/ivorysql_ora/sql/ora_sysview.sql`:
- Around line 216-260: The current test mixes a negative "invalid identifier"
check for COLLATION_NAME into the positive assertion that queries
SYS.ALL_TAB_COLUMNS for TEST_ALL_TAB_COLUMNS, so the whole block fails to
validate real column data; remove COLLATION_NAME from the SELECT list in the
successful assertion (the SELECT that lists COLUMN_ID, DATA_TYPE, NULLABLE,
DATA_DEFAULT, etc. and ends with ORDER BY COLUMN_ID) so it returns a valid
result set, and add a separate negative test that intentionally selects
COLLATION_NAME from SYS.ALL_TAB_COLUMNS (targeting TABLE_NAME =
'TEST_ALL_TAB_COLUMNS') asserting it raises an invalid identifier error.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Nitpick comments:
In `@contrib/ivorysql_ora/expected/ora_sysview.out`:
- Around line 345-403: Add a positive test that queries SYS.ALL_TAB_COLUMNS for
the created TEST_ALL_TAB_COLUMNS table to verify it returns expected metadata
(e.g., COLUMN_ID, COLUMN_NAME, DATA_TYPE, DATA_PRECISION, NULLABLE,
DATA_DEFAULT) and correct type mappings for VARCHAR2/NUMERIC/DATE; locate the
current failing negative test that selects COLLATION from SYS.ALL_TAB_COLUMNS
and either remove the unquoted COLLATION reference or replace it with a quoted
"COLLATION" (or simply omit that column) in the SELECT so the query succeeds,
then add assertions that the returned rows include the three columns NAME, AGE,
CREATE_TIME with correct DATA_TYPE and precision values for
TEST_ALL_TAB_COLUMNS.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@ora_sysview.sql`:
- Around line 24-26: The SELECT from ALL_PROCEDURES filtering OBJECT_NAME =
'PROC_DEL_TB' OR 'FUNC_AUTHID' returns multiple rows without a deterministic
order; update that query (and the other dictionary-view query referenced later)
to include an explicit ORDER BY (for example ORDER BY OBJECT_NAME, OBJECT_TYPE
or another stable key) so ora_sysview.out is deterministic; locate the SELECT
against ALL_PROCEDURES and the similar query around the later occurrence and
append a suitable ORDER BY clause to each.
- Around line 258-260: The query against SYS.ALL_TAB_COLUMNS is only filtering
TABLE_NAME and can return same-named tables from other schemas; update the WHERE
clause that queries SYS.ALL_TAB_COLUMNS for 'TEST_ALL_TAB_COLUMNS' to also
filter by OWNER (e.g., AND OWNER = '<EXPECTED_SCHEMA>' or AND OWNER = USER/
SYS_CONTEXT('USERENV','CURRENT_SCHEMA')) so the test only inspects the intended
schema; leave the ORDER BY COLUMN_ID as-is.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@contrib/ivorysql_ora/expected/ora_sysview.out`:
- Around line 345-402: The ALL_TAB_COLUMNS test returns no rows because the
WHERE clause compares OWNER = CURRENT_USER while the view projects OWNER via
sys.ora_case_trans(...), causing a case-mismatch; update the test's WHERE clause
to use the same transformation as the view (i.e., WHERE OWNER =
SYS.ORA_CASE_TRANS(CURRENT_USER::VARCHAR2)) so the OWNER filter matches and the
SELECT from SYS.ALL_TAB_COLUMNS for TEST_ALL_TAB_COLUMNS returns the expected 4
rows to validate DATA_TYPE, DATA_LENGTH, COLUMN_ID ordering, etc.; change the
predicate in the test that references OWNER/CURRENT_USER to use
SYS.ORA_CASE_TRANS(CURRENT_USER::VARCHAR2) and regenerate the expected output.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@contrib/ivorysql_ora/sql/ora_sysview.sql`:
- Line 236: The test/view is flaky because it selects the raw LAST_ANALYZED
timestamp; change the expectation to a deterministic assertion instead of
snapshotting the timestamp: update the code that emits or asserts LAST_ANALYZED
so it checks nullness (e.g., assert LAST_ANALYZED IS NULL or IS NOT NULL) or
returns a stable token (e.g., 'NULL'/'NOT NULL') rather than the real timestamp;
locate occurrences of LAST_ANALYZED in the SQL/view and replace the raw column
output with a deterministic null-check or token.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@contrib/ivorysql_ora/src/sysview/sysview--1.0.sql`:
- Around line 1492-1496: The CASE that computes char_used is incorrect: remove
the PostgreSQL internal 'char' token from the first WHEN list and make varchar
and bpchar map consistently (either both to NULL or both to the same value);
specifically, update the CASE over pg_type.typname used to produce char_used so
that bpchar (Oracle CHAR) stays in the 'C' branch, the
oracharchar/oracharbyte/oravarcharchar/oravarcharbyte/orachar* variants remain
split into 'C'/'B' as appropriate, and either drop varchar from the 'B' branch
so both varchar and bpchar fall through to NULL, or place varchar with bpchar
into the same branch—edit the CASE that selects AS char_used accordingly.
- Around line 1479-1483: The scalar subquery that defines avg_col_len reads
pg_statistic directly and duplicates the already-joined pg_stats; replace that
correlated subquery with the avg_width column from the existing LEFT JOIN to
pg_stats (use pg_stats.avg_width cast/alias as needed for numeric and preserve
NULL behavior), remove the pg_statistic lookup in the avg_col_len expression,
and ensure the view continues to expose avg_col_len using the joined pg_stats
value (refer to avg_col_len, pg_stats, and avg_width to locate and update the
code).
- Around line 1425-1426: The DATA_LENGTH expression for plain varchar/bpchar
currently returns pg_attribute.atttypmod - 4 (a character count); change it to
return the byte length by multiplying (pg_attribute.atttypmod - 4) by
pg_encoding_max_length(pg_database.encoding) (the same approach used in the
oracharchar/oravarcharchar branch) so DATA_LENGTH represents bytes, not
characters, or alternatively document the semantic change if you intentionally
want characters; update the WHEN branch that checks pg_type.typname IN
('varchar','bpchar') to use the encoding-aware multiplication.
- Line 1394: The OWNER column is using pg_get_userbyid(pg_class.relowner) (the
role name) but should use the schema name from pg_namespace; replace the
expression
sys.ora_case_trans(pg_get_userbyid(pg_class.relowner)::varchar2)::varchar2(128)
AS owner with an expression that uses pg_namespace.nspname passed through
sys.ora_case_trans (e.g.,
sys.ora_case_trans(pg_namespace.nspname::varchar2)::varchar2(128) AS owner) so
OWNER reflects the schema name rather than the owning role.
- Line 1397: Replace the direct use of pg_attribute.attnum for COLUMN_ID with a
dense sequential ordinal computed per table: compute COLUMN_ID as a row-number
over the non-dropped attributes partitioned by the table (use
pg_attribute.attrelid) and ordered by pg_attribute.attnum so dropped columns
(attisdropped) do not create gaps; update the column expression that currently
references pg_attribute.attnum::numeric to use ROW_NUMBER() OVER (PARTITION BY
pg_attribute.attrelid ORDER BY pg_attribute.attnum) (or equivalent) and ensure
the existing attisdropped filter is applied.
- Line 1528: The ALL_TAB_COLUMNS view currently filters pg_class.relkind IN
('r','v','m','p','f') which lets partition child tables (relkind='r' and
relispartition=true) produce duplicate rows; modify the view's WHERE clause to
add AND NOT pg_class.relispartition to exclude partition children while keeping
partitioned parents, updating the definition of the ALL_TAB_COLUMNS view (search
for the SELECT that references pg_class.relkind) and then add a regression test
that creates a partitioned table fixture and asserts the view returns exactly
one row per logical column for the parent table.

Verify each finding against current code. Fix only still-valid issues, skip the
rest with a brief reason, keep changes minimal, and validate.

Inline comments:
In `@contrib/ivorysql_ora/src/sysview/sysview--1.0.sql`:
- Around line 1488-1495: The CASE that computes char_col_decl_length wrongly
multiplies pg_attribute.atttypmod-derived character counts by
pg_catalog.pg_encoding_max_length(), converting character counts to bytes;
update the logic in the char_col_decl_length expression (the CASE referencing
pg_type.typname, pg_attribute.atttypmod and
pg_catalog.pg_encoding_max_length(current_database())) to remove the
multiplication for CHAR-semantics types so it returns the declared length in
character units (native units), leaving any byte-size computation for
DATA_LENGTH instead; apply the same fix to the analogous expression earlier in
the file that uses the same pattern near the other CASE at lines ~1438–1439.
- Line 1486: Replace the current_setting('server_encoding')::varchar2(44) AS
character_set_name expression with a CASE that returns 'CHAR_CS' for character
types, 'NCHAR_CS' for national character types and NULL otherwise, reusing the
same type-checking logic already used elsewhere in this file (the expression
testing a.atttypid/a.atttypmod or t.typcategory/format_type). Concretely, locate
the existing type-checking expressions (used for COLLATION_NAME or similar
columns) and copy that pattern into a CASE WHEN ... THEN 'CHAR_CS' WHEN ... THEN
'NCHAR_CS' ELSE NULL END AS character_set_name, replacing the current_setting
call.