Siebel Integration Basics
Posted by: eserhan on: November 1, 2009
- In: Integration | Siebel
- Leave a Comment
Here is a good site to understand Siebel Integration with posts, quizes, videos..
http://www.siebelintegration.com/tutorials/
Cascade Delete Property for Link
Posted by: eserhan on: October 31, 2009
- In: Integration | Siebel
- Leave a Comment
This property is to clear the relation or unmapped child date to be removed from the DB. Cascade Delete has 3 types of values. 1) None 2) Clear 3) Delete
1) None: When we are deleting the Parent record if we don’t want to untouch the child record we will set the property as “None” for the Cascade Delete.
2) Clear: If this property selected the relation ship b/w the parent and the child will be cleared but the child record will not be deleted. This will be useful when the child is the part of any other join.
3) Delete: This will delete the child record along with the parent record.
Siebel Logs
Posted by: eserhan on: October 31, 2009
- In: Siebel | Siebel Hints
- 1 Comment
Here is a useful post to manage Siebel Logs;
“If you have worked on Siebel CRM I am sure that you have heard the term LOGS lot of times. They are one of the most important and useful things if you are a Siebel Developer. But still most of us actually know how to use them to our advantage or to reduce our development time. Here in this Article I will try to explain some tips and tricks to get the logs and debug our code. Now we all know that we have two types of Siebel Application Clients.
- Dedicated Client
- Web Client
Siebel Web Client : We will talk first about the Siebel Web Client. Now to get logs from Siebel Web Client there is no easy way and the only way to get logs is to increase the log level at server for “Object Manager” or particular component that you want to get logs of for example Workflow Process Manager. I will talk detail about the Web Client Logging techniques in my next few posts.
In this article I am going to discuss ways which can help us get logs from dedicated clients and speed up our debugging and development time.
Dedicated Client : There are essentialy two ways to get logs from dedicated client
- Spool
- Environmental Variable
Spool : it is a very basic and known technique to get the spool of all the SQL Queries that are fired in the database level and we all come across it during our initial Siebel development days. We can enable the spool for Siebel Client as well as Siebel Tools. The steps to enable spool for both the Siebl tools and client there is common process which is as following
- Right click the siebel tools or Siebel Client Shortcut
- Go to end of String which is in Target Text Field
- Enter the Following string at the end: /s “c:\spool.txt”
/s switch here stands for Spool. Various other switches that can be used are as following
- /u : username
- /p : password
- /d : database
With the following string you can automate login to your local or sample database depeding on the value you give in the switch. I am providing you examples of both
/u sadmin /p sadmin /d sample : String to login to Sample database with Sadmin user id
/u user /p pwd /d local : String to login to Local database with “user” user id.
Environmental Variable : This is more powerful and less know method of generating logs for dedicated client. Siebel has provided couple of Environmental Variables which are as following :
- SIEBEL_LOG_EVENTS
- SIEBEL_LOG_DIR
SIEBEL_LOG_EVENTS : This environmental variable can have two types of values.
Numeric or text. The numeric value is the log level which can be between 1-4. 1 being the lowest and 4 being the highest. The Text value can be ALL which means Log Level of 4.
SIEBEL_LOG_DIR : This value of this variable is path where you want to create the logs. Makes sure that the path you mention is valid otherwise the logs will be created in the temp directory.
Examples of both the environmental variables are as following
SIEBEL_LOG_EVENTS : ALL
SIEBEL_LOG_DIR : C:\siebel_logs
The process to create these enviornmental variables is as following
- Right click on the My Computer Icon
- Select Properties from context menu
- Go to ==> Advanced Tab
- Click ==> Environmental Variables
- In User Environmental Variables Tab click New
- Enter SIEBEL_LOG_EVENTS in Variable Name field
- Enter ALL in Variable Value Field
- Click New again and Enter the following details
- Variable Name : SIEBEL_LOG_DIR ; Variable Value : “your siebel log path”
Remember that Variable Names are case Sensitive. After you have created the variables when you start you dedicated client you will notice that in the path that you have given a file named siebel.log is created for Dedicated Client and file named siebel_dev is created for Siebel Tools.”
Creating SiebelMessage (Hierarchy)
Posted by: eserhan on: October 31, 2009
- In: Integration | Siebel | Siebel Hints
- Leave a Comment
This section helps you to create an Hierarcy type input using EAI Siebel Adapter.
Click the Simulator under the Administration – Business Service.
First of all; select the Service Name as “EAI Siebel Adapter” and Method Name as “Query”
Then in the Input arguments click the New button.
Set Type to “PropertySet” and define two Property Name;
1-) PrimaryRowId (Row id of the base table of the base business component of the integration object)
2-) OutputIntObjectName (Name of the IO that SiebelMessage will be created)
Click on Run button and get the SiebelMessage from the Output Arguments part.
Combining Similar Rows
Posted by: eserhan on: February 26, 2009
First, we create a table named combine test;
CREATE TABLE combine_test AS
SELECT 100 eft_no, 4 bank_code, NULL eft_amount, ‘success’ eft_desc FROM DUAL UNION ALL
SELECT 100, 2, 100, NULL FROM DUAL UNION ALL
SELECT 120, 4, NULL, ‘pending’ FROM DUAL UNION ALL
SELECT 120, 4, 210, NULL FROM DUAL UNION ALL
SELECT 200, 3, 200, NULL FROM DUAL;
|
EFT_NO |
BANK_CODE |
EFT_AMOUNT |
EFT_DESC |
|
100 |
4 |
|
Success |
|
100 |
2 |
100 |
|
|
120 |
4 |
|
Pending |
|
120 |
4 |
210 |
|
|
150 |
3 |
200 |
|
Then, we get the bank_name using the table data. Now the output is like this;
|
EFT_NO |
BANK_CODE |
BANK_NAME |
EFT_AMOUNT |
EFT_DESC |
|
100 |
4 |
|
|
Success |
|
100 |
2 |
American |
100 |
|
|
120 |
4 |
American Express |
|
Pending |
|
120 |
4 |
|
210 |
|
|
150 |
3 |
American Express |
200 |
|
As you see; rows with the same eft_no have missing fields comparing to each other.
If we want to make a single-completed row, table x is grouped by eft_no
and maximum value of the columns wanted to be completed is taken.
SELECT x.eft_no, MAX (x.eft_amount) eft_amount, MAX (x.bank_name) bank_name, MAX (x.eft_desc) eft_desc
FROM (SELECT eft_no, bank_code,
DECODE(bank_code, 4, ’American Express’, null) bank_name, eft_amount, eft_desc
FROM combine_test
GROUP BY eft_no, bank_code, eft_amount, eft_desc) x
GROUP BY x.eft_no
The final output is;
EFT_NO EFT_AMOUNT BANK_NAME EFT_DESC
——– —————- —————– ————-
200 200
120 210 American Express pending
100 100 American Express success
Lead() function
Posted by: eserhan on: February 24, 2009
If we had an orders table that contained the following data:
ORDER_DATE PRODUCT_ID QTY 25/09/2007 1000 20 26/09/2007 2000 15 27/09/2007 1000 8 28/09/2007 2000 12 29/09/2007 2000 2 30/09/2007 1000 4
And we ran the following SQL statement:
select product_id, order_date,
lead (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders;
It would return the following result:
PRODUCT_ID ORDER_DATE NEXT_ORDER_DATE 1000 25/09/2007 26/09/2007 2000 26/09/2007 27/09/2007 1000 27/09/2007 28/09/2007 2000 28/09/2007 29/09/2007 2000 29/09/2007 30/09/2007 1000 30/09/2007 <NULL>
Lag() function
Posted by: eserhan on: February 24, 2009
If we had an orders table that contained the following data:
ORDER_DATE PRODUCT_ID QTY 25/09/2007 1000 20 26/09/2007 2000 15 27/09/2007 1000 8 28/09/2007 2000 12 29/09/2007 2000 2 30/09/2007 1000 4
And we ran the following SQL statement:
select product_id, order_date,
lag (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;
and the result is…
PRODUCT_ID ORDER_DATE PREV_ORDER_DATE 1000 25/09/2007 <NULL> 2000 26/09/2007 25/09/2007 1000 27/09/2007 26/09/2007 2000 28/09/2007 27/09/2007 2000 29/09/2007 28/09/2007 1000 30/09/2007 29/09/2007
Difference betweeen rank and dense_rank
Posted by: eserhan on: February 19, 2009
Partition is made over dept_no and ordered by salary for both rank and dense_rank. They both make the same job except the numeric ranking. The same number is assigned for records with the same order value in both of them. At dense_rank, the numeric assignment is made sequentially for next row. But in rank, it is not incremented sequentially.
If we need a list of employees in department 20 having the top 3 salaries using rank, ADAMS will be out of this list. This will be wrong for this kind of request.
with RANK_TEST as
( select 20 deptno, ‘SCOTT’ ename, 3000 sal from dual union all
select 20, ‘FORD’, 3000 from dual union all
select 20, ‘JONES’, 2975 from dual union all
select 20, ‘ADAMS’, 1100 from dual union all
select 20, ‘SMITH’, 800 from dual )
SELECT deptno, ename, sal,
DENSE_RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) dr,
RANK () OVER (PARTITION BY deptno ORDER BY sal DESC) r
FROM RANK_TEST
ORDER BY deptno, sal DESC;
|
DEPTNO ENAME SAL DR R ——– ———- ———- ——— ———- 20 SCOTT 3000 1 1 FORD 3000 1 1 JONES 2975 2 3 <<—- ADAMS 1100 3 4 <<—- SMITH 800 4 5 |
Advenced Queuing (AQ)
Posted by: eserhan on: February 19, 2009
there are not so many stuff about advanced queues for beginner level.
this example may help you to figure out..
-- to create queue, some grants are given to our user
GRANT "AQ_ADMINISTRATOR_ROLE" TO YTS;
GRANT "AQ_USER_ROLE" TO YTS;
GRANT EXECUTE ON SYS.DBMS_AQ TO YTS;
-- queue table is created
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'YTS.YTS_QUEUE_TABLE'
,QUEUE_PAYLOAD_TYPE => 'YTS.YTS_QUEUE_PAYLOAD_TYPE'
,COMPATIBLE => '8.1'
,STORAGE_CLAUSE => '
TABLESPACE D_SUPPORT_M
PCTUSED 0
PCTFREE 10
INITRANS 1
MAXTRANS 255
STORAGE (
INITIAL 1M
NEXT 1M
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
)'
,SORT_LIST => 'ENQ_TIME'
,MULTIPLE_CONSUMERS => FALSE
,MESSAGE_GROUPING => 0
,SECURE => FALSE
);
End;
Read the rest of this entry »
explain plan script
Posted by: eserhan on: February 19, 2009
this is a small hint to get a guick EXPLAIN PLAN when your ambulance button is not working at TOAD and you have limited time 
EXPLAIN PLAN FOR
SELECT * from something;
set pagesize 25
set linesize 121
SELECT * FROM TABLE(dbms_xplan.display);
just run this as script..
