sql

sql

sql – What is the alternative to REGEXP_REPLACE to create a functional index?

Question: After migrating to Oracle 18c Enterprise Edition, functional index (FBI) creation fails. This is how the DDL for creating an index looks like: CREATE INDEX my_index ON my_table( UPPER (REGEXP_REPLACE (“DEPT_NUM”, ‘[^[:alnum:]]’, NULL, 1, 0))); I am getting the following error: ORA-01743: only pure functions can be indexed 01743. …

sql – What is the alternative to REGEXP_REPLACE to create a functional index? Read More »

sql – How to get information about database sessions that have open transactions?

Question: How to get information about all database sessions that have open transactions from data dictionary views? As far as I understand, you need to access v$session and v$transaction and make a selection of v$session.sid and v$session.username . For example, through the console and SQL*Plus as two columns – session …

sql – How to get information about database sessions that have open transactions? Read More »

SQL Date Month Grouping

Question: There is a certain table with dates and other infoy. it is necessary to group information only by months. Here is what I tried Select наименование, month(дата) FROM таблица1 Group by month(дата), наименование This code groups, but only if the date exactly matches. It is necessary for me that …

SQL Date Month Grouping Read More »

Why does NEXT_DAY work differently in PL/SQL than in SQL? ORA-01846: not a valid day of the week

Question: This SQL query works: SELECT NEXT_DAY (sysdate, 7) “NEXT DAY” FROM DUAL; NEXT DAY ——————- 2021-08-15 00:31:12 But in PL/SQL, the same function doesn't work: declare nextday date := NEXT_DAY (sysdate, 7); begin null; end; / ORA-01846: not a valid day of the week Wouldn't want to hardcode the …

Why does NEXT_DAY work differently in PL/SQL than in SQL? ORA-01846: not a valid day of the week Read More »

Scroll to Top