If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Hybrid View

Facing problem while selecting ...

We have a specific requirement of Concatenating all the columns of a table in single select statement using ~ as the column delimiter.

Select col1||'~'||col2 from x.

The expression length varry depends on the number of columns in the table.

So far it was working fine and suddenly, we are getting only 32 bytes of the result. The result is coming properly if number of columns are less than 121 and it is giving 12 bytes results if the number of columns exceeds 121.

Also if we spilit the expression into 4 colums by grouping 100 columns in each group it is working fine.

Is it because of any Parameter setting in Oracle or related to any OS level setting.

Okay, first of all, what clueless wonder decided the proper way to return data was to concat it all into a single string?

And you're going to need to provide a few more details if you want an answer. Like, what does the table look like and what does the calling code look like.

And more likely than not, the issue will have something to do with the length of the string you are building, and not how many columns went into it.

Oh, and what are the 12-byte values that are being returned?

Hi Chrisrlong

Thanks for your response.

The purpose of this query is to create a data file with '~' as the field delimiter and the it will be a variable record length. To achieve this we are concatenating the required columns with delimiter ~.

We suppose to get the result set of all the field values concatenated with delimiter but we are getting only first 32 characters of result.

The table is having a mix of VARCHAR2, NUMBER, and DATE data type columns (130 columns). During concatenation proper data type conversion is also taken care .

You may be correct it is some this to do with [I] and not the number of columns. Actually it was desinged for Pro*C and we have enough length declared. Unfortunately the same select statement is not working in SQL*Plus also.

Is it something to do with Parameters setting at Oracle level or Memeory constraint at OS level. For you information we are using AIX server and Oracle 9i database.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

I have faced similiear problem. we are giving data to IRDA where the data is to be seperated by a'~' and given in a csv file.

You are using the past tense in regards to the problem and the present continue tense in regards to the underlying activity therefore it should be safe to assume you have solved it!!!
Wouldn't be nice to tell the poor guy how you solved it?

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.