Facing problem while selecting ...
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Facing problem while selecting ...

  1. #1
    Join Date
    Feb 2008
    Posts
    2

    Facing problem while selecting ...

    Hi

    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.

    It will be of great help if some on help to resolve.

    Regards
    P.Muthiah

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    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?
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  3. #3
    Join Date
    Feb 2008
    Posts
    2
    Quote Originally Posted by chrisrlong
    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.

    Regards
    Muthiah

  4. #4
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    You are probably hitting the varchar2() max lenght rather than a the "120 concatenated columns limit" you are suggesting.

    Take into consideration that if any of your concatenated columns is of the varchar2() datatype, your resulting concatenated string is a varchar2() therefore the 4,000 chars limit applies.

    You can test this theory by checking the size of your concatenated strings.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

  5. #5
    Join Date
    Feb 2008
    Location
    Chennai
    Posts
    1
    Dear p.muthiah,
    Are you working for any insurance company?
    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.

  6. #6
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by ajaykumarp_in
    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?
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width