This discussion is archived
14 Replies Latest reply: Nov 15, 2012 5:28 AM by 972756 RSS

No more data to read from socket

RamaKrishna.CH Explorer
Currently Being Moderated
Hi all,
When i have executed below code i got "No more data to read from socket" message .but if i change the value 5100 to 5010 in select statement then code executed successfully. Can any one help me to overcome this problem.
Sample code:

DECLARE
TYPE td_empno is table of  number(10);
t_empno td_empno;
TYPE td_sal is table of  number;
t_sal td_sal;
l_empno NUMBER;
begin 
SELECT level bulk collect into t_empno from dual where level > 5000 connect by level <= 5100;

forALL  i in 1..t_empno.COUNT  
execute immediate
 'BEGIN
 insert into emp(empno,sal) values(:1,:2) RETURNING EMPNO into :3;
 insert into emp(empno,ename) values(:3,:4);
 END;' using t_empno(i),100,in out l_empno,'rama';
 end;
error message:
Error report:
No more data to read from socket
Note 1: I have a requirement to insert 2,00,000 records like show in above scenario.
Note 2: i disabled the primary key constraint on EMPNO column in my data base.

Edited by: Rama Krishna.CH on Nov 9, 2012 3:43 PM
  • 1. Re: No more data to read from socket
    AlbertoFaenza Expert
    Currently Being Moderated
    Hi Rama Krishna,

    Check this thread {thread:id=2447713}

    Regards.
    Al
  • 2. Re: No more data to read from socket
    Manik Expert
    Currently Being Moderated
    IS this what you are trying to insert?
    WITH t AS
            (    SELECT LEVEL lvl
                   FROM DUAL
                  WHERE LEVEL > 5000
             CONNECT BY LEVEL <= 5100)
    SELECT lvl,
           SUM (CASE WHEN MOD (lvl, 2) <> 0 THEN 100 END)
              OVER (PARTITION BY lvl ORDER BY 1)
              sal,
           MAX (CASE WHEN MOD (lvl, 2) = 0 THEN 'rama' END)
              OVER (PARTITION BY lvl ORDER BY 1)
              ename
      FROM t;
    Cheers,
    Manik
  • 3. Re: No more data to read from socket
    RamaKrishna.CH Explorer
    Currently Being Moderated
    i am trying to do like below example.
    My DB version.
    BANNER                                                         
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.1.0.5.0 - 64bi 
    PL/SQL Release 10.1.0.5.0 - Production                           
    CORE     10.1.0.5.0     Production                                         
    TNS for HPUX: Version 10.1.0.5.0 - Production                    
    NLSRTL Version 10.1.0.5.0 - Production
    I am getting the input data like below mention from client side.
    alerttype       mbrsep           emailaddr
    1                  123456           ram1@gmail.com
    1                  123457           ram2@gmail.com
    1                  123458           ram3@gmail.com
    1                  123459           ram4@gmail.com
    1                  154879           ram5@gmail.com
    1                  985478           ram6@gmail.com
    1                  123456           ram1@gmail.com
    1                  123457           ram2@gmail.com
    1                  123458           ram3@gmail.com
    1                  123459           ram4@gmail.com
    1                  154879           ram5@gmail.com
    1                  985478           ram6@gmail.com
    1                  123456           ram1@gmail.com
    1                  123457           ram2@gmail.com
    1                  123458           ram3@gmail.com
    1                  123459           ram4@gmail.com
    1                  154879           ram5@gmail.com
    1                  985478           ram6@gmail.com
    We have the two table structures like below mentioned.
    CREATE TABLE EMAILS
      (
        "IDNUM" NUMBER(12,0) NOT NULL ENABLE,    
        "MSGTEXT" CLOB,   
        "MBRSEP"          NUMBER(10,0),
        "TYPE"            CHAR(1),
        CONSTRAINT "EMAILS_PK" PRIMARY KEY ("IDNUM")
      );
    /
    
      CREATE TABLE EMIALSTO
      (   
        "EMAILSID" NUMBER(20,0) NOT NULL ENABLE,
        "MSGTO"   VARCHAR2(50 BYTE)   
      ) ;
    /
    
    
    CREATE SEQUENCE S01_EMAILS MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 75282 CACHE 20 NOORDER NOCYCLE ;
    /
    create or replace
    TRIGGER TI1_EMAILS 
    before insert ON EMAILS for each row
    declare
    v_next_val NUMBER;
    
    BEGIN
    
      IF (:new.idnum IS NULL) THEN
    
          SELECT S01_EMAILS.NEXTVAL
    
                 INTO v_next_val FROM DUAL;
    
          :new.idnum := v_next_val;
    
      END IF;
    END;
    /
    Below is the my select statement  and i am trying to do like below because i will get upto 4,00,000 records at a time.
    
    DECLARE
    TYPE td_mbrsep IS TABLE OF NUMBER(10); 
    t_mbrsep td_mbrsep;
    TYPE td_emailaddr IS TABLE OF VARCHAR2(50);
    t_emailaddr td_emailaddr;
    l_idnum NUMBER;
    BEGIN
    
    WITH T as (
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          )
       SELECT mbrsep,emailaddr 
       BULK COLLECT INTO t_mbrsep,t_emailaddr 
       FROM t WHERE alerttype = 1;
       
       
       FORALL  i IN 1..t_mbrsep.COUNT
             EXECUTE IMMEDIATE
              'BEGIN
                   INSERT INTO EMAILS( MSGTEXT, MBRSEP, TYPE)
                   VALUES(:1, :2, :3)
                   RETURNING IDNUM INTO :4;  
                   
                      INSERT INTO EMIALSTO(EMAILSID, MSGTO) VALUES (:4, :5);
               END;' 
                   USING 
                   'NO REPLY'
                   ,t_mbrsep(i)
                   ,'E'
                   ,IN OUT l_idnum
                   ,t_emailaddr(i)
                    ;
    END;
     Getting error:
    Error report:
    ORA-06544: PL/SQL: internal error, arguments: [82904], [], [], [], [], [], [], []
    ORA-06553: PLS-801: internal error [82904]
    ORA-06512: at line 51
    06544. 00000 -  "PL/SQL: internal error, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
    *Cause:    A pl/sql internal error occurred.
    *Action:   Report as a bug; the first argument is the internal error nuber.
    Please let me Know if you need any additional information.
    Please let me know how to fix or best way to do this.

    Edited by: Rama Krishna.CH on Nov 9, 2012 5:42 PM

    Edited by: Rama Krishna.CH on Nov 9, 2012 5:46 PM
  • 4. Re: No more data to read from socket
    971895 Journeyer
    Currently Being Moderated
    Please change to loop as like ---- FOR i IN 1..t_mbrsep.COUNt LOOP
    it will work...i tried...
  • 5. Re: No more data to read from socket
    RamaKrishna.CH Explorer
    Currently Being Moderated
    Yes, But i will get the 4,00,000 of records from client side. if i use for loop it takes the approximately 3 hours so that i used FORALL statement to improving performance.

    Please let me know any alternate ways.
  • 6. ORA-06544: PL/SQL: internal error, arguments: [82904], [], [], [], [], [],
    RamaKrishna.CH Explorer
    Currently Being Moderated
    hi all,

    Please help in this.
  • 7. Re: No more data to read from socket
    971895 Journeyer
    Currently Being Moderated
    have you tried what i posted... it will work.. there is no major different for and for all.
  • 8. Re: No more data to read from socket
    971895 Journeyer
    Currently Being Moderated
    TRY with bulk collect LIMIT 10K
  • 9. Re: No more data to read from socket
    jeneesh Guru
    Currently Being Moderated
    I think you cannot do that like how you tried..
    Your only option (not sure though) will be two FORALL statment . you can try putting some LIMIT in bulk collect also..
    DECLARE
      TYPE td_mbrsep IS TABLE OF NUMBER(10); 
      t_mbrsep td_mbrsep;
      TYPE td_emailaddr IS TABLE OF VARCHAR2(50);
      t_emailaddr td_emailaddr;
      t_idnum td_mbrsep;
    BEGIN
      WITH T as (
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          )
       SELECT mbrsep,emailaddr 
       BULK COLLECT INTO t_mbrsep,t_emailaddr 
       FROM t WHERE alerttype = 1; 
       
       FORALL  i IN 1..t_mbrsep.COUNT
              INSERT INTO EMAILS( MSGTEXT, MBRSEP, TYPE)
                   VALUES('NO REPLY', :i1, 'E')
                   using t_mbrsep(i)
                   RETURNING IDNUM BULK COLLECT INTO t_idnum; 
       forall i in 1..l_idnum.count 
                INSERT INTO EMIALSTO(EMAILSID, MSGTO) VALUES (:i2, :i3)
                   USING t_idnum(i),t_emailaddr(i);   
    END;
    between, cant you do this in plain SQL?
  • 10. Re: No more data to read from socket
    972756 Newbie
    Currently Being Moderated
    Forall can't be used with a begin-end block.
    After a FORALL statement you are forced to use a DML statemet.

    see: http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/forall_statement.htm
  • 11. Re: No more data to read from socket
    jeneesh Guru
    Currently Being Moderated
    Fabio P wrote:
    Forall can't be used with a begin-end block.
    After a FORALL statement you are forced to use a DML statemet.

    see: http://docs.oracle.com/cd/E14072_01/appdev.112/e10472/forall_statement.htm
    Are you sure?

    Check Re: Two DML operations in one FORALL?
  • 12. Re: No more data to read from socket
    veejays_user10302525-Oracle Journeyer
    Currently Being Moderated
    Rama,

    hope the below helps
    DECLARE
    TYPE td_mbrsep IS TABLE OF NUMBER(10); 
    t_mbrsep td_mbrsep;
    TYPE td_emailaddr IS TABLE OF VARCHAR2(50);
    t_emailaddr td_emailaddr;
    l_idnum NUMBER;
    BEGIN
     
    WITH T as (
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          UNION ALL
          SELECT 1 alerttype,123456 mbrsep ,'ram1@gmail.com'emailaddr FROM DUAL
           UNION ALL
          SELECT 1,123457,'ram2@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,123458,'ram3@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,123459,'ram4@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1,154879,'ram5@gmail.com' FROM DUAL
           UNION ALL
          SELECT 1 ,985478,'ram6@gmail.com' FROM DUAL
          )
       SELECT mbrsep,emailaddr 
       BULK COLLECT INTO t_mbrsep,t_emailaddr 
       FROM t WHERE alerttype = 1;
       
       
       FORALL  i IN 1..t_mbrsep.COUNT
                   INSERT ALL 
                   INTO EMAILS(MSGTEXT, MBRSEP, TYPE,IDNUM)
                   VALUES('NO REPLY',t_mbrsep(i),'E',fseq('N'))
                   INTO EMIALSTO(EMAILSID, MSGTO) VALUES (fseq('C'),t_emailaddr(i))
                   select null from dual;
    END;
    I have used a function to get value from sequence as using a sequence in multitable inserts directly causes it to skip values, you can test this yourself. Below is the function code. I created the tables as shared and executed the above code, it inserted data in both the tables. In case you find any discrepancy please share.

    Code for function
    CREATE OR REPLACE function fseq(pin varchar2)
    return number is
    begin
    if pin = 'N' then 
        return S01_EMAILS.NEXTVAL;
    elsif pin = 'C' then
       return    S01_EMAILS.currval;
    else
       return 0;   
    end if;    
    end;
  • 13. Re: No more data to read from socket
    BluShadow Guru Moderator
    Currently Being Moderated
    968892 wrote:
    have you tried what i posted... it will work.. there is no major different for and for all.
    ROTFLMAO.

    Seriously... you believe there's no major difference between using a FOR loop and a FORALL loop?

    I suggest you go and research the manuals and learn the difference.
  • 14. Re: No more data to read from socket
    972756 Newbie
    Currently Being Moderated
    Whow that's interesting!

    Also I see that in documentation of 10.2 this behave was documented ( http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/forall_statement.htm#i34324 )
    I would like to know why they changed the definition...

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points