Siebel and Oracle Stuff

How to dynamically enable and disable pickmap constrain

2010-07-02T03:58:00.000-07:00

Imagine this scenario : You have a picklist that, under certain conditions, must have a constrain in the pickmap list, so what should I do to disable it when is not necessary?
Easy, read tools online help.

Pick Map UpdOnlyIfNull

Marks a copy pick map (read Pick Map) to perform only the copy operation if the field specified in the pick map updonlyifnull is null. Constrain pick maps are also copy pick maps. In cases where the constraint is active, this results in no operation (no-op). In cases where the UpdOnlyIfNull causes the constraint to be ignored, the copy operation works. To avoid the copy operation use a calculated field as the field for the constrain pick map.

EIM : Handling EIM process failures

2008-06-25T05:57:00.000-07:00

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

2008-06-06T02:02:00.000-07:00

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

2008-04-21T07:16:00.001-07:00

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

2008-04-16T01:51:00.000-07:00

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.

2008-03-03T05:56:00.000-08:00

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

2008-02-15T03:01:00.000-08:00

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

2008-02-13T05:50:00.000-08:00

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 :

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

And you are ready to go.

Oracle Sample Code Incubator

2008-02-07T00:54:00.000-08:00

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

BC : Force Active Property

2008-01-23T04:00:00.000-08:00

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 :

Every time you do a BO.GetBusComp(”Name”) in a script.
Every time you navigate in application and the underlying BC is "Name".
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

2008-01-17T00:54:00.000-08:00

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.

2008-01-14T00:49:00.000-08:00

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 :

Populate s_act_contact intersection table with the desired pair activity/contact using EIM_ACTIVITY1.
Set ACT_TARGET_PER and ACT_PR_CON (in EIM table) to 'Y'.
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.

2008-01-07T01:47:00.000-08:00

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

2008-01-03T01:07:00.000-08:00

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.

PL/SQL: Returning multiple rows into a comma separated string

2007-12-31T02:39:00.000-08:00

I've spent my last days strugling to optimize a query, during one of my multiple desperate tries, I came across a crazy idea of dinamically build my query. This try have no results, but I read this example of data retrieval, and I didn't resist to write about it. So here's the code :

with data
   as
   (
      select myvalues, row_number() over (order by myvalues) rn, count(*) over () cnt
         from
         (
            select email_addr myvalues from customers where zip = 72204
         )
   )
select ltrim(sys_connect_by_path(myvalues, ','),',') catvalues
   from data
   where rn = cnt
   start with rn = 1
   connect by prior rn = rn-1;

This sample returns a comma separated string with all email addresses whith zip code 72204.

But the most important lesson from this sample was learning about hierarchically connect data in one single query.

PL/SQL : Add_Month function

2007-12-27T06:02:00.000-08:00

Be aware of this function, it may have unexpected results :

SELECT add_months(TO_DATE('27-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('28-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('29-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('30-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('31-JAN-2007'), 1) FROM dual;
SELECT add_months(TO_DATE('01-FEV-2007'), 1) FROM dual;

This queries returns :

If you need to add a specific number of days, consider to use :

SELECT TO_DATE('01-FEV-2007') + 30 FROM dual;

Siebel Support Web Unleashed

2007-12-20T07:32:00.000-08:00

You need to search Siebel Support Web, and you don't have login nor password? Your problems end here.

But with a little workaround, you can use Supportweb for searching without having a login. How? It’s very easy. Just go to the homepage of Supportweb (which will redirect you to https://ebusiness.Siebel.com/Supportweb/index.asp) and at the login screen just press the “Login” button without filling in the fields.

As expected nothing happens and you stay on the login page. Now, type in the address bar http://Supportweb.Siebel.com and you will be able to login and search.

BC : Method SetSearchExpr overrides SetSearchSpec Results

2007-12-20T03:18:00.000-08:00

This problem came across me with a business component query. When I looked to my recorset and saw some totally unexpected results, I resolved to dig a little.As result of my digging, I've discovered that a SetSearchExpr overrides previous SetSearchSpec instructions.

oBCTVCActividades.SetViewMode(AllView);
oBCTVCActividades.ActivateField("Field1");
oBCTVCActividades.ActivateField("Field2");
oBCTVCActividades.ClearToQuery();
oBCTVCActividades.SetSearchSpec("Field1", "val1");
oBCTVCActividades.SetSearchExpr("[Field2] <> 'Value2'");
oBCTVCActividades.ExecuteQuery

will only query for results with "Field2" different from "Value2", to achieve your goal is better to use this

oBCTVCActividades.SetViewMode(AllView);
oBCTVCActividades.ActivateField("Field1");
oBCTVCActividades.ActivateField("Field2");
oBCTVCActividades.ClearToQuery();
oBCTVCActividades.SetSearchExpr("[Field2] <> 'Value2' AND [Field1] = 'val1'");
oBCTVCActividades.ExecuteQuery();

Siebel Tools : How to perform a local lock

2007-12-19T07:38:00.001-08:00

Problem: You need to change an object that is checked out by other user.
Solution : You need to locally lock the project that contains your object. So far, so good, but you have another problem,that project is locked by the user that performed check out. So all you have to do is this:

Login to your local database. Check it here.
UPDATE siebel.s_project SET locked_flg= 'N' where name=''
commit
Requery tools for your project
Lock it!

Don't forget to synchronize both projects, so you can lost all your changes.

BC : "Immediate Post Changes" field property

2007-12-14T00:35:00.000-08:00

When this property is set to True, in a business component field, the changes made to that fiel causes an imediate roundtrip, to server, for saving data.

A TRUE or FALSE value.

Field data is posted to the server when the focus moves off of the field and then the data is refreshed.

Causes an immediate roundtrip to the server. When set to True the browser script PreSetFieldValue event is bypassed. Typically used for constrained drop-down lists and calculated fields. Excessive use affects performance.

Check here for all field properties description.

Siebel Tools : How to query your local database ?

2007-12-12T06:01:00.000-08:00

You can perform some queries against your local database using an application provided with Siebel tools, this application can be found in \tools\BIN\dbisqlc.exe.

S_ACT_CONTACT : Description

2007-12-11T06:46:00.000-08:00

This table associates a contact with its activities. This relation is suported by column con_id (par_row_id from s_contact) and activity_id (activity_uid from s_evt_act).
This is a one-to-many relationship : A contact can have more than one activity associated, but a position can only be related with one contact.
Keep in mind that con_id is foreign key from s_party and activity_id from s_evt_act.

PL/SQL : How to query for & in SQL*Plus?

2007-12-10T05:39:00.000-08:00

Today, I tried to execute a simple query like :

select '&ab' from dual;

as expected I was prompted to insert value for ab...

So, to query special character & you have several options :

change DEFINE settings to allow &;

set define off;
select '&ab' from dual;

define a escape character;

set escape '\';
select '\&ab' from dual;

don't scan for substitution variables;

set scan off;
select '\&ab' from dual;

Siebel : How to disable an applet button?

2007-12-07T09:50:00.000-08:00

Just edit your applet server script and add some come for WebApplet_PreCanInvokeMethod.

For example:

function WebApplet_PreCanInvokeMethod (MethodName, &CanInvoke
{
switch (MethodName)
{
case 'Metodo1':
if (true)
{
CanInvoke = "TRUE";
}
else
{
CanInvoke = "FALSE";
}
}
}

In this case Metodo1 will be the method being invoked by the button that you want to disable.

Siebel : Oracle new forums

2007-12-03T02:06:00.000-08:00

Oracle had just created new Community Discussion Forums for Siebel specific applications.

Let's make it happen!

Siebel : IE7 compatibility

2007-12-03T01:52:00.000-08:00

Finally you can throw away your old IE 6 and install (not so new) IE7, finally Siebel fully supports it.
Here is the list of siebel version (and QuickFixes) that supports IE7 :

8.0.0.1 with QF0103
7.8.2.5 with QF0547
7.7.2.8 with QF0814
7.5.3.17

Harvest : Get last file version

2007-11-30T05:34:00.000-08:00

Imagine this situation:

You have two package P1 and P2 in the same stage, and both P1 (who is to be installed first) and P2 have an attached file F1;
In P1 the F1 file is in version 1, but in P2 is in version 3;

When I try to execute P1 and I try to get F1 file, Harvest return me F1 in version 3, when it has supposed to return version 1 of the file.

So take care when promoting 2 or more packages, to the same stage, with different version of the same file. Harvest only get the last version of it, regardless what version you included in your package.

WHy MINUS does NOT EXISTs in our queries?

2007-11-29T03:16:00.000-08:00

Read this great (and old) article about MINUS vs NOT EXISTS operators.

There really isn’t just one right way to design queries. In some cases, you really are better off using Not Exists. In many cases, however, you should use the set operator MINUS. Once you understand the principles, you can easily choose the best method for your particular case.

Joins : Please talk ANSI SQL with me...

2007-11-28T06:09:00.000-08:00

People, who had to port a Microsoft SQL Server or Access database over to Oracle, know what I'm talking about. This article show us a good example of ANSI SQL joins and also show us a few good reasons why we should use ANSI join syntax instead of classic and specific join syntax :

You don't need to specify the join condition in a natural join;
You can do more with ANSI SQL join syntax than with classic Oracle syntax;
There is no performance penalty;
In the long run you will see more of it, and less of the older syntax;
It's portable;
It's easier to understand what is happening .

Bottom line is :

So join me in creating portable SQL joins that are easy to read, do more than you can with Oracle syntax, and don't slow you down.

S_POSTN_CON : Description

2007-11-28T01:00:00.000-08:00

This table associates a contact with positions that can acess it. This relation is suported by column con_id (par_row_id from s_contact) and postn_id (row_id from s_postn).
This is a many-to-many relationship : A contact can have more than one position associated, and a position can be related with multiple contacts.
Keep in mind that con_id is foreign key from s_party.

SQL Navigator : Code Templates

2007-11-27T02:06:00.000-08:00

After trying for a while to use SQL Navigator from Quest to develope my pl/sql procedures and queries, I've found a new (for me) feature : Code templates. Tired of write allways the same kind of code structures ? Use code templates. How? It's easy. Just take a look at this help page from navigator help. (As a good developer that I am, I never read help files).

You can program Key combinations from "Ctrl+Shift+A" to "Ctrl+Shift+Z" with text up to 255 characters in length.
To program shortcut keys
From the View menu, choose Preferences.
Under the Code Editors preference heading, expand General, and then scroll down to the Templates category.
Where you see the words , click and then view or change shortcut key assignments as required.

Try it, and watch your development times drop a little bit !!!

PL/SQL : Check script for object owners

2007-11-26T06:02:00.000-08:00

In real life development environments, you develope a script logged as UserX, and someone in Support Team will install it in production environment logged as UserZ.
So far, nothing unusual happens. But, if we are talking about oracle scripts, there's something that you have to deal with, Schemas, If you didn't reference all you objects with full name, SchemaX.TableA for instance, when UserZ tries to install it, he will not find TableA in is schema.

In simple scripts, with few lines, it's easy to manually check for errors, but try to do it in a scripy with thousand of lines...

So I've developed this procedure to "parse" my code looking for this kind of errors

FUNCTION CHECK_OWNER_IN_SQL(
p_list varchar2,
p_del varchar2
) return boolean
is
l_idx pls_integer;
l_list varchar2(32767) := p_list;
l_value varchar2(32767);
keyword varchar2(10000);
existsObject integer;
result boolean;
begin
result := false;
loop
l_idx := instr(l_list,p_del);
if l_idx > 0 then
keyword := substr(l_list,1,l_idx-1);
select count(*) into existsObject from user_objects where lower(object_name) = lower(keyword);

if existsObject > 0 then
DBMS_OUTPUT.Put_Line( 'Possible error in ' keyword);
result := true;
end if;

l_list := substr(l_list,l_idx+length(p_del));
else
keyword := l_list;
select count(*) into existsObject from user_objects where lower(object_name) = lower(keyword);

if existsObject > 0 then
DBMS_OUTPUT.Put_Line( 'Possible error in ' keyword );
result := true;
end if;
exit;
end if;
end loop;
return result;
end;

--Possible values for p_type are :
-- DATABASE LINK,FUNCTION,INDEX,PACKAGE,PACKAGE BODY,PROCEDURE,SEQUENCE,SYNONYM,TABLE,TRIGGER,TYPE,VIEW

PROCEDURE CHECK_OWNER(
p_owner IN varchar2,
p_type IN varchar2,
p_name IN varchar2,
p_sql IN varchar2 := NULL
)
IS
cursor lines
is
Select text from all_source where lower(owner) = lower(p_owner) and lower(type) = lower(p_type) and lower(name) = lower(p_name) order by line;
sqltext varchar2(32000);
result boolean;
linha integer;
begin
if p_sql is not null then
result := siebel.check_owner_in_sql(p_sql , ' ');
else
linha := 1;
for line in lines loop
sqltext := TRIM(line.text);
IF substr(sqltext,1,2) <> '--' then
if siebel.check_owner_in_sql(sqltext , ' ') then
DBMS_OUTPUT.Put_Line( 'Linha ' linha ': ' sqltext);
end if;
end if;
linha := linha + 1;
end loop;
if linha = 1 then
DBMS_OUTPUT.Put_Line( 'Não foi encontrado o objecto a validar');
end if;
end if;
end;

After create this two procedures, you have only to execute the last one, and watch for possible warnings in your output window.

How to relate a contact whit their accounts

2007-11-23T05:48:00.000-08:00

Contact accounts can be found in S_PARTY_PER intersection table. This table relates person_id column (par_row_id from s_contact) with party_id column (par_row_id from s_org_ext). Both person_id and party_id are foreign keys from s_party.

Localcooling.com

2007-11-22T05:21:00.000-08:00

Download the 100% Free LocalCooling Application and it automatically optimizes your PC's power consumption by using a more effective power save mode. You will be able to see your savings in real-time translated to more environmental terms such as how many trees and gallons of oil you have saved.

Local Cooling will:
Cut your energy bills.
Reduce the amount of Greenhouse Gas CO2 emissions as a result of your reduced PC power consumption.
Give you full control over your power mode settings.
Improve your overall computing experience and efficiency.
Show you in detail how much you have saved since installing the software.
Learn more about the LocalCooling Application and how it will help you save energy!

EIM : DUP_RECORD_EXISTS after Update

2007-11-21T05:44:00.000-08:00

This status happens after running an EIM update process with lines that match exactly the lines being updated in base table.

PL/SQL : Scripting for table drop

2007-11-20T06:41:00.000-08:00

I came from MS SQLServer and I miss a lot of nice functionalities, like for instance, If exists.
Why am I talking about this? Simple, I want to run a script that "only" drops a few tables that I'don't no if they already exists.

Easy to say, hard to find. So here's a script for doing that, without returning any errors.

set echo off;
set heading off;
spool run.sql;
select 'drop table 'table_name';' from dba_tables where UPPER(table_name) in ('TABLEA', 'TABLEB', 'TABLEC');
spool off;
@run.sql;

Enjoy.

EIM : Shell execution order

2007-11-16T07:38:00.000-08:00

When running an EIM process with a shell type process section, it will be executed from top to bottom. In this example, first process to run will be Assess followed by Contact.

[Siebel Interface Manager]
PROCESS = P1
USER NAME = "XXXX"
PASSWORD = "XXXX"

[Lancar]
TYPE=SHELL
INCLUDE = "ASSESS"
INCLUDE = "CONTACT"

[CONTACT]
TYPE = DELETEBATCH = 3999
TABLE = EIM_CONTACT
ONLY BASE TABLES = S_PARTY,S_CONTACT
DELETE MATCHES = S_CONTACT, (COLUMN= 'DELETED')

[ASSESS]
TYPE = DELETE
BATCH = 3998
TABLE = EIM_ASSESS
ONLY BASE TABLES = S_ASSESS,S_ASSESS_VAL
DELETE MATCHES = S_ASSESS, (COLUMN = 'DELETED')

PL/SQL : How to update using select

2007-11-15T00:51:00.000-08:00

This "problem" came across me when I needed to update an entire temporary table column. One option is to loop all table rows, boring and time consumer task. After some background searchs I've adopted this method:

UPDATE TABLEA SET
(TABLEA.COLUMNA, TABLEA.COLUMNB) =
(SELECT TABLEB.COLUMNA, TABLEB.COLUMNB FROM TABLEB WHERE TABLEB.ID = TABLEA.ID)

I've basically do one inner select and join the two tables in the inner select's WHERE clause.

Amazing Script

2007-11-13T10:34:00.000-08:00

Script that does exactly what you are looking for.

PL/SQL : Show locked objects

2007-11-13T10:23:00.000-08:00

Today, after trying unsuccessfully to drop my temporary tables, and subsequent fails due to object locks, I tried to find who's locking my table.
Not an easy task, so I dig a little and found this amazing script that solved my problem. If you have the same problem, read here how to list all locked objects.

PL/SQL Tip #1

2007-11-07T06:31:00.000-08:00

Query v$parameter view for BD parameters checking.
In this example I'm looking for the selected optimizer mode.

select value from v$parameter where name = 'optimizer_mode';

Enjoy.

Oracle : Reducing join execution time

2007-11-07T05:45:00.000-08:00

After executing a join over two tables, whith 4 million rows each and indexes in join columns, I realized that 4 minutes it's too much time waiting for results. So I decided to watch for the execution plan :

select columns from tableA join TableB on tableA.id = tableB.id

Execution Plan----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE
1 0 FILTER
2 1 SORT (GROUP BY)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'TABLEA'
4 3 NESTED LOOPS
5 4 TABLE ACCESS (FULL) OF 'TABLEB'
6 4 INDEX (RANGE SCAN) OF 'TABLEA_U1' (UNIQUE)

All seems to be correct, after all I'm using Rule Base Optimizer. In order to reduce execution time I force the execution of an hash join instead of a Nested Loops.
Execution time droped to about 2 minutes and execution plan looks like this :

Execution Plan----------------------------------------------------------
0 SELECT STATEMENT Optimizer=RULE (Cost=624 Card=40000 Bytes=2 800000)
1 0 FILTER
2 1 SORT (GROUP BY) (Cost=624 Card=40000 Bytes=2800000)
3 2 HASH JOIN (Cost=172 Card=40000 Bytes=2800000)
4 3 TABLE ACCESS (FULL) OF 'TABLEA' (Cost=35 Card=2000 Bytes=62000)
5 3 TABLE ACCESS (FULL) OF 'TABLEB' (Cost=136 Card=2000 Bytes=78000)

And all I've to do is change my query to :

select /*+ USE_HASH (tableA tableB ) */ columns from tableA join TableB on tableA.id = tableB.id

Why is this happening? why RBO this choose this plan? The answer is simple, RBO doesn't consider hash joins has a valid execution path, so you have to force it.
This specific join is used when a large amount of data needs to be joined or when a large fraction of the the table needs to be joined, however this is a very memory expensive operation and need to be carefully analised.

Last but not least, if you want that hash joins were a valid option just start using Cost Base Optimizer. If you don't decide yet what join you should use, just read this article.

SQL * Plus Tip #1

2007-11-07T05:37:00.000-08:00

To change the previous query, just type EDIT, this command will redirect you into an editor so you can edit previous SQL command, short name : ED.

Then type / and the new SQL command will be executed

Oracle : SQLCODE and SQLERRM in Inserts

2007-11-06T09:28:00.000-08:00

Today I came across an unexpected problem when executing an exception block inside a procedure :

My code was something like this :

insert into dc_errors (error_message,error_date) values (SQLERRM, SYSDATE);

and I allways obtain an annoying PL/SQL: ORA-00984: column not allowed here.

To solve this problem I've changed my code into :

v_exception := substr(SQLERRM, 1, 256);
insert into dc_errors (error_message,error_date) values (v_exception, SYSDATE);

with v_exception as varchar2(256) .

The greatest invention since the wheel

2007-11-05T05:25:00.000-08:00

Today, after spend some time reading a friend's blog, I realize that Not Working Bar will bring a revolution to our work environments. Read more about it here.

Thanks for the tip Mário.

Oracle : Reduce your parsing times

2007-11-05T02:44:00.000-08:00

All the queries that you execute in an Oracle database are parsed and then executed. However, is a nonsense(and a time wastefulness) to parse over and over the same query, even if executed by different users.

So, to avoid this, Oracle uses a 'Shared pool' area where all the cursors are cached after being parsed.

How it works? It's easy:

Your query is hashed;
Oracle searchs shared pool for the matching hash value;
Is it there? If so, execute the cursor;
Otherwise parse your query, hash it and put it in shared pool for future executions;

To take advantage of this feature of Oracle engine, we have to take care for:

Hashing is executed over all your query, so Case is important.

Select * from emp is different from Select * from EMP and therefore you will not take advantage of shared pool in your second execution; So, it's important that your developers team agree in Naming and Case conventions to take greater advantage of Oracle Shared Pool.

Where clauses are hashed too;

Select * from emp where emp_no = 1 is different from Select * from emp where emp_no = 2, use global variables and procedures whenever you can.

If you follow this rules, you will not solve all the performance problems from your application but will give a little step toward the perfection.

How to deploy files in a remote server?

2007-11-02T02:22:00.000-07:00

It's easy you say, just share your drives when you connect through rdp. Ok, let's talk about worse case scenario, you can't share drives and you can even share a drive from your remote server.
So you can use a very usefull tool named WordPad. Surprised ?
It's even easier than sharing you drives, watch it :

Zip all your files into a single file;
Open a wordpad in your local machine;
Copy zip file into wordpad (WordPad would serialize your file);
Copy object that appears inside Wordpad;
Open wordpad in your remote machine and paste your object into it;
Finally copy your recent pasted object into remote server.

Enjoy.

Error 452: Match column X not found in Y

2007-10-31T04:27:00.000-07:00

Today I had to create a new column in EIM_CONTACT table, to build a new attribute mapping for deletion purposes. All went fine until I test my EIM task.

Error 452: Match column X_COLUMN not found in S_CONTACT.

I run trough all my steps to find what's wrong in it. All seems to be in perfect shape. My next step was to loose some time reading bookshelf and Siebel support SR's. Nothing came across that helped me to solve my problem.

Last step in problem resolution algorithm, ask for help. Allways work!!

My colleague João had the solution, Siebel caches EIM mappings in \siebsrvr\BIN\diccache.dat, all we have to do is delete this file and Siebel will build a new one for you (with your new mappings).

Finally, I successfully ran my EIM process.

Debugging in http://localhost:8082

2007-10-30T04:36:00.000-07:00

Today I've made my first siebel project compilation, all my environment is brand new and well configured.
I've enabled 'Auto-start web client' feature and hit the road. IExplorer browses http://localhost:8082/ and inherent error. Why in God's name is this happening?

The answer is simple, (If my friend Alexandre didn't help me, it wouldn't be so simple), my Firewall is enabled.

After I disabled that, my debug can go on!!