How to Call PL/SQL or Batch file from siebel escript

Calling PL/SQL or Batch file from Siebel is always fun, it gives end users added flexibility to generate report by themself. Here we will see how to use Clib function in Siebel escript to call PL/SQL or batch file.
Suppose, we have to fetch Opportunities which start with Opportunity Name like ‘Test’ and write it in to a file. So our first task is to write one batch file which will have SQL statement with spooling option to fetch opportunities and next step, call batch file from siebel browser script.

Step 1:
Write all sql statements into a file, including the query “SELECT A.ROW_ID, A.NAME FROM SIEBEL.S_OPTY A WHERE A.NAME LIKE ‘Test%’; ” and save the file with .sql extension. We have saved it as opty.sql. The SELECT SQL statement will fetch all opportinities where name starts with ‘Test’ and spool it in opty.txt. Our opty.sql file looks like below.

Set heading off
Set echo off
Set feedback off
Set pagesize 0
Set inlinesize 256
Set serveroutput off

Spool “D:Runsqlopty.txt”
SELECT A.ROW_ID, A.NAME FROM SIEBEL.S_OPTY A WHERE A.NAME LIKE ‘Test%’;
Spool off

Step 2:
As we can not call SQL statement directly from Siebel script, we will put it in one batch file. So we have created one batch file opty.bat which will be used in script. Batch file should look like below:
Absolute path of Sqlplus.exe Database_UserName/Password@dbname @absolute path of the sql file

Step 3:
Now write one client side business service which will call opty.bat. Go to Administration – Business Service and create a new BS ‘Generate Report’ with method name as ‘Generate Report’.
In PreInvokeMethod, use Clib function to call the bat file.

function Service_PreInvokeMethod (MethodName, Inputs, Outputs)
{
        Clib.system(“D:\Runsql\opty.bat”); //give the absolute path of the file location.
        retunr (CancelOperation);
}

Now verify the bisuness service, go to Administration – Business Service > Simulator and create a record with Service name and Method name and click on Run. It will create an output file ‘opty.txt’ with opportunity details where Opportunity name starts with ‘Test’.

If you have any doubt or question, feel free to comment here. To get more updates on recent activities, follow ‘TechOneStop on Facebook/Twitter/LinkedIn or join our website as followers.

Siebel Configuration Interview Questions & Answers

In Siebel interview, many companies take online test first and then face to face round. Here we have listed down few Siebel configuration interview questions and answers (multiple choice) that will help you to pass the online exam with good percentage.

Siebel Configuration Interview Questions:

1. Types of web template files, choose 3

A) List Applet Web Template

B) View Web Template

C) Screen Web Template

D) Web Page Web Template

Ans: A, B, D

2. Find out all party entities, choose 2

A) Service Request

B) Account

C) Activities

D) Position

Ans: B, D

3. Use join for, choose as per applicable

A) 1:1

B) 1:M

C) M:1

D) M:M

Ans: A, C

4. If Outer Join Flag is TRUE, join returns

A) all rows from base table, even when there is no related row in joined table

B) all rows from join table, even when there is no related row in base table

C) all rows from base table as well as join tables

D) no rows from base table as well as join tables

Ans: A

5. Person related entities in Party Business Components, choose as per applicable

A) User List

B) Partner

C) Contact

D) User

Ans: B, C, D

6. Organization related entities in Party Business Components

A) Household

B) Organization

C) A and B

D) Neither A nor B

Ans: C

7. Business Object, choose 2

A) is a collection of business components

B) provides the foundation for applets

C) keeps the relationship among BCs

D) is associated with table

Ans: A, C

8. Link is used for, choose 2

A) 1:1

B) M:1

C) 1:M

D) M:M

Ans: C, D

9. In M:M relationship, we need to populate below link properties, choose as per applicable

A. Inter table

B. Inter Parent Column

C. Inter Child Column

D. Inter Child Delete

Ans: A, B, C, D

10. Below statements are true about Cascade Delete Property, choose 2

A) It determines how child records will be deleted when parent record is deleted

B) It is part of business component object definition

C) Values would be one among three: Delete, Clear, None

D) Use this property for 1:1 and 1:M relationship.

Ans: A, C

11. Types of Applications, choose as per applicable

A. Customer

B. Partner

C. Web

D. Employee

Ans: A, B, D

12. One List Applet could be based on ___ Business Component

A. More than One

B. One or Many

C. None

D. One and only one

Ans: D

13. Siebel Gateway Name Server uses ___ file to get details about component groups and components definitions

A. eapps.cfg

B. uagent.cfg

C. siebns.dat

D. All of the above

Ans: C

14. Which statement/s about views and responsibilities is/are true?

A. One view could be associated with one and only one responsibility

B. Users see the union of all views from all of their responsibilities

C. Data control is possible using proper responsibilities

D. All of the above

Ans: B

15. What are all access control mechanisms for controlling customer data, choose as per applicable

A. Access based on user id

B. Access based on responsibilities

C. Access based on catalog

D. Access based on user position

E. Access based on user organization

Ans: A, D, E

16. _____ table is used to store object definitions for Siebel applications.

A. Data

B. Interface

C. Repository

D. System

Ans: C

17. “Every object definition could be associated with one and only one project”. Is the statement true?

A. Yes

B. No

Ans: A

18. How do you choose the best view web template when building a new view using wizard?

A) When the wizard asks for the view template, scroll through the list and check each template’s layout in the preview window until you find a perfect one

B) The view wizard will automatically select the best template based on applet/s that you select to display in the view

C) Before starting the wizard, find an existing view that has the desired applet layout and check the view template object definition to determine the template being used

D) Always use the view default web template

Ans: C

19. What is View Web Template Item?

A) View object definition

B) Applets that have been bound to a view

C) Controls that have been bound to an applet

D) All of the above.

Ans: B

20. To bring party data into non-party business component, the join specification destination column should refer

A) ROW_ID

B) PAR_ROW_ID

C) ROW_ID of Party BC

D) Id

Ans: B

21. Which statements are true about customer data, choose two

A. Includes dynamic, transactional data

B. Is created and managed by company administrators

C. Can be grouped into catalog and category

D. Has access controlled at the record level

Ans: A, D

22. How can you assign access groups to child categories, choose two

A) Manually enter access groups for each child category

B) Right-click on the parent access group and select ‘Make Child’ to create a child with same properties

C) Click on the propagate button to assign the parent access groups to all child categories

D) Click on ‘Cascade’ button to assign the parent access groups to all child categories

Ans: A, D

23. If there is always a related row in a join table, should you set the ‘Outer Join Flag’ to ‘TRUE’?

A) Yes, it is necessary in order to pull data form join tables

B) Yes, it will improve performance

C) No, it will cause a conflict and join would not work properly

D) No, it is not necessary and will have a negative performance impact

Ans: D

24. ‘All View’ displays all records – view is, choose one

A) Directly assigned to user

B) Associated with the user’s organization

C) From all organizations

D) From the organization and its subordinate organization

Ans: B

25. S_PARTY_PER stores relations for, choose 3

A. Employee & Position

B. Account & Contact

C. Access Group & Members

D. Person & Employee

Ans: A, B, C

26. If you lock a project locally in Siebel Tools, what all statements are NOT TRUE, choose 2

A. You can not check-in your changes to the server

B. You can check-in your changes to the server

C. You can use Tools > Unlock to unlock a project

D. You can not undo your changes

Ans: B, D

27. Suppose in Contact List Applet, there are 20 records without any MVG and in Contact Form Applet, there are 4 MVGs without primary. How many queries Siebel will fire to display all 20 records on Contact screen?

A. 1

B. 24

C. 5

D. 20

Ans: C

28. You don’t need to compile below objects in a SRF file, choose 3

A. Workflow

B. Project

C. Access Group

D. Task

E. Integration Object

Ans: A, C, D

29. Steps to configure a list applet, choose 3

A. Create a new applet object definition

B. Associate applet template with applet object definition

C. Bind controls and list columns to the template

D. Add the applet to the screen

Ans: A, B, C

30. What all statements are correct about Detail Category, choose 2

A. Detail Category appears in the link bar or visibility filter dropdown list

B. It may belong to an aggregate category

C. It groups detailed views

D. It does not contain any view

Ans: C, D

31. Difference between an organization and a division

A. Organization can do data access control but not division

B.  Siebel developers can delete an organization from UI but not division

C. Siebel developers can delete a division from UI but not organization

D. Positions are assigned to organizations, not divisions

Ans: A

32. What statements are true about ‘Flat Tab’ in Object Explorer, choose as per applicable

A. It removes parent child relationship between two objects

B. It helps developers to find out child object with unknown parent

C. In ‘Flat Tab’, developers can expand top level object to see all child objects

Ans: A, B

33. What all products can you install using Siebel Enterprise Server Installer, choose as per applicable

A. Siebel Server

B. Siebel Gateway Name server

C. Siebel Tools

D. Siebel Web Client

E. Siebel Web server Extension

F. Sample Database

G. Database Configuration Utility

Ans: A, B, G

34. Suppose for Service Request BC, ‘Search Specification’ property value is ‘Status = Open’ and for Service Request List Applet, ‘Search Specification’ property value is ‘Status = Closed’. How records will be displayed in Service Request List Applet?

A. Users will see all service request records where status is open or closed

B. Users will not see any record in the applet

C. Users will see only those SR records where status is open

D. Users will see only those SR records where status is closed

Ans: B

35. Siebel File System contains, choose 3

A. Products

B. Contacts

C. Accounts

D. Solutions

E. Literature

Ans: A, D, E

Hope you have learnt something new from our Siebel configuration interview questions & answers.

If you have any question or want to give us feedback on Siebel configuration interview questions, please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.

SBL-DBC-00105 / ORA-00904: An error has occurred executing a Sql statement

SBL-DBC-00105: An error has occurred executing a Sql statement. Please continue or ask your systems administrator to check your application configuration if the problem persists.

ORA-00904:Invalid Identifier

Reason:

If you are upgrading Siebel application from HI to Siebel Open UI, it is quite obvious that you will face these two errors. Both errors occur when logical schema and physical schema are not in sync.

What do we mean by logical schema and physical schema?

Logical Schema is related to Siebel Tools. Whatever customization you do in Siebel tools, that is nothing but change in logical schema. When you apply those changes in actual database, that is called physical schema change.

For example: You create table and columns in Siebel tools that is logical schema change. When you apply and activate that table, actual table will be created in database that is physical schema change.

Mismatch between logical and physical schema could be for many reasons. Such as,  a BC field is pointing to a non-existing column or DVM is referring to inactive/non existing column.

Step to debug:

  1. First you generate the log file to get all mismatches between logical and physical schema. If you are using web client, then increase the log level of object manager and get the log file. For dedicated client, add spooling parameter by modifying properties of Siebel shortcut. To know more about Siebel spooling, read this: Specifying SQL Spooling in Siebel Developer Web Client
  2. Open the log file and search for the error code ‘SBL-DBC-00105’. Then you copy the whole query, followed by this error code.
  3. Run the query on Siebel server database using database query tool like PL SQL developer or Toad. It will tell you for which table or column, mismatch exists.
  4. Now open Siebel tools, select the table and check the column status. If the column is inactive, then make it active or change the configuration in BC so that Siebel does not refer the inactive or non existing column. If the column is newly created, then Apply/Activate the table once again.
  5. If the mismatch is related to join, check join configuration at BC and table level.

Follow the same steps (Step 2 – 4) to fix the error ‘ORA-00904: invalid identifier‘.

You could run DBCHCK Utility to sync logical and physical schema. To know more, read our article on DBCHCK utility.

Do you have any question? Please feel free to comment below.

Keep in touch, follow TechOneStop on Facebook / Twitter / LinkedIn / Goolge+.

<< SBL-EAI-04397 and SBL-BPR-00162                                        SBL-SCB-00014 and SBL-NET-01023 >>