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.