[Oracle Tips] Looping over dates in PL/SQL

How do you loop over dates if you want to build your own calendar?

I had to generate a weekly calendar that had every week starting on the previous Saturday of a given date and ending on the next Friday. 

The following procedure solved my problem. The procedure takes two parameters: 

- PROCESS_START_DT (DATE)

- PROCESS_END_DT (DATE)

that define the time interval for which the calendar needs to be generated. 

I.e. If PROCESS_START_DT = '01-Jul-2017' and PROCESS_END_DT = '31-Jul-2017', the expected result will be: 

The result will be stored in a dedicated table. 

The key is to determine the day of the week given a date, in order to establish the previous Saturday and next Friday. To do that, we use the TRUNC(cut_date, 'IW') which will return the first day of the  ISO week. Using simply TRUNC would make the code dependent on the local NLS parameters.  

To loop from the Start Date to the End Date, we convert the dates to the julian format (to_number(to_char(PROCESS_START_DT,'j'))) and we implement a numeric loop starting from the PROCESS_START_DT (temporarly stored in cut_date variable) and we increment it by 7 days at each iteration (cut_date := cut_date + 7). 

create or replace PROCEDURE PR_XXX_XXX_GENERATE_CAL(PROCESS_START_DT DATE, PROCESS_END_DT DATE) AS  

cut_date date;

cal_year number;

st_dt date;

end_dt date;

cal_week_nbr number;

table_exists_flg number;

diff_days number; 

begin

/* Reset Variables */ 

cut_date := PROCESS_START_DT;

cal_week_nbr := 1; 

select count(*) into table_exists_flg

from all_tables

where table_name = upper('wc_xxx_xxx_calendar');

/* If the calendar table does not exists, then create it */

if table_exists_flg = 0 then 

execute immediate 'create table wc_xxx_xxx_calendar

(CAL_YEAR NUMBER,

CAL_WEEK_NBR NUMBER,

EFFECTIVE_START_DT DATE,

EFFECTIVE_END_DT DATE)';

end if;

/* Truncate Calendar Table */ 

execute immediate 'truncate table wc_xxx_xxx_calendar';

commit;

for i in to_number(to_char(PROCESS_START_DT,'j'))..to_number(to_char(PROCESS_END_DT,'j')) loop

/* Make sure that always previous Saturday and next Friday are retrieved, independently of the DB Localization */

if  to_date(i,'j') = cut_date then 

/* Removed because it depends on Localization */ 

/* select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'd')-2 , trunc(cut_date, 'd')+4  */ 

/* Calculate difference in days from cut_date and previous Monday */

select  cut_date - trunc(cut_date, 'IW') into diff_days 

from dual; 

if (diff_days >= 5) then 

/* cut_date is a Saturday or Sunday - always current Saturday is returned */ 

select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'IW')+5 , trunc(cut_date+7, 'IW')+4  

into cal_year, st_dt, end_dt

from dual; 

else 

/* Any weekday - except for Saturday and Sunday */

select EXTRACT(YEAR FROM cut_date), trunc(cut_date, 'IW')-2 , trunc(cut_date, 'IW')+4  

into cal_year, st_dt, end_dt

from dual; 

end if; 

insert into wc_xxx_xxx_calendar values (cal_year, cal_week_nbr, st_dt, end_dt);

COMMIT; 

cal_week_nbr := cal_week_nbr + 1;

cut_date := cut_date + 7;

end if;

END LOOP;

END PR_XXX_XXX_GENERATE_CAL;

[Oracle Tips] Looking for a value in all table columns of a database schema

I have been working on a project where honestly I did not have much knowledge of the data model of the schema I was using. Most of the times I had to figure out in which table (and column) I could find a certain value. Not the best strategy, but Oracle documentation and the project schedule were not not of much help.
The following procedure was extremely useful and has helped me out in many occasions. Depending on the size of the tables that are being scanned, the procedure can take quite a long time to be executed, but it will retrieve all the values that match 'v_search_string' in any column of the datatype and the schema specified as a parameters.

set serveroutput on size 100000;

declare
    v_match_count integer;
    v_counter integer;

    -- The owner of the tables to search through (case-sensitive)
    v_owner varchar2(255) := 'SCHEMA_OWNER';
    -- A string that is part of the data type(s) of the columns to search through (case-insensitive)
    v_data_type varchar2(255) := 'COLUMN_DATATYPE';
    -- The string to be searched for (case-insensitive)
    v_search_string varchar2(4000) := 'VALUE_TO_BE_SEARCHED';

    -- Store the SQL to execute for each table in a CLOB to get around the 32767 byte max size for a VARCHAR2 in PL/SQL
    v_sql clob := '';
begin
    for cur_tables in (select owner, table_name from all_tables where owner = v_owner and table_name in
                       (select table_name from all_tab_columns where owner = all_tables.owner and data_type like '%' ||  upper(v_data_type) || '%')
                       order by table_name) loop
        v_counter := 0;
        v_sql := '';

        for cur_columns in (select column_name from all_tab_columns where
                            owner = v_owner and table_name = cur_tables.table_name and data_type like '%' || upper(v_data_type) || '%') loop
            if v_counter > 0 then
                v_sql := v_sql || ' or ';
            end if;
            v_sql := v_sql || 'upper(' || cur_columns.column_name || ') like ''%' || upper(v_search_string) || '%''';
            v_counter := v_counter + 1;
        end loop;

        v_sql := 'select count(*) from '  || v_owner || '.' || cur_tables.table_name || ' where ' || v_sql;

        execute immediate v_sql
        into v_match_count;

        if v_match_count > 0 then
            dbms_output.put_line('Match in ' || cur_tables.owner || ': ' || cur_tables.table_name || ' - ' || v_match_count || ' records');
        end if;
    end loop;

    exception
        when others then
            dbms_output.put_line('Error when executing the following: ' || dbms_lob.substr(v_sql, 32600));
end;
/


Here is the output of the procedure if I look for 'CRISTINA' string of 'VARCHAR' type in any table column in the 'A' schema: 

Note: the procedure will work only if you have the right privileges to query all_tables and all_tab_columns and you have at least read privileges to the schema
 

ORA-02292: integrity constraint (.) violated - child record

I was writing an ODI procedure from creating a static dimension from scratch. The procedure looked like this:

begin
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT;
end

When I run it, I got the following error:

ORA-02292: integrity constraint (<OWNER>.<FK_CONSTRAINT>) violated - child record

The error means that I was trying to delete values that were used in a column that had a foreign key constraint on it.
To overcome this issue, without deleting any value in the referencing table, you first have to disable the constraint and then enable it again once you have finished populating the table.
So the procedure will look like this:


begin
execute immediate 'ALTER TABLE <DIM_TABLE> DISABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
DELETE FROM <DIM_TABLE>;
INSERT INTO <DIM_TABLE> VALUES (0,1,etc.);
COMMIT; 
execute immediate 'ALTER TABLE <DIM_TABLE> ENABLE CONSTRAINT <OWNER>.<FK_CONSTRAINT>';
end