Merve Olamlı

Views Not Seen in Local DB

Posted by merveolamli on October 16, 2010

When I logined from my thick (developer) client to local environment , I realised that some views aren’t visible. As a sample, I could not open “Workspace Projects” view in Administration-Product screen in my local db. This view is added to my responsibilities , both in Siebel Administrator (out of box one) and X which is the custom one (which is copied from Siebel Administrator and including other custom views). It could be because of the srf, so I took the srf from the server side, in which this view is visible.  But nothing changed after these controls in my local, still not visible.

After a search, I got some information from Oracle Support: If a user has more than one responsibility, all the responsibilities’ Local Access Check flag should be True. If  any of one has this view with Local Access as False, view gets invisible in the client side. Porblem was fixed, deleting the Siebel Administrator (out of box one, so it is read only, its Local Access flag can not be checked under Administration-Application -> Views ) Responsibility under the local user. So, all the responsibilities of the user has the view with Local Access check flag is set to Y. Logoff – login again, the view is now available.

Advertisements

Posted in SIEBEL | Leave a Comment »

Unlock a Project in Local Repository

Posted by merveolamli on October 22, 2009

If you get a Project from the Server Repository , which is locked by another user, the Project’s “Locked” flag is checked. You can not modify the objects belonging to this locked Project. To be able to work with this Project in local repository , it should be locked by local repository user. If you know the user’s password , you can login to Server Repository and unlock the Project, then get it. But, it is so risky that this user can not check in the Project from his local repository anymore. You can login to your local db and with an SQL, the lock on the Project can be removed:

UPDATE SIEBEL.S_PROJECT SET LOCKED_FLG = ‘N’ WHERE NAME = < PROJECT_NAME >;

Posted in SIEBEL | Leave a Comment »

Symbolic String Error

Posted by merveolamli on September 26, 2008

When I tried to check out the Project that stores the Symbolic Strings (Project_B), I got an error meanwhile which is :

[Siebel Database][ODBC Driver][Adaptive Server Anywhere] Integrity constraint violation: Index ‘S_SYM_STR_U1’ for table ‘S_SYM_STR’ would not be unique

After clicking OK another pop up which says:

Siebel-ERR- 1124: Unable to import table ‘S_SYM_STR’ (UTLOdbcExecute insert)

So , I could not get the project from the Server.

After that, I increased the log level to find out the error. I searched the log text and came up with a symbolic string named X_xxx causes the error. I searched this in my local repository and its Project was seemed Project_A. Also, I logined to the server from Siebel Tools , again searched it but its Project was Project_B. While getting the Project_B , X_xxx loses uniqueness, so Siebel Tools throws like an error. To overcome, first I got the Project_A from the server which does not contain X_xxx to crush the code in repository. Following this, I was successful to check out the Project_B.

Posted in SIEBEL | Leave a Comment »

SPOOL COMMAND

Posted by merveolamli on September 12, 2007

‘spool … ’ command is used to send queries and their results to a file. This can be said, it is like a screen shot format of the commands to the file. You have to spool on and spool off between which executions to store. But there are two different output files when spooling in different editors, for sqlplus command line and for pl/sql developer. After connecting to the database:

* In sqlplus command line:

SQL> spool on
SQL> spool c:\spool_here.txt
SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off

–The output in the spool_here.txt file is:

SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off


* In pl/sql developer command window:

SQL> spool c:\spool_here.txt
Started spooling to c:\spool_here.txt

SQL> select count(*) from user_objects;
COUNT(*)
———-
71

SQL> spool off
Stopped spooling to c:\spool_here.txt

–The output in the spool_here.txt file is:

COUNT(*)
———-
71

According to this test situation, it can be said that, pl/sql developer only takes the outputs of the queries while sqlplus command window takes all the written queries and their outputs.
In addition to this, you cannot see any output in the output files until ‘spool off ’ command executes.

Posted in Oracle | Leave a Comment »

LOGIC QUESTION

Posted by merveolamli on September 12, 2007

Today, I read an advertising with a title ‘Do You Want To Work in Google ?’ and under this topic, there were many logic questions asked in its interviews. At first sight, one of them seemed to be solvable:

There are four people who have to pass beyond a rope bridge at night. But they have only one torch and its battery will be exhausted in 17 minutes. On the other hand, the bridge is not hardy to allow more than two persons to pass and also without torch. The people have different speeds. One passes in 1 minute, other one in 2 minutes, the third one in 5 minutes and the slowest one in 10 minutes. How can it be?

This one is not hard to solve, I tried and was successful 😉

Posted in Daily Topic | 1 Comment »

DATA INTEGRITY

Posted by merveolamli on September 3, 2007

In the applications, we want to prevent insertion, manipulation or deletion of invalid data to satisfy data integrity. So, to provide acceptable information, we have to apply some rules.

Data integrity is helpful for enforcing types of rules associated with the database. For instance, validity of data, manipulation and navigation of data in the tables correctly. Let’s explain the rules applied to table columns for data integrity.

First one is Null Rule, which is defined on a single column and controls if the value in this column can contain null values while insertion or deletion. Second one is Unique Column Rule, it can be defined on one or more columns. It controls if the column (s) contain unique values. According to this rule, values of these columns can contain null values. The Third one is Primary Key Rule, that is defined as a key value on a column or set of columns. It satisfies each row in the table can be uniquely defined. The difference to the Unique Column Rule is, none of the values can contain null values in Primary Key Rule. The other one is Referential Integrity Rules which are used frequently. The definition for this rule is the necessity of existence of a column or set of columns also in the referenced table. It controls the data manipulations on the referential values. The last one is Complex Integrity Checking. This is a user defined rule that checks insertion, deletion and update operations on a column or more than one columns.

Oracle enforces data integrity by definitions. Integrity constraints and triggers help these rules to be applied to the database.

Posted in Oracle | Leave a Comment »

ORACLE DATA MINING (ODM)

Posted by merveolamli on August 28, 2007

As a general look, data is raw information, however information is the result of processing, manipulating and organizing data which forms knowledge. Real-world applications consist of complex data.

Data mining means extracting information from large data sets and databases. Market Basket Analysis is an example of data mining used in sales : If men buy beer, diaper sales also increases.
Data mining is embedded in Oracle database and is one of the area of data warehouse. Data is stored in tables although there are many different types of information and does not leave database while the operation taking place. Also, the number of columns you mine is not important in Oracle Data Mining (ODM).

There should be a problem waiting to be solved. In addition to this, there are some basic steps for the solution. Data gathering is the first step. The next step will be to choose one of the data mining algorithms. Always remember, there is no one perfect algorithm for problems. The choice of algorithm can change according to the type of data and problem. Data should be filtered and normalized before used in algorithms.

Humans and machines are different from each other. Although one of them has ability to sense, identify and think , the other one behaves through what he learned. There are kinds of functions and algorithms required by Oracle Data Mining. Data mining applies machine learning concepts to data. There are two types of Data Mining functions. These learnings are generated from machine learning area. One of them is supervised data mining which is direct learning, making prediction. The second one is unsupervised learning which is used for descriptive uses and non-directed.
The conspicuous algorithms used in data mining are Naive Bayesian, Decision Tree, Clustering (k-means) ect.

Posted in Oracle | Leave a Comment »

TEMPORARY TABLES

Posted by merveolamli on August 21, 2007

Oracle can hold session-private data which means data is stored for the duration of transaction or session. Two types of temporary tables can be created with “create global temporary table…” statement, transaction-specific or session-specific. These names are the data existence for the temporary tables. For transaction-specific tables, data is stored during the transaction, or for session-specific transaction during the session. The rows are private to the session in temporary tables to modify its own data.

There are lots of characteristics like permanent tables. Temporary tables can be indexed and analyzed, but indexes created on temporary tables will be temporary too. Views and triggers can be created on temporary tables. Also, export and import utilities can be used with temporary tables.

In Oracle, data changes are stored in redo logs. But, for temporary tables, DML statements <insert , update, delete> do not generate redo logs. However, temporary tables generate a minimum amount of redo. They are not stored in rollback segments.

Oracle allocate temporary segments for the user needs. Data are deallocated at the end of the session or transaction.

The syntax of temporary tables is “create global temporary table…” .

The statement “… on commit delete rows” is used to specify that rows are only visible within the transaction.

“…on commit preserve rows” is also used to specify that rows are visible for the session.

A note from High Performance Tuning by Guy Harrison:

“Take advantage of temporary tables when your Pl/Sql needs to store data that does not need to persist beyond the session or the transaction”.

I will explain the difference between permanent tables and temporary tables with a simple example:

*The first part is about permanent tables.

First, we connect to the database:

Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

Then, create a table named test with a parameter id type number:

SQL> create table test (id number);
Table created

After that, insert a row with a value of id 1:

SQL> insert into test values(1);
1 row inserted

When we show the table’s contents:

SQL> select * from test;
ID
———-
1

At the end of these executions, disconnect from the database:

SQL> disconnect;
Not logged on

Again we connect to the database, and show the data in our test table:

SQL> connect hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

SQL> select * from test;
ID
———-
1

It seems that, Oracle hold data in permanent tables until you delete the row, truncate or drop the table.

*The second part of the example is about temporary tables.

We drop the permanent table and create a new one that is temporary:

SQL> drop table test purge;
Table dropped

Following this, create a temporary table which hold the data during the session:

SQL> create global temporary table test (id number) on commit preserve rows;
Table created

SQL> insert into test values (2);
1 row inserted

SQL> select * from test;
ID
———-
2

Again disconnect from the database:

SQL> disconnect;
Not logged on

Connect once more:

SQL> connect hr/hr;
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0
Connected as hr

Finally, as select the data from the test table, there is no data to be stored:

SQL> select * from test;
ID
———-

 

In conclusion, temporary tables are useful in applications which you need to store data for transaction or session level.

Posted in Oracle | Leave a Comment »

‘DEFAULT’ CLAUSE IN ‘CREATE TABLE’ STATEMENT

Posted by merveolamli on August 14, 2007

When a table will be updated or any data is inserted into the table, a default value can be used. At the beginning, the column’s value is set to value in create table statement with default clause. Also, the data type of the expression must match with the data type of the column It will be clear with a simple example.

First , a table named default_tbl is created without a default value in its columns:

SQL> create table default_tbl (x integer, y varchar2(10));
Table created

After creating the table, we insert a row, which its x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

When we select the table’s data to show, x column has the value 1, but y column is null:

SQL> select * from default_tbl;
X                             Y
————– ———-
1

Let’s drop our table and create again but now with a default clause for y column:

SQL> drop table default_tbl purge;
Table dropped

SQL> create table default_tbl (x integer, y varchar2(10) default ‘y’);
Table created

Once we insert a row with an x column has a value 1:

SQL> insert into default_tbl(x) values (1);
1 row inserted

Finally, select table clause shows that table has a row with a y column value ‘y’ which we determined in create table statement:

SQL> select * from default_tbl;

X                         Y
————- ———-
1                         y

Posted in Oracle | Leave a Comment »

HEAP ORGANIZED TABLES – OBJECT TABLES

Posted by merveolamli on August 13, 2007

In Oracle, tables are the basic storage units. There are several types of database tables. While designing an application in the database, you should use the appropriate table type to store data for necessities. Each type of table has different characteristics to be used in different applications.

Heap Organized Table:

The default table type in Oracle is heap table. If you use “create table” clause normally, you create a standard database table. Exp:

SQL> create table test (id integer);
Table created

If you want to create another type of table, it should be denoted in create table statement.
“Heap” name comes from its heap-like management. When data is added to the table, the first free space that data can fit is used. As data is removed from table, insert and update statements can use this freed spaces.

Object Table:

This type of table is created based on an object type, not collection of columns. It is generally used in special cases. The logic depends on object-oriented programming. “create table ” statement for object tables is:

create table test of x_type;

An example from Thomas Kyte-Expert one-on-one book :

SQL> create or replace type address_type
2 as object
3 ( city varchar2(30),
4 street varchar2(30),
5 add_state varchar2(2),
6 zip number
7 )
8 /
Type created

SQL> create or replace type person_type
2 as object
3 ( name varchar2(30),
4 dob date,
5 home_address address_type,
6 work_address address_type
7 )
8 /
Type created

SQL> create table people of person_type
2 /
Table created

SQL> desc people;

Name Type Nullable Default Comments
———— ———— ——– ——- ——–
NAME VARCHAR2(30) Y
DOB DATE Y
HOME_ADDRESS ADDRESS_TYPE Y
WORK_ADDRESS ADDRESS_TYPE Y

As you can see from the example, two objects are created. One of them is address_type and the other one is person_type. After that, a table named people is created based on these objects. This table has type person_type, also pay attention to the person_type object. It includes two attributes which has types address_type. The table people has four columns , two of them are object types.

Like in C structure types, C++ or Java class types , Object types are good structures for representing objects to use in the tables, instead of specifying the columns of the object again.
But according to heap tables, object tables allocates more spaces.

Remember that, there is no one truth. Performance depends on the application.

Posted in Oracle | 1 Comment »