Knowledge Pool

BI Applications: Bind Variables in View Objects

2011-10-31T11:57:00.000+05:30

BI Applications: Bind Variables in View Objects: We have a setting in ADF layer using which we can specify how the View Criteria and the WHERE clauses used in the queries fired on the VOs ...

The request filtering module is configured to deny a request that exceeds the request content length

2011-09-29T22:37:00.000+05:30

HTTP Error 400 Bad request

Or

Unable to upload large/bigger files on to web server (IIS)

Include the following setting in your Web.config file.
<system.webServer>
        <security>
            <requestFiltering>
                <requestLimits maxAllowedContentLength="1000000000" />
            </requestFiltering>
        </security>
    </system.webServer>

It allows upto 1000MB file to be uploaded.
The same can be achieved by the following command too.

%windir%\system32\inetsrv\appcmd set config "Default Web Site/<your app>" -section:requestFiltering -requestLimits.maxAllowedContentLength:1000000000

BI Applications: Trees in Peoplesoft Simplified

2011-09-13T16:49:00.000+05:30

BI Applications: Trees in Peoplesoft Simplified: Trees in PSFT can be broadly classified into Summer Trees and Winter Trees. I will take the same example of Department for both the cases so...

BI Applications: Collations in SQL Server

2011-08-18T19:02:00.001+05:30

BI Applications: Collations in SQL Server: Character Strings are stored in the SQL Server database using certain Bit Patterns.
A collation specifies the bit patterns that represent e...

BI Applications: Collations in SQL Server

2011-08-18T19:02:00.000+05:30

BI Applications: Collations in SQL Server: Character Strings are stored in the SQL Server database using certain Bit Patterns.
A collation specifies the bit patterns that represent e...

BI Applications: Resetting the password for SYS User

2011-08-05T13:50:00.000+05:30

BI Applications: Resetting the password for SYS User: "Many times we tend to forget the password of the SYS user. In such cases log onto the machine which hosts the DB server and connect as SYSD..."

BI Applications: Overrides in Informatica

2011-03-14T19:46:00.000+05:30

BI Applications: Overrides in Informatica: "Few points regarding overrides in INFA - Source Qualifier Override - 1. The number of columns in the SQ query should match with the numbe..."

BI Applications: Configuring INFA Connections in DAC

2011-01-18T13:45:00.000+05:30

BI Applications: Configuring INFA Connections in DAC: "Configuring the physical connections can be quiet daunting for Informatica DW server and REP server. First - Provide all the details in Set..."

BI Applications: Privileges within a PLSQL procedure

2010-12-31T14:38:00.000+05:30

BI Applications: Privileges within a PLSQL procedure: "Oracle expects that we mention explicitly what kind of privileges the PLSQL procedure has. If we do not specify it properly there are chance..."

BI Applications: Accessing Table Names of other Schemas

2010-12-31T14:32:00.000+05:30

BI Applications: Accessing Table Names of other Schemas: "Many times we would want to select the table names from another schema. In such cases below SELECT would help.. select TABLE_NAME FROM ALL_..."

BI Applications: Database Character Set of a DB

2010-12-02T22:02:00.000+05:30

BI Applications: Database Character Set of a DB: "This query will help in finding the database character set of an oracle db. Select value from SYS.NLS_DATABASE_PARAMETERS where PARAMETER =..."

BI Applications: Difference Between COALESCE and NVL

2010-11-15T18:19:00.000+05:30

BI Applications: Difference Between COALESCE and NVL: "COALESCE and NVL are used to achieve the same functionality of providing a default value in case the column returns a NULL. The differences..."

BI Applications: TO_CHAR(date column) returns zero

2010-09-13T14:51:00.000+05:30

BI Applications: TO_CHAR(date column) returns zero: "In Oracle Database we face this issue sometimes. We see that Date field contains all zeros Example - select to_char(Date_column,'dd-mm-yyyy ..."

BI Applications: Unsupported conversion Error for INFA maps on Orac...

2010-09-08T16:36:00.003+05:30

BI Applications: Unsupported conversion Error for INFA maps on Orac...: "If your ETL tasks are failing with error as Unsupported conversion Error and log mentions something like below - ORA-26097: unsupported conv..."

BI Applications: Debug the Debugger of INFA

2010-09-08T16:36:00.002+05:30

BI Applications: Debug the Debugger of INFA: "Many times we face issues while using the debugger feature of Informatica. We get error saying 'Error connecting to DTM Service' There are..."

BI Applications: DAC - For First Timers

2010-09-08T16:36:00.001+05:30

BI Applications: DAC - For First Timers: "DAC is the Datawarehouse Administration Console which is an important component of the OBIA stack. To understand DAC and learn all its feat..."

BI Applications: Oracle BI Applications

2010-09-08T16:36:00.000+05:30

BI Applications: Oracle BI Applications: "I have faced this question many times. What is Oracle BI Applications. In simple words its a prebuilt BI solution which can support multip..."

Converting multiple rows into a single comma separated row

2010-04-05T00:13:00.000+05:30

Recently, I have been bitten by the MSDN forums bug. I enjoy spending time in the SSAS and SSRS forums, and the learning that I take out from there is tremendous. Initially, I just used to read and learn, while now I reply to a lot of posts too (just the simple ones, keep the tough ones for the experts to solve ;) ).

So the other day, there was this post - how can you convert multiple rows into a single comma separated row? For eg,

This can be done by the FOR XML command.

select State, (
select City + ',' as [text()]
from tableA soi
where soi.State=t.State
order by City
for xml path( '' )
)
from tableA s )t

A handy command, especially because you don't have to use stored procedures to achieve the result.

Stagnation

2009-01-01T14:31:00.002+05:30

Well it's been quite a while since I posted anything here and quite frankly it's been quite a while since I found anything worth posting. My work occupies most of my time and it seems like I have lost that zeal to learn and try out new stuff. So,as part of this year's resolution, I have decided to hit the books again! Hopefully you will see a new post coming from me soon.

Meanwhile, I just thought of sharing this link with all of you. I hit this link during my inital training days and have found this quite useful.

http://learndatamodeling.com/

Regards,
Chandan
'Knowledge should not be speculative, it should be certainty'

Union woes

2008-07-23T22:35:00.005+05:30

Silly Me!

I always thought a Union should and would accept a Select clause that is exactly identical w.r.t number of columns, data types and column names. i.e if I want to say display the list of all employees who are in Salary Grade 'A' and the list of employees who are in HR Department in the same result set (for some strange reason) I was expecting this to be the correct query:

SELECT
E.EMPLOYEE_NAME,
S.SAL_GRADE AS GRADE_DEPT
FROM EMPLOYEE E , SALARY_GRADE S
WHERE E.SAL > S.LOW_SAL
AND E.SAL < S.HIGH_SAL
AND S.SAL_GRADE = 'A'

UNION

SELECT
E.EMPLOYEE_NAME,
D.DEPT_NAME AS GRADE_DEPT
FROM EMPLOYEE E, DEPARTMENT D
WHERE E.DEPT_CODE = D.DEPT_CODE
AND D.DEPT_CODE = 'HR'

But apparently my assumption made using my "profound knowledge" was wrong. All a Union requires is for the number of columns and the data type of the columns in the Select list to be the same (refer Wiki). So even if I interchange the columns in the second query above, the query will still run and give results, but not necessarily the results you would want!

I learn this the hard way after discovering a stange bug in one of the reports that I had developed. After looking into the SQL I realized that I had interchanged the column names in one of the SQLs in the Union. The SQL still worked fine because each query was still having equal number of columns and same data types for each column!

Felt like pulling my hair out after discovering the bug!

Correlated Update : A tricky Situation

2008-07-17T17:38:00.000+05:30

Hi Folks,

Long time no see, any post.
Last day I saw a bewildering issue. It may not strike some of you if you already know it.
But i thought this should be shared.

In a correlated update :
eg:
UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);

We know one thing ; that is it will scan the whole table and consider each row for updating.
But in this case if the inner query returns not result, still the update will complete. It will update even the row for which the inner query did not give any data with NULL. Some kind of force update .Hence resulting in loss of data.
Tom Kyte says this not the right way to do such an update.

To avoid this there are two methods:

- Use an updatable join (updatable view), if the row to be updated is key preserved (i.e; only one row is fetched for the join condition).

- Use an exists clause in the outer where condition, so that only records with change are picked for update. It also makes the query faster as the update is done on a smaller subset.

UPDATE MASTER_ORDERS X
SET QTY=(SELECT Y.QTY
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM)
WHERE EXISTS (SELECT 1
FROM ORDERS Y
WHERE X.ORDER_NUM = Y.ORDER_NUM);

Insufficient Privileges in Stored Procedures

2008-04-01T15:45:00.007+05:30

Hello All,
I came across an intersting problem.
There are two roles CONNECT and RESOURCE given to user xxx.
The problem is while creating a view dynamically through named procedures,i get insufficient privileges error
ex:create or replace procedure stg_proc as
begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
SQL> /
Procedure created.
SQL> exec stg_proc;
BEGIN stg_proc; END;
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "STB_STAGING.STG_PROC", line 3
ORA-06512: at line 1
but the strange thing was, there was no error with anonymous procedure block while creatng the view dynamically
ex:begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
SQL> /
PL/SQL procedure successfully completed.
The reason why this happens is
PL/SQL Blocks and Roles
The use of roles in a PL/SQL block depends on whether it is an anonymous block or a named block (stored procedure, function, or trigger), and whether it executes with definer's rights or invoker's rights.
Named Blocks with Definer's Rights
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
The SESSION_ROLES view shows all roles that are currently enabled. If a named PL/SQL block that executes with definer's rights queries SESSION_ROLES, the query does not return any rows.
Anonymous Blocks with Invoker's Rights
Named PL/SQL blocks that execute with invoker's rights and anonymous PL/SQL blocks are executed based on privileges granted through enabled roles. Current roles are used for privilege checking within an invoker's rights PL/SQL block, and you can use dynamic SQL to set a role in the session.
There are 2 solutions to the above problem
1)AUTHID as CURRENT_USER
2)Grant CREATE VIEW permission to xxx.
ex:create or replace procedure stg_proc AUTHID CURRENT_USER as
begin
execute immediate 'create or replace view stg_view as select * from stg_dummy';
end;
Procedure created.
SQL> exec stg_proc;
PL/SQL procedure successfully completed.
For further information can be found at http://stanford.edu/dept/itss/docs/oracle/10g/network.101/b10773/authoriz.htm#1007305
Hope it helps....

Difference between a Primary Key and a Unique Index

2007-12-31T18:34:00.000+05:30

The differences between a Primary Key and a Unique Index are:
1) Column(s) that make the Primary Key of a table cannot be NULL since by definition, the Primary Key cannot be NULL since it helps uniquely identify the record in the table. The column(s) that make up the unique index can be nullable. A note worth mentioning over here is that different RDBMS treat this differently –> while SQL Server and DB2 do not allow more than one NULL value in a unique index column, Oracle allows multiple NULL values. That is one of the things to look out for when designing/developing/porting applications across RDBMS.
2) There can be only one Primary Key defined on the table where as you can have many unique indexes defined on the table (if needed).
3) Also, in the case of SQL Server, if you go with the default options then a Primary Key is created as a clustered index while the unique index (constraint) is created as a non-clustered index.
This is just the default behavior though and can be changed at creation time, if needed.
So, if the unique index is defined on not null column(s), then it is essentially the same as the Primary Key and can be treated as an alternate key meaning it can also serve the purpose of identifying a record uniquely in the table.

I have directly picked the above information from http://decipherinfosys.wordpress.com/2007/07/04/back-to-the-basics-difference-between-primary-key-and-unique-index/ link.

Selectivity Factor - Block Selectivity

2007-11-25T23:19:00.000+05:30

Hello everyone.

I was just reading through an article on Indexes, and as I was reading it I realized that I had a mistake in my earlier post where I had claimed that Row selectivity is an important factor to consider while deciding on Indexes. As you know , Indexes are only helpful if they help in reducing the number of IO(physical or logical). Lets consider the case of Oracle. In Oracle, data in a table is stored in blocks. Lets assume a case where each block hold only 100 rows of a table. If there 1000 rows in a table, then assume that these are stored in 10 different blocks. In this case, even if a filter has a low selectivity (say 10%), having an index will result in 10 IOs per block(for 10 rows in each ). If Oracle had decided to do a Full Table scan, then it would have resulted in just 10 IOs as a whole. My point here is, instead of taking Row selectivity as a decision making parameter, Indexing decisions have to be based on something known as Block selectivity. A simple definition of Block selectivity is :

Block selectivity = P/Ptotal
where P = Number of blocks which have atleast 1 row which qualifies the filter condition.
Ptotal = Total number of blocks below the high water mark of the table.

The lesser the block selectivity for a field, the better the performance will be if the field is indexed. One way to ensure a good block selectivity is to physically cluster the data along this field. A good clustering factor will ensure that the rows with same value for this field are physically close and in a lesser number of blocks. On the other hand , if these rows were spread across almost the entire set of blocks, then an Index may prove as an overhead as each block would have to be visited only once. In fact a full table scan would ensure that each block is read once and only once. So people, next time you are planning to index a field make sure to physically cluster the data in the table and find out the block selectivity for this field. For more information refer this link:

http://www.hotsos.com/e-library/abstract.php?id=5

P.S: You will have to register to read this article. Registration is very simple and will take only 1 min of your time.

Moving To New Tablespace.

2007-11-02T11:06:00.000+05:30

Hi Guys,

This is a small thing. But may be useful.
When we move tables and indexes to another tablespace, what all needs to be done (Oracle queries are used).
1) move the table.
alter table tablename move tablespace tablespacename;
2) now the indexes are of no use. You have to rebuild them in the new table space.
alter index indexname rebuild tablespacename;
3) Finally the packages need to be recompile. There is no need for explicit compilation as during execution it will be recompiled.
alter package packagename compile package;

Thats it!!

Insert Data Into 2 tables at 1 shot

2007-10-11T11:05:00.000+05:30

Hi All,

Have you ever wondered how to insert data into 2 tables from one table at one shot?

oracle provides a feature called INSERT ALL.....

I have used the above concept like this.

I insert good data into the fact table along with their rowid's into rowid_tab table at one shot.

Then i insert the data into reject table whose rowid's are not present in the rowid_tab.

ex: To Insert data into fact table and rowid table....
INSERT /*+ APPEND */ ALL
INTO TABLE_FACT(col1,
col2,
col3)
VALUES( col1.val,
col2.val,
col3.val )
INTO ROWID_TAB(ROWID_COL) VALUES(ROWID)
SELECT col1,
col2,
col3,
rowid
FROM TABLE_STG
WHERE Cond1 AND Cond2...;

To insert data into Reject table

INSERT /*+ APPEND */ ALL
INTO TABLE_FACT_REJ(col1,
col2,
col3)
VALUES( col1.val,
col2.val,
col3.val )
SELECT col1,
col2,
col3,
rowid
FROM TABLE_STG
WHERE rowid NOT IN (SELECT ROWID_COL
FROM ROWID_TAB);

Another improvisation can be to make ROWID_TAB as a Global Temporary table.

For more imformation on this,please refer the links below
1.http://certcities.com/editorial/columns/story.asp?EditorialsID=51
2.http://www.dba-oracle.com/t_global_temporary_tables.htm

Hope this helps....

Selectivity factor when deciding on Indexes

2007-10-10T21:32:00.000+05:30

Ever wondered how you should arrive at the indexing strategy to be used in your warehouse? What are the parameters to be considered while deciding on the fields to be indexed. Well,one parameter which I feel is important to consider is what I call as the 'Selectivity Factor'. The following is the definition of Selectivity factor( this is just my way of definition so no way of corroborating this)

Selectivity factor for a field - Percentage of rows selected from the table after applying the filter on the field.

You have to keep in mind that this factor will be different for different values of the same field. But again, you will have an estimate of the number of rows in the table for each of these values, so you can take an average of the Selectivity factor.The Average selectivity factor for a field is always the same irrespective of the SQL in which it is used.

Now if you have many SQLs having the same kind of filter ( a date field for example), then it makes sense to have an index on that field, right? Not always! What if the filter qualifies 90% of the records most of the time , then there will be no point in having that filter.In fact it will be an overhead, as the Database will first scan the index list then hit the actual row ids. If suppose you use a B*Tree index, then you might end up doing a very high number of logical I/Os, which will translate to reading the same set of blocks multiple times. But if you were reading only 10% of the table, then the number of logical I/Os will come down drastically. For example, suppose you have a query like this:

Select * from list_of_politicians where sex = 'M'

In this case the field "sex" has a cardinality of 2 -'M','F' (assuming there is no ambiguity about this data!). Assume that the table has 1,00,000 records out of which 90000 are with 'M' and 10000 with 'F'. Suppose the block size is 8kB and the row size is 80 bytes(which means 100rows/block ) then approximately 1000 block are used for the table.In this case, the query is returning 90% of the table. If the B*Tree index is used in this case then, a logical I/O is done for each of the 90000 records, which means on an average each block gets read 90 times!!! this will be a huge performance hit.Whereas if it had done a Full table scan, the execution would have been much faster.

That is where I feel , "Selectivity factor" makes much more sense as a quantifiable parameter to use for taking that decision. In the above scenario, the selectivity factor is 90% in case of 'M' and 10% in case of 'F' which means the Average Selectivity factor is 50%. This is a high number and thus not suited for B*Tree indexes. On the other hand ,a Bitmap index will be very useful in this scenario. In a bitmap index, each index entry will store references to many rows.The bitmap structure is something like this:

Row 1 2 3 4 5 6 7 8

M 0 1 1 1 1 1 1 1
F 1 0 0 0 0 0 0 0

As you can see there are only two entries here in the index.The first entry shows that the value 'M' is appearing in rows 2,3,4,5,6,7,8 and the value 'F' in row 1. So if I run the query,

Select * from list_of_politicians where sex = 'M'

the db can easily get the rowids of all the rows which have values 'M' for sex by reading only a few index entries( there will be more than one entry per value of a field based on the number of rows and the storage size). This will perform much much faster than in case of a B*Tree index.

So to sum it up,

When the selectivity factor(or the average selectivity factor) for the field is low ( say less than 10%) and a small portion of the table is selected, then it is helpful to have a B*Tree index on the field.

When the selectivity factor(or the average selectivity factor) for the field is high (say greater than 25 %) and you are selecting a small to medium portion of the table then it useful to have a Bitmap index on this field.

On the other hand , if the selectivity factor is high and you are reading a large portion of the table then it is better to leave the field alone.

Now all this applies to a Rule based optimizer. If you are using a Cost based optimizer, then just analyzing the table before running the query will tell the optimizer whether to use the index or not. So in the above case, where you are selecting 90% of the records, if the table is analyzed before running the query, then the CBO will decide to do a Full table Scan instead of using the B*tree index.

So next time you are planning your indexes, make sure to study the data, know the cardinality of the fields and also the Average Selectivity factor of the fields.

Concatenation on group by

2007-10-09T19:44:00.000+05:30

Hi all,
As an extension to Chandan's previous post i would like to post this sample code which shows the usage of sys_connect_by_path operator in Oracle.

Using SYS_CONNECT_BY_PATH operator

Source table “temp”:

Name Deptno
------ -------
jagan 1
guru 2
varu 2
bharath 1
manju 1
giri 3
chandan 3

SELECT
deptno, substr(SYS_CONNECT_BY_PATH(name, ','),2) name_list
FROM
(
SELECT name,
deptno,
count(*) OVER ( partition by deptno ) cnt,
ROW_NUMBER () OVER ( partition by deptno order by name) seq
FROM temp
WHERE deptno is not null
)
WHERE
seq = cnt
START WITH
seq=1
CONNECT BY PRIOR
seq+1 = seq
AND PRIOR
deptno = deptno;

Result:
deptno Name_list
------- -------------
1 bharath,jagan,manju
2 guru,varun
3 chandan,giri

Concatenation on group by

2007-10-04T22:39:00.000+05:30

Hi,
I had this requirement in my project to group by a column of a table and produce a concatenation of row values for a second column for each group. For example consider a table with two columns:

Department Employee
1 Chandan
1 Jagannath
2 Rahul
2 Pradeep
2 Manjunath

I want the result like this:

Department Name_List
1 Chandan ,Jagannath
2 Rahul,Pradeep,Manjunath

And since this was in SQL SERVER 2000 , there was no recursive query and I had to write using sql only. I finally wrote it by transposing the rows and then concatenating the result. Something like:

Department Name1 Name2 Name3
1 Chandan Jagannath -
2 Rahul Pradeep Manjunath

As you can see, here there is a fundamental limit. How many columns will you consider for transposing as you don't know how many rows are there for each department. Well the only way to proceed is to find the maximum count of rows for any department and transpose it to that many (or more) number of columns. I did just that and it worked.

Now in Oracle there is something called SYS_CONNECT_BY_PATH which can be sued with a recursive query to achieve this. But wonders of wonders , MYSQL has a function called GROUP_CONCAT which does exactly this. A concatenation of rows for each group. Now I am changing my perception of MySql. With features like this, it is easily the coolest database for developers. May be it is not ideal for Data warehouses but who cares about that!!

So if you guys ever come across this kind of requirement in Oracle or MySQL make sure to use these features:)

See this links for more:
Oracle
http://www.oracle.com/technology/oramag/code/tips2006/101606.html
MySQL http://www.oreillynet.com/databases/blog/2007/05/debunking_group_by_myths.html

Enjoy!!

Crystal Reports- +ve's and -ve's

2007-09-07T12:21:00.000+05:30

Crystal reports is a very popular Reporting tool. Below is an attempt to measure its effectiveness in reporting.

+ve's

The simplicity of the tool.The tool asks for a connection and once you provide it either to a DB or a cube the report can be filled up through drag and drop of the fileds.
The parameter feature can be used for Yes/No questions etc as it does not have any radio buttons kind of feature.
The supress feature can be used extensively to get various functionalities. It depends on how and where you use it.
The formula fields can be used in tandem with supress feature and parameters,this can make many complex things simpler.
There are feature like on demand subreports(hyperlink to other reports) or normal subreports as well.

-ve's

Deleting any unused parameters is a pain sometimes as they dont get deleted and cause problems later
Only one level of sub report nesting is available.(You cant have subreport within a subreport)
We can have only one query for a report.(We cant have separate queries for different measures as in BO)
Aliases can be added but they really act weirdly.(so avoid them)
Formatting the report takes double the report development time.
Tables cant be added to the report(Lines have to be drawn..!!!)
Export feature is good only for PDF format. For other formats you cant even compare the report and the exported copy.

T-SQL : Sweet and Sour

2007-09-05T17:01:00.000+05:30

I have working on SQL server 2005 (t-sql) lately. This blog is a comparison between the pl/sql features available in t-sql,db2 v 8.2 and oracle 9i pl/sql.
This blog will be updated when anything new turns up.
So here goes...

#	Feature	Available in SQL server 2005?	DB2	Oracle
1	Exception handling	Yes.	yes	yes
2	Transaction	Yes.	yes	yes
3	ELSIF construct	No	yes	yes
4	IF exists ()	Yes.	yes	no
5	Max column size	32000	32000	4000
6	Implicit Cursor	No	Yes	Yes
7	Recursive query	Yes. Similar to db2 but only 100 literations	yes	yes : using connect byprior
8	Print Command	Yes	No	Yes
9	Dynamic SQL	Yes	Yes	Yes
10	Dynamic SQL STRING DATATYPE	nvarchar and nchar	varchar	varchar
11	Variable and cursor name same	yes	yes	no
12	Default value while variable declaration	no	yes	yes
13	Sequences	No	Yes	yes

Extracting parts from a Comma seperated string

2007-09-03T14:10:00.000+05:30

One of the requirements in our project was to extract individual column names from a string containing comma seperated values i.e if the string has say:
v_column_string := ',country_code,country_name,country_descripton,'
I wanted to get each column name from this string. Here's how I wrote the query for that..

[CODE]
SELECT *
FROM(
SELECT
SUBSTR(v_column_string ,
INSTR (v_column_string , ',' , 1 , A.LVL)+ 1,
INSTR (v_column_string , ',' , 1 , A.LVL + 1 ) -
INSTR (v_column_string , ',' , 1, A.LVL)-1
) AS COLUMNS
FROM
( SELECT ROWNUM LVL FROM ALL_OBJECTS WHERE ROWNUM <= no_of_words)A

) B
WHERE B.COLUMNS IS NOT NULL

OUTPUT :

country_code
country_name
country_desc

where no_of_words is the number of words in the String(in this case it is 3). In case you don't know how many words might be there, you can go ahead and give the length of the string here(although this will be an overhead as many nulls will be returned , which is why i have a filter for not null).

Notice that I have given the string with a leading and ending comma i.e ',country_code,country_name,country_desc,'. This is basically to extract the first word and the last. If you don't want to give these two comma's you can tweak the query to handle it.

Hope you wil find this useful someday:)

CLOB Datatype

2007-09-03T11:10:00.000+05:30

Ah!! The first Tech Post...
Some time back, i had tried out some things to get the features of the CLOB datatype in DB2.
I will share the details, so that you guys need not spend more time on it if in some point of time you need to find the features of CLOB.
Some of the features are w.r.t storing a procedure text in a clob variable.

Features Of CLOB Variables

No Parameter Supports
1 Size upto 2GB - 1 byte

2 Logging Yes

3 Reading Directly from disk. Buffer Pool Not Used.

4 Execute immediate
of DMLs specified in
CLOB Yes
[Code]
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB ='INSERT INTO GTT_CORE.DUMMY VALUES(1)';
EXECUTE IMMEDIATE V_CLOB;

END

5 Compilation of Procedure
as dynamic statement Yes
[Code]
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;
SELECT COUNT(1) INTO V_COUNT FROM GTT_CORE.DUMMY;
INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';
EXECUTE IMMEDIATE V_CLOB;

END

6 Indentation of procedure Yes

7 Global Temporary Table

With a CLOB column NO. DB2 SQL error: SQLCODE: -350, SQLSTATE: 42962,SQLERRMC: V
Message: LOB, DATALINK, or structured type column ""V"" cannot be used in an index, a key, a unique constraint, a functional dependency, a generated column, or a declared temporary table.

[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;
declare GLOBAL TEMPORARY TABLE DUMMY_CLOB (V CLOB) ON COMMIT PRESERVE ROWS NOT LOGGED WITH REPLACE;
SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into SESSION.DUMMY_CLOB values(v_clob);

END

8 Storage of CLOB in a table.
Is it with indentation?? Yes
[Code]
DROP PROCEDURE GTT_CORE.CLOB_TEST
GO
CREATE PROCEDURE GTT_CORE.CLOB_TEST
LANGUAGE SQL
BEGIN
DECLARE V_CLOB CLOB;

SET V_CLOB =
'Create procedure GTT_CORE.PROC
LANGUAGE SQL
BEGIN
DECLARE V_COUNT INTEGER;';
SET V_CLOB = V_CLOB||'
SELECT COUNT(1)
INTO V_COUNT
FROM GTT_CORE.DUMMY;

INSERT INTO GTT_CORE.DUMMY VALUES (V_COUNT);
END';

insert into gtt_core.dummy1 values(v_clob);

END

Hope it will be useful someday

-- Rahul Nair

Posting Guidelines

2007-09-01T21:55:00.001+05:30

I have identified some posting guidelines to help us have better posts
Authors

Please Contribute through new posts. Don't Use comments section.
Reply to questions through Comments(responses) link.
Please Give appropriate labels

Some labels can be : Oracle, DB2, SQL server, DW, Java,SQL,PL/SQL
Labels can help to find postings fast if there are too many :)

Non Authors

Please Post Question in Response link

We can add to the above when need arises. But let us first kick this off to a good start.

Knowlege Pool

2007-09-01T21:49:00.000+05:30

Welcome to Knowledege Pool Guys!!!
This is an Initiative for having a knowledge repository accessible, wherever you are.
Learn . Share and Question
So lets take it forward.....