14 Replies Latest reply: Nov 15, 2012 7:28 AM by 972756 RSS

    No more data to read from socket

    RamaKrishna.CH
      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
          Hi Rama Krishna,

          Check this thread {thread:id=2447713}

          Regards.
          Al
          • 2. Re: No more data to read from socket
            Manik
            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
              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
                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
                  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.
                  • 7. Re: No more data to read from socket
                    971895
                    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
                      TRY with bulk collect LIMIT 10K
                      • 9. Re: No more data to read from socket
                        jeneesh
                        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
                          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
                            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
                              vijayrsehgal-Oracle
                              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
                                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
                                  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...