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

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

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 :

27-02-2007
28-02-2007
28-02-2007
28-02-2007
28-02-2007
01-03-2007

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

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

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.

So

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

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

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 ?

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

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?

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?

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

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

Let's make it happen!

Siebel : IE7 compatibility

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 :

1. 8.0.0.1 with QF0103
2. 7.8.2.5 with QF0547
3. 7.7.2.8 with QF0814
4. 7.5.3.17