EIM : Handling EIM process failures

Imagine this scenario, you are deleting a large number of contacts and you process aborts somewhere during the process. If you are using default parameters in process IFB, you can be in a lot of trouble. Why? EIM have this 3 parameters

COMMIT EACH PASS = TRUE
COMMIT EACH TABLE = TRUE
ROLLBACK ON ERROR = FALSE

whith this defaults, this means that after deleting a table a commit is performed. And no rollback is performed. this can leave you with "orphan" records.
To avoid this, just change your parameters to

COMMIT EACH PASS = FALSE
COMMIT EACH TABLE = FALSE
ROLLBACK ON ERROR = TRUE

This ensures that EIM will either complete sucessfully or rollback the batch.

Just one note, be sure that your database resources are large enough to handle this transaction.

PL\SQL : Copy tables between databases

As you noticed, my posts run around my daily problems, and this isn't an exception. Past week I needed to copy large amount of data between production and development database to perform some tests. After some research I've found SQL*Plus COPY command, that fits like a glove in my needs.
So here's the syntax:

COPY FROM database TO database action
destination_table (column_name, column_name...) USING query

where action stands for:

• create - if destination table doesn't exist yet;
• replace - if destination table exists and we wish to drop and create it again;
• append - Inserts data if table exist, otherwise it will be created;
• insert - Insert data into an existing table.

An example could be :

SQL>copy from user/pass@DB1 
   to user2/pass2@DB2
   append new_emp
   using select * from emp;

Enjoy!

SBL-EAI-04375 - EAI upsert error

Past week I came across an annoying error, lets supose that I have an integration object that generates a schema like this:

   <name>Account Name</name>
   <competitorlist>
      <competitor>
         <name>Name 1</name>
         <type>TYPE</type>
      </competitor>
   </competitorlist>

When I used EAI to upsert this values, it works fine in first execution, but fails in next executions (with same data)
with this error :

"Method 'WriteRecord' of business component 'BC' (integration component 'BC') for record with search specification '[Name]="TYPE" AND [Type]="Name 1"' returned the following error:"The same values for 'Name, Type' already exist.
If you would like to enter a new record, please ensure that the field values are unique.(SBL-DAT-00382)"(SBL-EAI-04375)
--
Error invoking service 'EAI Siebel Adapter',

So, let me explain all scenario for a better explanation:

• Table that supports competitor list have a key for name and type for each account
• BC that supports this IO have a MVG declaration with Type Field = Type and Type Value = TYPE
• By mistake values in CompetitorList came switched...
  

   <name>Account Name</name>
   <competitorlist>
      <competitor>
         <name>TYPE</name>
         <type>Name 1</type>
      </competitor>
   </competitorlist>

So what's really happening? Why first execution works fine (even with switched values)? After strugling with it for a day
I found the answer in siebel sql trace, When EAI process is trying to decide if is an update or an insert in Competitors table, is used MVG restriction to perform the
query, so, because values are switched EAI is looking for a register with type = TYPE, which wasn't found because we are inserting a record with type= Name 1, then when EAI
didn't found any record it will try to insert a new one with values that already exists in db causing this error to happen.

This is a pretty strange situation, that causes a pretty confuse explanation.
So, bottom line is : If any similiar situation happens to you, look in siebel logs for the query that is causing it.

Using BS simulator

Today, I've lost 4 hours trying to understand an unexpectable error when testing a BS Method using BS simulator, so here's the story.
All my executin ends in:

An error has ocurred in business component "Service Request" creation used by business object "Business Service"....

After struigling with it, and try to discover where I've used that BS, because I don't remember to use it, I've discover (with a little help from Gonçalo) this piece of code :

   activeBO = TheApplication().ActiveBusObject();
   bcSR = activeBO.GetBusComp("Service Request");

So I've solved the mistery, "Business service" is the ActiveBusObject when you instantiate a method from BS simulator, and that's causing the unexpected error.

Thanks Gonçalo, without your help I surely can't solve this out.

ORA-01445: cannot select ROWID from a join view wihtout a key-preserved table.

Today, I was building an huge query, after adding another branche, the query returned me this strange error :

ORA-01445: cannot select ROWID from a join view wihtout a key-preserved table.

My query was something like this :

select * from tableA a join tableB b on (a.column = b.column)

No row_ids...

After struggling with it for a while, I search metalink, and after all it was Oracle thats causing my problems.... Oracle(9) has a limit of 1050 columns in any query that uses ANSI joins. So I've changed my query to :

select * from tableA a join (select column from tableB) b on (a.column = b.column)

This way I've reduced my query colums and my problem was solved.

Applet : Fast button enable

Today, I came across this great tip from Siebel Unleashed, in how to enable an applet button;

If you create a button and give method name as CopyScript then change it to EventMethodCopyScript it will automatically enable the button. But you need to change the method name at BC level too. So at BC Level the scripting will change to

if(MethodName == “EventMethodCopyScript”)

Enjoy!

SQLNavigator : Toolbars have disappeared

Today, I had to query my Database, start SQL Navigator instance and .... Surprise!!
All my toolbars have disappeared. Happily, I've only to delete one registry folder to solve my problem.
If this happen, all you have to do is :

1. Close all your sql navigator running instances;
2. Open registry; (Click run and type regedit)
3. Go to : HKEY_CURRENT_USER\Software\Quest Software\SQL Navigator
4. Delete Main_Form folder

And you are ready to go.

Oracle Sample Code Incubator

New Oracle wiki, you can try it here. Let's share our common, or not so common, daily routines.

BC : Force Active Property

This business component property is more powerfull than most people imagine. Why?
It's simple. If used unnecessary it can decrease application performance.
When you set this property to TRUE, it means that this fiels is queried every time business component is instantiated :

1. Every time you do a BO.GetBusComp(”Name”) in a script.
2. Every time you navigate in application and the underlying BC is "Name".
3. BC is accessed internally in any form.

Why?

Every time you make a field Force active it will be made part of where clause each time a query is issued against that BC or table irrespective of the fact that field is required or not.

You can read detailed information here

EIM : Session SQL

When using EIM to delete,update or insert data, you can perform some post execution operations. Just use SESSION SQL keyword in your IFB, this allow you to execute a query after EIM section ends is execution.
You can read more about it here.

EIM : How to populate S_EVT_ACT contact columns.

This was my last task, populate primary contact columns (target_per_id and pr_con_id) in activities table (s_evt_act). So, I start looking for some eim table to do my job, and after looking my entire references I didn't find one.
And why didn't I found one? Because, there are no explicit way to do it. So what you have to do is :

1. Populate s_act_contact intersection table with the desired pair activity/contact using EIM_ACTIVITY1.
2. Set ACT_TARGET_PER and ACT_PR_CON (in EIM table) to 'Y'.
3. Run EIM task.

Internally EIM will implicitly fill s_org_ext columns for you. Below you can find an ifb excerpt that do what we desire.

[Update Activities]TYPE = IMPORT
BATCH = 4511
TABLE = EIM_ACTIVITY1
ONLY BASE TABLES =  S_EVT_ACT,S_ACT_CONTACT

Get ready for Windows Server 2008.

I know this is a little bit off topic, but it's hard to forget our past.
So that's it, Microsoft is offering some free material regarding Windows Server 2008. Check it here.

Hurry up, this offer is limited in time.

BC : How to make a read-only field

Just create a business component user prop with the following values :

• Name : Field Read Only Field: Field Name
• Value : 'N' or 'Y'

If this field changes from read-only to writable depending on other data, just build a calculated field that reproduces the desired behaviour and assign it to the user prop value.