Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2009
    Posts
    1

    Question Unanswered: Help: Query results to "No more data to read from socket"

    Hello,

    I am just a newbie in oracle pl/sql, I have a query with multiple unions.

    I am able to execute the query... but there are times when I got the error "No more data to read from socket"... Often times I get this error after the first execution [Only the first execution is successful]

    below is the source code of the said query I ran.
    ---------------------------------------------------------
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select
    to_char(DATADATE, 'yyyy/mm/dd') as SORT_DATE,
    DATADATE,
    to_char(DATADATE, 'mm/dd/yyyy') as DATE_,
    (select company_name from company where company_id = 1000637 and rownum = 1) as IFC_LABEL,
    'Ford Motor Company' as IFC_QV_LABEL,
    nvl(RATIO_VALUE, 0 ) as IFC_VALUE
    from table(RATIOS_HISTORICAL.RATIOS_TS(1000637,'CURRENT _RATIO_HIST','Q',0, ((48 * 4) -1) ,'INDIV', (select pkg_qvtemplate.f_get_format_type('1000637','CURREN T_RATIO_HIST','Q',0,((48 * 4) -1) ,'INDIV','A1') from dual) ))
    ) where DATADATE between to_date( '12/01/2003', 'MM/dd/yyyy') and to_date( '12/11/2007', 'MM/dd/yyyy')
    union
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select
    to_char(DATADATE, 'yyyy/mm/dd') as SORT_DATE,
    DATADATE,
    to_char(DATADATE, 'mm/dd/yyyy') as DATE_,
    'QV_SP500' as IFC_LABEL, 'S&P 500' as IFC_QV_LABEL,
    nvl(RATIO_VALUE, 0 ) as IFC_VALUE
    from table(RATIOS_HISTORICAL.RATIOS_IDX_TS('000003','ID X_CURRENT_RATIO_HIST','Q',0, (48 * 4) -1 ))
    ) where DATADATE between to_date( '12/01/2003', 'MM/dd/yyyy') and to_date( '12/11/2007', 'MM/dd/yyyy')
    union
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select
    to_char(DATADATE, 'yyyy/mm/dd') as SORT_DATE,
    DATADATE,
    to_char(DATADATE, 'mm/dd/yyyy') as DATE_,
    'QV_SP1500' as IFC_LABEL,
    'S&P 1500' as IFC_QV_LABEL,
    nvl(RATIO_VALUE, 0 ) as IFC_VALUE
    from table(RATIOS_HISTORICAL.RATIOS_IDX_TS('031855','ID X_CURRENT_RATIO_HIST','Q',0, (48 * 4) -1 ))
    ) where DATADATE between to_date( '12/01/2003', 'MM/dd/yyyy') and to_date( '12/11/2007', 'MM/dd/yyyy')
    union
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select
    to_char(DATADATE, 'yyyy/mm/dd') as SORT_DATE,
    DATADATE,
    to_char(DATADATE, 'mm/dd/yyyy') as DATE_,
    'QV_GSECTOR' as IFC_LABEL,
    'Consumer Discretionary (GSECTOR)' as IFC_QV_LABEL,
    nvl(RATIO_VALUE, 0 ) as IFC_VALUE
    from table(RATIOS_HISTORICAL.RATIOS_TS(1000637,'CURRENT _RATIO_HIST','Q',0, (3 * 4) - 1 ,'GSECTOR', (select pkg_qvtemplate.f_get_format_type('1000637','CURREN T_RATIO_HIST','Q',0,((48* 4) -1) ,'GSECTOR','A1') from dual) ))
    ) where DATADATE between to_date( '12/01/2003', 'MM/dd/yyyy') and to_date( '12/11/2007', 'MM/dd/yyyy')
    union
    select distinct SORT_DATE, DATADATE, DATE_, IFC_LABEL, IFC_QV_LABEL, IFC_VALUE from (
    select
    to_char(DATADATE, 'yyyy/mm/dd') as SORT_DATE,
    DATADATE,
    to_char(DATADATE, 'mm/dd/yyyy') as DATE_,
    'QV_GIND' as IFC_LABEL,
    'Automobiles (GIND)' as IFC_QV_LABEL,
    nvl(RATIO_VALUE, 0 ) as IFC_VALUE
    from table(RATIOS_HISTORICAL.RATIOS_TS(1000637,'CURRENT _RATIO_HIST','Q',0, (3 * 4) - 1 ,'GIND', (select pkg_qvtemplate.f_get_format_type('1000637','CURREN T_RATIO_HIST','Q',0,((48 * 4) -1) ,'GIND','A1') from dual) ))
    ) where DATADATE between to_date( '12/01/2003', 'MM/dd/yyyy') and to_date( '12/11/2007', 'MM/dd/yyyy')
    )

    ---------------------------------------------------------

    Note: this query is generated from my create function for getting the data I needed for creating the chart image.

    I have tried each of the queries being unioned and each of them are working alone... try unioning up to 4 queries and it runs fine... combining 4 of any of the queries... but when I union all of the 5 queries... only the first run of the query is successful... after that the error will appear
    ..........................
    another thing, the whole query works if I will add any filter on the result of the unioned query

    Is there a limitation on the number of queries to union? I cannot say its on the number of rows since.. the result of the 4 queries are less than 10 each .. only one query results into 1452 rows...

    I really do not know how to handle this problem since I am just a newbie in oracle

    hope somebody can help me with this..

    thanks a lot and godbless
    Last edited by borgymanotoy; 12-11-09 at 16:21.

  2. #2
    Join Date
    Feb 2005
    Posts
    57
    Use Google (or similar) to search for error message - there are many results.
    How are you running the query - from SQL*Plus, Java, etc?

Posting Permissions

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