ORA-01403: no data found

Pretty common oracle error. Raised when you are trying to fetch data from sql into a pl/sl variable and the sql does not return any data.

>> Using the data from this schema

  FROM scott_emp
  WHERE empno = 9999;
   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
ERROR at line 1:
ORA-01403: no DATA found
ORA-06512: at line 5

What to do next

1. Re-Raise it with a error message that provides more context.

   l_ename scott_emp.ename%TYPE;
   l_empno scott_emp.empno%TYPE := 9999;
   SELECT ename
     INTO l_ename
     FROM scott_emp
     WHERE empno = l_empno;
  WHEN no_data_found
   THEN raise_application_error(-20001,'No employee exists with employee id ' || l_empno);
ERROR at line 1:
ORA-20001: No employee EXISTS WITH employee id 9999
ORA-06512: at line 11

2. Suppress the error if this is a valid business scenario and do the necessary processing.
Example CASE : If a user has a preference to display the numbers in local currency, convert the amount, else, display in USD.

CREATE OR REPLACE PROCEDURE p_calc_sales_metrics(
   p_user_id IN users.user_id%TYPE,
   p_profit  IN net_sales.profit%TYPE
) AS
  l_pref_currency user_prefs.pref_currency%TYPE;
  l_profit_local_amt net_sales.profit%TYPE;
---other code
	SELECT pref_currency
	  INTO l_pref_currency
	 WHERE user_id = p_user_id
        l_cur_conv_factor := get_conv_rate('USD',l_pref_currency);
	  WHEN no_data_found 
	   THEN l_cur_conv_factor := 1;
--- other code..
 l_profit_local_amt :=  p_profit * l_cur_conv_factor;

3. Functions, by design, do not raise the NO_DATA_FOUND exception, instead they return null to the calling program.

   i_empno IN scott_emp.empno%TYPE
) RETURN scott_emp.ename%TYPE
  l_ename scott_emp.ename%TYPE;
  SELECT ename
    INTO l_ename
    FROM scott_emp
   WHERE empno = i_empno;
  RETURN l_ename;  
SQL> SELECT f_get_ename(7839) FROM dual;
SQL>  SELECT f_get_ename(9999) FROM dual;
SQL> SELECT nvl(f_get_ename(9999),'NULL RETURNED') FROM dual;
Posted in Oracle errors | Leave a comment

Extending functionality using Command Tasks : Informatica.

Martin posted an interesting question in the comments section here. It belongs to a common class of questions about how we can add abilities such as file handling, processing, and similar tasks in informatica, so I decided to extend the discussion into a post in itself.

This post explains how you can use Informatica’s command task in such scenarios. You can see another example here, where we enhance the event wait functionality to answer better queries and reduce maintenance.

Just to be clear, I’ll repeat the requirement..

Everyday before the workflow runs, check the two files “file1″ and “file2″ for differences. If they differ, the workflow should fail and send a failure email. Otherwise, it should continue with the rest of the workflow.

Pretty simple task for the Unix compare command. Here is the shell script that implements the above requirement, and writes details to the workflow log file. (they will not be visible from the workflow monitor ,but if you check the actual workflow log on the file system, you’ll be able to see the messages).

# Script Name : file_diff.ksh                                   #
# Function    : Checks for differences betweek file1 and file2  #
# Output      : returns -1 if the files differ and 0 otherwise  #
  echo "INFO : EXTERNAL : [" `date "+%a %b %d %H:%M:%S %Y"` "]" $1 >> $LogFile
log "Executing script file_diff.ksh.."
log "Checking difference between $FileDir/file1 and $FileDir/file2"
if cmp -s $file1 file2; then
   log "File 1 and File2 match. Exiting command task successfully."
   log "file 1 and file2 are different . Diff file exists, failing command task.."
log "ending file diff script."
return $retCode

The second part involves calling this script from Informatica and making sure the workflow fails when we see a diff in the files.
I’m going to extend the workflow I set up in the email task example. Here is how the initial workflow looks.

Initial workflow before adding the command task

Initial workflow before adding the command task

Steps to add command Task :

1. In workflow Manager, open the folder in which your workflow is present and then open the workflow.
2. Once you are in the Workflow Designer tab, click on Task > Create task from the menu bar.
3. Select “command Task” from the drop down and name it “cmd_check_file_diffs”. Click create and done.
4. Double click on the created tasks and set the following properties.

General Tab : check “Fail parent if this task fails”.
Properties Tab : check “Fail task if any command fails”, Recovery Strategy : “Restart task”.
Command Task : Add a new command and name it “Check_File_Diffs”. In the command section enter the unix command with the full path name.

Adding script to command task

Adding script to command task

Connect the newly created command task to the rest of the workflow and add the link conditions as needed.

Adding command task in the workflow

Adding command task in the workflow

Once this is done, I added two following files to simulate a difference in the two files.

infra@ DEV stcdwhdd /> cat file1
line1 -> common in both files
iine2 -> present in file1 alone
infra@ DEV stcdwhdd /> cat file2
line1 -> common in both files

Run the workflow. Here is the content from the workflow log when I look at it in the file system.

INFO : LM_36435 [Wed Dec 21 11:28:10 2011] : (14967|34) Starting execution of workflow [wkf_Test] in folder [rchamarthi] last saved by user [rchamarthi].
INFO : LM_44195 [Wed Dec 21 11:28:10 2011] : (14967|34) Workflow [wkf_Test] service level [SLPriority:5,SLDispatchWaitTime:1800].
INFO : LM_36330 [Wed Dec 21 11:28:10 2011] : (14967|34) Start task instance [Start]: Execution started.
INFO : LM_36318 [Wed Dec 21 11:28:10 2011] : (14967|34) Start task instance [Start]: Execution succeeded.
INFO : LM_36505 : (14967|34) Link [Start --> cmd_check_file_diffs]: empty expression string, evaluated to TRUE.
INFO : LM_36330 [Wed Dec 21 11:28:10 2011] : (14967|34) Command task instance [cmd_check_file_diffs]: Execution started.
INFO : LM_36620 : (14967|34) Command task instance [cmd_check_file_diffs]: running command [check_file_diffs], with command value [sh -c "sh /home/infra/rchamarthi/file_diff.ksh"].
INFO : LM_36621 : (14967|30) Command task instance [cmd_check_file_diffs]: started command [check_file_diffs], with pid [3795] on node [node01_stcdwhddb01].
INFO : EXTERNAL : [ Wed Dec 21 11:28:10 2011 ] Executing script file_diff.ksh..
INFO : EXTERNAL : [ Wed Dec 21 11:28:10 2011 ] Checking difference between /home/infra/rchamarthi/file1 and /home/infra/rchamarthi/file2
INFO : EXTERNAL : [ Wed Dec 21 11:28:11 2011 ] file 1 and file2 are different . Diff file exists, failing command task..
ERROR : LM_36623 : (14967|34) Command task instance [cmd_check_file_diffs]: execution of command [check_file_diffs] did not complete successfully with exit code [65280]
WARNING : LM_36626 : (14967|34) Command task instance [cmd_check_file_diffs]: previous command [check_file_diffs] failed and "run if previous command succeeded" option is set, the remaining commands will not be run.
ERROR : LM_36320 [Wed Dec 21 11:28:12 2011] : (14967|34) Command task instance [cmd_check_file_diffs]: Execution failed.
WARNING : LM_36373 : (14967|34) Workflow [wkf_Test] is suspended.
INFO : LM_36375 : (14967|34) Workflow [wkf_Test]: Sent suspension email to email user [rajesh@etl-developer.com].

As you can see, the following lines have been added to the log as part of the shell script.

INFO : EXTERNAL : Executing script file_diff.ksh..
INFO : EXTERNAL : Checking difference between /home/infra/rchamarthi/file1 and /home/infra/rchamarthi/file2
INFO : EXTERNAL : file 1 and file2 are different . Diff file exists, failing command task..

To test the case where the files match, I copied file1 to file2 in Unix.
In this case, the contents in the workflow log look like this.. (and the workflow proceeds to the next task ).

INFO : EXTERNAL : Executing script file_diff.ksh..
INFO : EXTERNAL : Checking difference between /home/infra/rchamarthi/file1 and /home/infra/rchamarthi/file2
INFO : EXTERNAL : file 1 and file2 are different . Diff file exists, failing command task..

I haven’t found any APIs in the informatica documentation that let the developer add INFO/WARNING/MESSAGES from shell scripting. The messsages I showed above are only available on the file system and cannot be viewed when you see the log from workflow monitor. You can add INFO messages from Java transformations using the log() command, but adding them from an external script doesn’t seem possible. If you know of any way to do that, please post it in the comments.

Finally, keep in mind that this is a task that you are developing outside Informatica. You must ensure you do good exception handling and logging in your shell script and test it out correctly. Obviously, Informatica will not be able to help you there :)

Posted in Informatica | Tagged , , , | 5 Comments

Email task, Session and Workflow notification : Informatica

One of the advantages of using ETL Tools is that functionality such as monitoring, logging and notification are either built-in or very easy to incorporate into your ETL with minimal coding. This Post explains the Email task, which is part of Notification framework in Informatica. I have added some guidelines at the end on a few standard practices when using email tasks and the reasons behind them.

1. Workflow and session details.
2. Creating the Email Task (Re-usable)
3. Adding Email task to sessions
4. Adding Email Task at the Workflow Level
5. Emails in the Parameter file (Better maintenance, Good design).
6. Standard (Good) Practices
7. Common issues/Questions

1. Workflow and session details.

Here is the sample workflow that I am using. The workflow (wkf_Test) has 2 sessions.

s_m_T1 : Loads data from Source to Staging table (T1).
s_m_T2 : Loads data from Staging (T1) to Target (T2).

The actual mappings are almost irrevant for this example, but we need atleast two sessions to illustrate the different scenarios possible.

Workflow Test with the two sessions.

Test Workflow (2 Sessions)

2. Creating the Email Task (Re-usable)

Why re-usable?. Becuase we’d be using the same email task for all the sessions in this workflow.

1. Go to Workflow Manager and connect to the repository and the folder in which your workflow is present.
2. Go to the Workflow Designer Tab.
3. Click on Workflow > edit (from the Menu ) and create a workflow variable as below (to hold the failure email address).

Failure Email workflow variable

Failure Email workflow variable

4. Go to the “Task Developer” Tab and click create from the menu.
5. Select “Email Task”, enter “Email_Wkf_Test_Failure” for the name (since this email task is for different sessions in wkf_test).
Click “Create” and then “Done”. Save changes (Repository -> Save or the good old ctrl+S).
6. Double click on the Email Task and enter the following details in the properties tab.

Email User Name : $$FailureEmail   (Replace the pre-populated session variable $PMFailureUser, 
                                    since we be setting this for each workflow as needed).
Email subject   : Informatica workflow ** WKF_TEST **  failure notification.
Email text      : (see below. Note that the server varibles might be disabled, but will be available during run time).
Please see the attched log for Details. Contact ETL_RUN_AND_SUPPORT@XYZ.COM for further information.
Folder : %n
Workflow : wkf_test
Session : %s
Create Email Task


3. Adding Email task to sessions

7. Go to the Workflow Tab and double click on session s_m_T1. You should see the “edit task” window.
8. Make sure you have “Fail parent if this task fails” in the general tab and the “stop on errors” is 1 on the config tab.
Go to “Components” tab.
9. For the on-failure email section, select “reusable” for type and click the LOV on Value.
10. Select the email task that we just created (Email_Wkf_Test_Failure), and click OK.

Adding Email Task to a session

Adding Email Task to a session

4. Adding Email Task at the Workflow Level

Workflow-level failure/suspension email.

If you are already implementing the failure email for each session (and getting the session log for the failed session), then you should consider just suspending the workflow. If you don’t need session level details, using the workflow suspension email makes sense.

There are two settings you need to set for Failure notification emails at workflow level.
a) Suspend on error (Check)
b) Suspension email (Select the email task as before). Again, remember that if you have both session and workflow level emails, you’ll get two emails, if a session fails and causes the parent to fail.

Informatica workflow suspension email

Informatica workflow suspension email

Workflow Sucesss email

In some cases, you might have a requirement to add a success email once the entire workflow is complete.
This helps people know the workflow status for the day without having to access workflow monitor or asking run teams for the status each day. This is particularly helpful for business teams who are more concerned whether the process completed for the day.

1) Go to the workflow tab in workflow manager and click Task > Create > Email Task.
2) Enter the name of the email task and click OK.
3) In the general tab, select “Fail parent if this task fails”. In the properties tab, add the necessary details
Note that the variables are not available anymore, since they are only applicable at the session level.
4) Add the necessary Session.status=”succeedeed” for all the preceding tasks.

Here’s how your final workflow will look.

Success Emails

Informatica success emails

5. Emails in the Parameter file (Better maintenance, Good design).

We’ve created the workflow variable $$FailureEmail and used it in the email task. But how and when is the value assigned?
You can manage the failure emails by assigning the value in the parameter file.
Here is my parameter file for this example. You can seperate multiple emails using comma.

infa@ DEV /> cat wkf_test.param

While it might look like a simpler approach initially, hard-coding emails IDs in the email task is a bad idea. Here’s why.

Like every other development cycle, Informatica ETLs go thorugh Dev, QA and Prod and the failure email for each of the environment will be different. When you promote components from Dev to QA and then to Prod, everything from Mapping to Session to Workflow should be identical in all environments. Anything that changes or might change should be handled using parameter files (similar to env files in Unix). This also works the other way around. When you copy a workflow from Production to Development and try to make changes, the failure emails will not go to business users or QA teams as the development parameter file only has the developer email Ids.

If you use parameter files, here is how it would be set up in different environments once.
After the initial set up, you’ll hardly change it in QA and Prod and migrations will never screw this up.

In development   : $$FailureEmail=developer1@xyz.com,developer2@xyz.com"
In QA / Testing  : $$FailureEmail=r=developer1@xyz.com,developer2@xyz.com,QA_TEAM@xyz.com
In Production    : $$FailureEmail=IT_OPERATIONS@xyz.com,ETL_RUN@xyz.com,BI_USERS@xyz.com

6. Standard (Good) Practices

These are some of the standard practices related to Email Tasks that I would recommend. The reasons have been explained above.

a) Reusable email task that is used by all sessions in the workflow.
b) Suspend on error set at the workflow level and failure email specified for each session.
c) Fail parent if this task fails (might not be applicable in 100% of the cases).
c) Workflow Success email (based on requirement).
d) Emails mentioned only in the parameter file. (No Hard-coding).

7. Common issues/Questions

Warning unused variable $$FailureEmail and/or No failure emails:

Make sure you use the double dollar sign, as all user-defined variables should. (unless you are just using the integration service variable $PMFailureEmailUser). Once that is done, the reason for the above warning and/or no failure email could be…
a) You forgot to declare the workflow variable as described in step 3 above or
b) the workflow parameter file is not being read correctly. (wrong path, no read permissions, invalid parameter file entry etc.)
Once you fix these two, you should be able to see the success and failure emails as expected.

Posted in 8.6.1, Informatica | Tagged , , , , , | 2 Comments

(When to use ) Unconnected LookUp in Informatica.

One of the readers posted the following question, which is another frequently asked Question in Informatica

What is the absolute necessity of Unconnected lookup, if same functinality can be achived by Connected Lookup Transformation. Please post some real time situations where we decide either to use Connected/Unconnected Lookup Tranformation.

The main advantage of using an unconnected lookup is the fact that you can use it in an expression/any other transformation like a “function” in other programming languages. The Lookup Cache is calculated only once as part of the session initialization and hence more efficient, since all subsequent look-ups will re-use the Cache.

The only constraint is that the cache has to be static, i.e the data in your look-up table shouldn’t be changing once the lookup Cache has been created. Actually, it could change, but the Cache wouldn’t be updated. So the results might be incorrect.

This narrows down the usage of Unconnected Lookup to the following scenarios.
a) When you are looking up against a Static dimension (or any table) that is rarely ever updated
b) When you are looking up against a Base Dimension that is loaded before any of the Facts are loaded
c) The logic used in the Lookup-override is required at a lot of other places.

a) When you are looking up against a Static dimension (or any table) that is rarely ever updated

The most common example for this is the Date Dimension. Usually loaded once when your Data Warehouse goes to Production and very rarely updated.What makes it even more appealing to use unconnected lookup for Date Dimension is the Date Dimension Role Playing.
Example, An online order can have Order Date, Ship Date, Cancelled Date, Recieved Date and so on and the same Date Dimension table plays multiple roles.
Without an unconnected Lookup, here’s how your mapping would look..Also note that the Lookup Is being done on the same table internally (DATE_DIM), but the cache is being calculated one for each lookup.

Using an unconnected Lookup For Date Dimension, this is how it would be transformed..

As you can see, this promotes greater reuse ,a less complex mapping and is more efficient becuase of lesser Caching.

b) When you are looking up against a Base/Conformed Dimension that is loaded before any of the Facts are loaded.

Another example (one which is not static Data) is looking up against any customer Master Data/Conformed Dimension. Your DataWarehouse can have many Confirmed Dimensions, which are loaded before any of the Fact Loads.
One such example is the Employee Dimension. You can lookup the employee key multiple times in a given mapping and this makes a great candidate for Unconnected Lookup.

c) The logic used in the Lookup-override is required at a lot of other places.

This is more of a good coding practise than an Informatica specific “tip”. If your lookup involves a lookup override and you calculate the actual fact using a formula instead of using a direct column value, it would make sense to get the result using an unconnected lookup and use it at multiple places using a :LKP expression.

One such example is when you want to Lookup Revenue based on the Status of the Order instead of a direct Lookup, and you have a look-up override like the one below.

SELECT order_id,
       (CASE WHEN order_status = 'Pending' THEN revenue*0.7
            WHEN order_status = 'COnfirmed' THEN revenue
            WHEN order_status = 'Cancelled' THEN -1*revenue
        END) revenue           
  FROM order_revenue

So if your lookup data is constant during your load and you have a good case for reuse, Unconnected Lookup can be very useful, both in terms of maintainability and efficiency.

Posted in Informatica | 22 Comments

Informatica Workflow Successful : No Data in target !

This is a frequently asked in the Informatica forums and the solution is usually pretty simple. However, that will have to wait till the end because there is one important thing that you should know before you go ahead and fix the problem.

Your workflow should have failed in the first place. If this was in Production, Support Teams should know that something Failed. Report users should know the data in the Marts is not ready for reporting. Dependent workflows should wait until this is resolved. This coding practice basically violates the Age-old Principle of fail-fast when something goes wrong, instead of continuing flawed execution pretending “All is well”, causing the toughest-to-debug defects.

Of Course, this is not specific to Informatica. It is not uncommon to see code in other languages which follows this pattern. The only issue that is specific to Informatica is that this is the default behavior when you create a session. So you might have this “bug” in your code without even knowing it.

Stop On Errors:

Indicates how many non-fatal errors the Integration Service can encounter before it stops the session. Non-fatal errors include reader, writer, and DTM errors. Enter the number of non-fatal errors you want to allow before stopping the session. The Integration Service maintains an independent error count for each source, target, and transformation. If you specify 0, non-fatal errors do not cause the session to stop.
Optionally use the $PMSessionErrorThreshold service variable to stop on the configured number of errors for the Integration Service.

In Oracle, it is the infamous “when others then null” .

  <process SOME Data>
   WHEN others 
       THEN NULL;  

In Java..Something like..

try {
catch ( Exception e ) {
   // do nothing

The solution to this problem in Informatica is to set a limit on the number of allowed errors for a given session using one of the following methods.
a) Having “1″ in your default session config : Fail the session on the first non-fatal error.
b) Over-write the session Configuration details and enter the “Stop On Errors” to “1″ or a fixed number.
c) Use the $PMSessionErrorThreshold variable and set it at the integration service level. You can always override the variable in the parameter file. Take a look at this Article on how you can do that.

Remember, if your sessions do not belong to one of these categories, you are doing it wrong!.
a) Your session Fails and Causes the workflow to fail whenever any errors occur.
b) You allow the session to continue despite some (expected) errors, but you always send the .bad file and the log file to the support/business team in charge.

Why is there no data in Target

The solution to “why the records didn’t make it to the target” is usually pretty evident in the session log file. The usual case (based on most of the times this question is asked) is becuase all of your records are failing with some non-fatal error.

The only point of this article is to remind you that your code has to notify the right people when the workflow did not run as planned.


Posted in Coding Practices, Informatica | 13 Comments