4 Replies Latest reply: Aug 27, 2014 2:41 PM by JonWat RSS

    Query and Update work, but not Merge

    JonWat

      Does anyone have any idea why the following does not work? (Version 11.2.0.1.0)

       

      create table test(line_nbr number(3),source_line varchar2(100),proc_belong varchar2(40));

       

      insert into test(line_nbr,source_line) values (1,'proc1   procedure');

      insert into test(line_nbr,source_line)  values (2,'  other stuff');

      insert into test(line_nbr,source_line)  values (3,'proc2   procedure');

      insert into test(line_nbr,source_line)  values (4,'  other stuff for proc2');

      insert into test(line_nbr,source_line)  values (5,'  other stuff for proc2');

      insert into test(line_nbr,source_line)  values (6,'proc3   procedure');

      insert into test(line_nbr,source_line)  values (7,'  other stuff for proc3');

      insert into test(line_nbr,source_line)  values (8,'  other stuff for proc3');

       

      A straight query works:

       

      select l.line_nbr,z.procname

        from test l

        inner join

          (select line_nbr start_line,lead(line_nbr) over ( order by line_nbr) - 1 to_line,procname

          from

            (select regexp_substr(source_line,'([^ ]*)',1,1,null,1) procname,line_nbr

            from test

            --where contains(source_line,'procedure',1) > 1 and substr(source_line,1,1) is not null

            where lower(source_line) like '%procedure%' and substr(source_line,1,1) is not null      

            )

          )  z on l.line_nbr between z.start_line and nvl(z.to_line,9999)

        ;

       

      An update based on that query works:

       

      update test t

      set proc_belong =

      (select z.procname

        from test l

        inner join

          (select line_nbr start_line,lead(line_nbr) over ( order by line_nbr) - 1 to_line,procname

          from

            (select regexp_substr(source_line,'([^ ]*)',1,1,null,1) procname,line_nbr

            from test

            --where contains(source_line,'procedure',1) > 1 and substr(source_line,1,1) is not null

            where lower(source_line) like '%procedure%' and substr(source_line,1,1) is not null

            )

          )  z on l.line_nbr between z.start_line and nvl(z.to_line,9999)

        where l.line_nbr = t.line_nbr 

        );

       

      But a merge based on the same query fails with "No more data to read from socket":

       

      merge into test t using 

      (select l.line_nbr,z.procname

        from test l

        inner join

          (select line_nbr start_line,lead(line_nbr) over ( order by line_nbr) - 1 to_line,procname

          from

            (select regexp_substr(source_line,'([^ ]*)',1,1,null,1) procname,line_nbr

            from test

            --where contains(source_line,'procedure',1) > 1 and substr(source_line,1,1) is not null

            where lower(source_line) like '%procedure%' and substr(source_line,1,1) is not null

            )

          )  z on l.line_nbr between z.start_line and nvl(z.to_line,9999)

        ) q

      ON (t.line_nbr = q.line_nbr)

      when matched then update set t.proc_belong = q.procname;

       

      The original problem was also using a contains() with a Context index, but I took that out and it still fails, so that is not the problem.

       

      Thanks,

       

      Jon

        • 1. Re: Query and Update work, but not Merge
          Frank Kulash

          Hi, Jon,

           

          Sorry, I can't reproduce the results on any version at hand.  In either 11.1.0.6.0, or 11.2.0.3.0, the MERGE statement merges 8 rows without error.  I'll be at an 11.2.0.1.0 database in about 5 hours; I'll test it then.

           

          You shouldn't be able to cause that error if you tried.  I suspect there's a setup issue on your machine, or a bug.  Open a Service Request with Oracle.

           

          Excellent post, by the way.

          • 2. Re: Query and Update work, but not Merge
            Frank Kulash

            Hi, Jon,

             

            JonWat wrote:

            ...

                  --where contains(source_line,'procedure',1) > 1 and substr(source_line,1,1) is not null

                  where lower(source_line) like '%procedure%' and substr(source_line,1,1) is not null

            ...

            This has nothing to do with your problem, but do you really need the NOT NULL test? 

             

            If lower (source_line) is like '%procedure%', then you know that source_line is not NULL, and therefore the 1st character of source_line won't be NULL, and so the WHERE clause will be TRUE.

            If lower (source_line) is not like '%procedure%', then the WHERE clause will be FALSE, regardless of whether substr (source_line, 1, 1) is NULL or not.

             

            I don't know how CONTAINS works, but I suspect it won't return a positive number if source_line is NULL.  I'm guessing you don't need the test "substr (source_line, 1, 1) IS NOT NULL".  If you do, "source_line IS NOT NULL" would work as well.

            • 3. Re: Query and Update work, but not Merge
              JonWat

              Hi Frank,

               

              The lines I'm interested in have the procedure name starting in the first column and the word PROCEDURE after any amount of white space. There are some other places where the procedure name does not start in the first column; it's in a sort of prototype section, and those ones I want to ignore.

               

              Meanwhile I tried the merge on oracle xe at home (11.2.0.2.0) and it worked fine, so either it is a problem with that very specific version or there is some other problem local to my work environment.

               

              Thanks,

               

              Jon

              • 4. Re: Query and Update work, but not Merge
                JonWat

                Seems to have just been a "glitch". Even when I simplified the merge to the completely trivial it still gave the "no data to read from socket" error. If I copied to a second table (test2), it worked. When I deleted the original test and recreated it, it worked. I guess there was a totally corrupt plan cached that it kept on trying to use.