如何在ODI 11g中删除最后三个月etl_date的相关记录？

Hey there! Great to hear your ODI 11g report development is on track. Let’s break down exactly how to implement that delete logic for your target table. The approach we’ll use is straightforward with an ODI Procedure, which is perfect for ad-hoc or recurring SQL operations like this.

Step 1: Create a New ODI Procedure #

1. Open ODI Studio and navigate to your project. Right-click on Procedures in the Project Explorer and select New Procedure.
2. Give your procedure a descriptive name (e.g., Purge_Last_3_Months_MYTABLE) and select your target database technology (e.g., Oracle, since your SQL uses Oracle-specific functions like ADD_MONTHS).

Step 2: Add the Delete Command #

In the Details tab of your new procedure:

1. Click Add to create a new command line.
2. Paste your delete SQL, but note that ODI uses colon-prefixed variables (:VAR_NAME) instead of # for variable substitution. Update the query to match ODI’s syntax:

   DELETE FROM MYTABLE 
   WHERE ETL_DATE IN (
       TO_DATE(:ETL_DATE, 'DDMMYYYY'), 
       ADD_MONTHS(TO_DATE(:ETL_DATE, 'DDMMYYYY'), -1), 
       ADD_MONTHS(TO_DATE(:ETL_DATE, 'DDMMYYYY'), -2)
   );
   

3. Make sure the Context and Logical Schema are set to your target environment’s settings.

Step 3: Configure the ETL_DATE Variable #

If :ETL_DATE isn’t already defined as an ODI variable:

1. Go to the Variables tab in your procedure and click Add.
2. Select the existing variable (or create a new one if needed):
   • For a project-level variable: Right-click Variables in your project > New Variable. Set its type to VARCHAR (since your format is DDMMYYYY) or DATE, and define its value (e.g., you can set it to the current date in DDMMYYYY format using TO_CHAR(SYSDATE, 'DDMMYYYY') for runtime).
3. Map the variable in your procedure to ensure ODI resolves it correctly at execution time.

Step 4: Test and Integrate the Procedure #

• Test first: Before running the delete, swap DELETE with SELECT to verify the data you’re about to remove is correct. This avoids accidental data loss:

  SELECT * FROM MYTABLE 
  WHERE ETL_DATE IN (
      TO_DATE(:ETL_DATE, 'DDMMYYYY'), 
      ADD_MONTHS(TO_DATE(:ETL_DATE, 'DDMMYYYY'), -1), 
      ADD_MONTHS(TO_DATE(:ETL_DATE, 'DDMMYYYY'), -2)
  );
  

• Execute or schedule: Once you’re confident, you can run the procedure directly by right-clicking it > Execute. To integrate it into your existing ETL workflow, add the procedure to an ODI Package or Load Plan (place it before or after your data load steps, depending on your requirements).

Key Notes #

• Ensure the ETL_DATE column in MYTABLE matches the date format you’re using (DDMMYYYY). If it’s a DATE type, the TO_DATE conversion will work; if it’s a string, double-check the format matches exactly.
• Consider adding error handling in your procedure (e.g., logging, rollback triggers) to catch any issues during execution.

内容的提问来源于stack exchange，提问作者arsnlupn