java.sq.SQLRecoverableException: No more data to read from socket

Toad Extension for Eclipse Community

java.sq.SQLRecoverableException: No more data to read from socket

Follow / 1 Jun 2011 at 3:34pm

I put this problem yesterday in the Forum, but it isn't here now ... :( ? *** happens ;)

so another try

i lounch this in TEE 1.8

SELECT * FROM (SELECT * FROM TABLE(get_veic ('AV625KF'))) where rownum

and got this errors

java.sql.SQLRecoverableException: No more data to read from socket

java.sql.SQLException: OALL8 is in an inconsistent state

sometimes there is also the error

java.sql.SQLRecoverableException: Io exception: Broken pipe

is it possible the call doesn't work with JAVA?

here the declaration of get_veic

create or replace FUNCTION get_veic (
pvar_targa VARCHAR2 := NULL,
pvar_cognome VARCHAR2 := NULL,
pvar_nome VARCHAR2 := NULL,
pvar_rag_it VARCHAR2 := NULL,
pvar_rag_de VARCHAR2 := NULL)
RETURN pk_typ_tab_veicoli PIPELINED IS
lnum_id_veicolo veic_pc_parco.id_veicolo%TYPE;
lnum_id_pratica NUMBER;
lnum_id_persona NUMBER;
lvar_cognome VARCHAR2 (200);
lvar_nome VARCHAR2 (200);
lvar_ragione_sociale_it VARCHAR2 (200);
lvar_ragione_sociale_de VARCHAR2 (200);
lnum_result NUMBER;

.....
.....
.....

PIPE ROW (pk_typ_obj_veicoli
(rec.id_veicolo, -- veic_id
pvar_targa, -- veic_targa VARCHAR2 (50),
rec.id_persona, -- veic_pers_id NUMBER,
lvar_cognome, -- veic_pers_cognome VARCHAR2 (200),
lvar_nome, -- veic_pers_nome VARCHAR2 (200),
rec.id_controparte, -- veic_cont_id NUMBER,
lvar_ragione_sociale_it, -- veic_cont_ragione_sociale_i VARCHAR2 (200),
lvar_ragione_sociale_de)); -- veic_cont_ragione_sociale_d VARCHAR2 (200)));
END LOOP;

RETURN;

Replies

  • Follow / 2 Jun 2011 at 8:12am

    Hi Michael,

    hm, I cannot tell you what the problem is. I tested some simple pipeline functions and they worked. It may be a subsequent error, who knows. Are there some error call stacks? Those could provide more information...

    Tomas

  • Follow / 3 Jun 2011 at 12:47pm

    UI UI UI .... this bug will be a very difficult to investigate

    I create an other function with the same parameter and the same object piped as return value.

    But I changed the content of the function, there are now no selects and cursors but only static variables which will piped as row.

    With this method the select works fine.

    If I try me other select with the other function, after getting errors as described in my other message, I can't execute my function which works again, NOW THE SECOND FUNCTION GIVES THE SAME ERRORS. After reconnecting to the SCHEMA, the second select works again until i execute my first select again.

    I got only one time an log for errors, i paste it on the bottom

    i will go on, on investigating

    com.quest.toadext.sql.SqlExecutionException: java.sql.SQLRecoverableException: Io exception: The Network Adapter could not establish the connection
    at com.quest.toadext.sql.SqlExecutor.reconnect(Unknown Source)
    at com.quest.toadext.sql.SqlExecutor.logon(Unknown Source)
    at com.quest.toadext.models.MConnection.connect(Unknown Source)
    at com.quest.toadext.models.MConnection.logon(Unknown Source)
    at com.quest.toadext.connections.view.ConnectAction.doRun(Unknown Source)
    at com.quest.toadext.ui.actions.ToadextActionDelegate.doRunWithEvent(Unknown Source)
    at com.quest.toadext.ui.actions.ToadextActionDelegate$1.run(Unknown Source)
    at org.eclipse.core.runtime.SafeRunner.run(SafeRunner.java:42)
    at org.eclipse.ui.internal.JFaceUtil$1.run(JFaceUtil.java:49)
    at org.eclipse.jface.util.SafeRunnable.run(SafeRunnable.java:175)
    at com.quest.toadext.ui.actions.ToadextActionDelegate.runWithEvent(Unknown Source)
    at org.eclipse.ui.internal.PluginAction.runWithEvent(PluginAction.java:241)
    at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
    at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
    at org.eclipse.jface.action.ActionContributionItem$6.handleEvent(ActionContributionItem.java:452)
    at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
    at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1258)
    at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:3540)
    at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3161)
    at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2640)
    at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2604)
    at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2438)
    at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:671)
    at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
    at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:664)
    at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
    at org.eclipse.ui.internal.ide.application.IDEApplication.start(IDEApplication.java:115)
    at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
    at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
    at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
    at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369)
    at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
    at java.lang.reflect.Method.invoke(Method.java:597)
    at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:620)
    at org.eclipse.equinox.launcher.Main.basicRun(Main.java:575)
    at org.eclipse.equinox.launcher.Main.run(Main.java:1408)
    at org.eclipse.equinox.launcher.Main.main(Main.java:1384)
    Caused by: java.sql.SQLRecoverableException: Io exception: The Network Adapter could not establish the connection
    at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:101)
    at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:112)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:173)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:229)
    at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:458)
    at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:411)
    at oracle.jdbc.driver.PhysicalConnection.(PhysicalConnection.java:490)
    at oracle.jdbc.driver.T4CConnection.(T4CConnection.java:202)
    at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:33)
    at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:474)
    at com.quest.toadext.oracle.sql.SqlExecutorOracle.createConnection(Unknown Source)
    ... 40 more

  • Follow / 22 Jun 2011 at 10:42am
    I will trying this bug with an other JDBC driver, as described in other bugs.

    On selecting an ojdbc14_10g i got this error

    Selected JDBC driver is not compatible with the current JRE.
    Your current JRE version is 1.6.0_25

    which JDBC driver I should use? And are there any limitations on JRE?
  • Follow / 22 Jun 2011 at 3:01pm
    So with ojdbc14.jar I have got the same problem!

    I try this afternoon reproducing it for you

    Michael
  • Follow / 22 Jun 2011 at 3:16pm

    1) ojdbc14_10g.jar is not supported... That error message is wrong, it was not intented for this incompatibility, we will fix it - TEE-73.

    2) We support ojdbc14.jar, ojdbc5.jar and ojdbc6.jar... ojdbc6 does not work with JRE 5 (or 4).

    Tomas

    Message was edited by: Tomas

  • Follow / 22 Jun 2011 at 3:32pm
    Michael,

    we analyzed your error call stack. It seems that this is a bug in the ojdbc driver or somewhere in combination driver-database. I am forwarding you a link, maybe you find much more information yourself.

    http://www.orindasoft.com/public/PL-SQL%20Recordstwo.php4?siteloc=PL-SQL%20Recordstwo#probva
    Another quotation:

    "This is known to occur under when you are using too big an array size. How big your array can be depends on the length of each record and the Driver/Database combination. If you exceed the maximum size you will get the "OALL8" SQLException and your connection object may become unusable."

    I hope you find something useful to solve the problem.
    Tomas

  • Follow / 22 Jun 2011 at 5:22pm

    OMG,

    I was able to reproduce the bug ....

    I hope you also can reproduce it to this on a schema

    #### create a type as object

    create or replace TYPE pk_typ_obj_veicoli AS OBJECT (
    veic_id NUMBER,
    veic_targa VARCHAR2 (50),
    veic_pers_id NUMBER,
    veic_pers_cognome VARCHAR2 (200),
    veic_pers_nome VARCHAR2 (200),
    veic_cont_id NUMBER,
    veic_cont_ragione_sociale_i VARCHAR2 (200),
    veic_cont_ragione_sociale_d VARCHAR2 (200)
    )
    /

    #### create a type as table

    create or replace TYPE pk_typ_tab_veicoli AS TABLE OF pk_typ_obj_veicoli
    /

    #### create a simple table

    create table tableTEE
    (
    id_veicolo number(11)
    )
    nocache
    logging;

    #### insert one row in the created table

    insert into tableTEE (ID_VEICOLO) values (111620);

    #### create a function

    create or replace FUNCTION get_tee_2
    (
    pvar_targa VARCHAR2 := NULL,
    pvar_cognome VARCHAR2 := NULL,
    pvar_nome VARCHAR2 := NULL,
    pvar_rag_it VARCHAR2 := NULL,
    pvar_rag_de VARCHAR2 := NULL
    )
    RETURN pk_typ_tab_veicoli PIPELINED IS

    CURSOR c IS
    SELECT * FROM tableTEE;

    BEGIN
    FOR rec IN c LOOP

    PIPE ROW (pk_typ_obj_veicoli
    (1, -- veic_id
    'AA', -- veic_targa VARCHAR2 (50),
    10, -- veic_pers_id NUMBER,
    'LASTNAME', -- veic_pers_cognome VARCHAR2 (200),
    'FIRSTNAME', -- veic_pers_nome VARCHAR2 (200),
    10, -- veic_cont_id NUMBER,
    'BLA BLA IT', -- veic_cont_ragione_sociale_i VARCHAR2 (200),
    'BLA BLA DE')); -- veic_cont_ragione_sociale_d VARCHAR2 (200)));
    END LOOP;

    RETURN;
    END;

    #### call the table

    SELECT * FROM (SELECT * FROM TABLE(get_tee_2 ())) where rownum

    so, now everything is create and the select above must work!

    For me it works fine

    if I now insert another 7 times the same insert statment with the same value, I have got 8 rows with the same value.
    on re execute the select it works also.

    On inersting another time the same row (now there are 9 rows) and re-executing the select ... I got the errors ...

    I hope you also get the errors

    AND NOW DO THIS, go to the function get_tee_2 created before and comment the parameter and compile it.

    now the select works also with 9 rows.

    I think not only the parameters are a problem, becouse If I have got much more then 9 rows also on commenting the parameters I got the errors

    AND NOW PLEASE LET ME KNOW YOU WAS ABLE TO REPRODUCE THE BUG :/

  • Follow / 27 Jun 2011 at 10:44am
    pipe_test.jpg

    Thank you very much Michael. I created the objects and ran the pipeline query... I just changed one row in the function to get the veic_id from the cursor instead from the fixed "1".
    I inserted more lines into the table, but there is no error message. I attach a snapshot so that you can believe that it works for me. This is also to illustrate that some problems are really hard to reproduce, there might be more factors involved.

    So again, we think that the problem is in Oracle. I used Oracle 11g/r1 and ojdbc6 in the correspondent Oracle client in the test.

    Tomas

  • Follow / 27 Jun 2011 at 3:14pm

    So I do the same thing in test JavaClient which do the same thing and I also get the error ....

    java.sql.SQLException: No more data to read from socket
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
    at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179)
    at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:1160)
    at oracle.jdbc.ttc7.MAREngine.unmarshalUB1(MAREngine.java:963)
    at oracle.jdbc.ttc7.MAREngine.unmarshalSB1(MAREngine.java:893)
    at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:375)
    at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
    at oracle.jdbc.ttc7.TTC7Protocol.fetch(TTC7Protocol.java:1253)
    at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2532)
    at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2850)
    at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:609)
    at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:537)
    at it.siag.oraclePipeTest.OraclePipeTest.(OraclePipeTest.java:50)
    at it.siag.oraclePipeTest.OraclePipeTestMain.main(OraclePipeTestMain.java:9)

    I used also the ojdbc6.jar driver and it doesn't works.
    Can you send me your used ojdbc6 driver?

    thx
    Michael

  • Follow / 27 Jun 2011 at 6:06pm

    Ok guys ...

    On google I found that
    for someone works on 11g and for someone other not
    for someone works on 10 and for someone other not

    I tested it on 10g and it works there!
    So I tested it on a other DB, on a 9.2.0.8 and it works there!
    Then I controlled which DB Version was the not working DB, it is a 9.2.0.6

    So for me on 9.2.0.8, 10 and 11g everything works on an ojdbc6 and ojdbc14 client

    and on a 9.2.0.6 it woks never on any jdbc driver :(

    So now I can say it's not your problem :D

    Is there anything else who can test it on other DBs?
    Have out there anyone an 9.2.0.6 to test it?

    thx
    Michael

  • Follow / 28 Jun 2011 at 8:01am

    I used the ojdbc6 that's contained in client, no magic. I also have an Oracle 9i, it's version 9.2.0.1. Maybe it won't work there, it's even an older version than yours.
    Anyway, this is an Oracle problem, so we cannot fix it.

    Tomas

  • Follow / 28 Jun 2011 at 8:46am

    yes .... it's an Oracle/Java problem, so sorry for spent your time :p

    I close this question with answered

    Thx
    Michael

    P.s. if you like you can test it on a 9.2.0.1 and let us know the result

  • Follow / 28 Jun 2011 at 11:49am

    For the sake of it, I tested your pipeline issue on that 9.2.0.1 Oracle db. I tested with ojdbc14...5...6. I got no error at all..........
    It seems that Oracle blesses me

    Tomas

  • Follow / 28 Jun 2011 at 11:55am
    did you test it with

    because with

    I got the error only on
  • Follow / 28 Jun 2011 at 1:00pm

    Yes I tried both