9 Replies Latest reply: Dec 27, 2012 8:17 AM by 613913 RSS

    Need help with nested table

    613913
      Hi,

      I am using the following code which is failing with no data found error.
      While if i hardcode the same value the code works.
      create table test(input varchar2(10),result varchar2(10));
      
      insert into test values('4093163','SUCCESS')
      
      
      declare
      TYPE acctid_tab IS table  OF varchar2(40);
          acctid       acctid_tab := acctid_tab ();
      V_RESULT varchar2(20);
      v_input varchar2(40);
      i pls_integer;
      begin
       acctid := acctid_tab('4080402 4093163 64472');
       i := acctid.FIRST;
      WHILE i IS NOT NULL
       LOOP
      v_input :=   trim(substr(acctid(i),instr(acctid(i),' ',1),instr(acctid(i),' ',-1,2)));
      
      SElect result into v_result from test 
      where input = v_input;
            i := acctid.NEXT (i);
         END LOOP;
      
      END;
      
      ORA-01403: no data found
      ORA-06512: at line 14
      
      
      declare
      TYPE acctid_tab IS table  OF  varchar2(40);
          acctid       acctid_tab := acctid_tab ();
      V_RESULT varchar2(20);
      v_input varchar2(40);
      i pls_integer;
      begin
       acctid := acctid_tab('4080402 4093163 64472');
       i := acctid.FIRST;
      WHILE i IS NOT NULL
       LOOP
      v_input :=   trim(substr(acctid(i),instr(acctid(i),' ',1),instr(acctid(i),' ',-1,2)));
      
      SElect result into v_result from test 
      where input = '4093163';
            i := acctid.NEXT (i);
         END LOOP;
      
      END;
      
      PL/SQL procedure successfully completed.
      Please advise.
      Thanks in advance.
        • 1. Re: Need help with nested table
          981855
          not able to reproduce
          SQL> create table test(input varchar2(10),result varchar2(10));
          
          Table created.
          
          SQL> insert into test values('4093163','SUCCESS')
            2  ;
          
          1 row created.
          
          SQL> commit;
          
          Commit complete.
          
          SQL> ed
          Wrote file afiedt.buf
          
            1   declare
            2   TYPE acctid_tab IS table  OF varchar2(40);
            3       acctid       acctid_tab := acctid_tab ();
            4   V_RESULT varchar2(20);
            5   v_input varchar2(40);
            6   i pls_integer;
            7   begin
            8    acctid := acctid_tab('4080402 4093163 64472');
            9    i := acctid.FIRST;
           10   WHILE i IS NOT NULL
           11    LOOP
           12   v_input :=   trim(substr(acctid(i),instr(acctid(i),' ',1),instr(acctid(i),' ',-1,2)));
           13   dbms_output.put_line(v_input);
           14    select result into v_result from test where input = v_input;
           15   dbms_output.put_line(v_result );
           16    i := acctid.NEXT (i);
           17   END LOOP;
           18*  END;
           19  /
          4093163
          SUCCESS
          
          PL/SQL procedure successfully completed.
          • 2. Re: Need help with nested table
            NSK2KSN
            I executed the same anonymous blocks, am not getting any error
            • 3. Re: Need help with nested table
              APC
              Hmm, misintepreted the code.

              Edited by: APC on Dec 27, 2012 11:41 AM
              • 4. Re: Need help with nested table
                Karthick_Arp
                I guess you can just do
                declare
                   v_input varchar2(1000);
                begin
                   v_input := '4080402 4093163 64472';
                   
                   for i in (select input, result 
                               from test 
                              where input in (select regexp_substr(v_input, '[^ ]*', 1, level)
                                                from dual
                                              connect by level <= length(v_input) - length(replace(v_input, ' ')) + 1))
                   loop
                      dbms_output.put_line(i.input || ' ' || i.result);
                   end loop;
                end;
                /
                Or even better
                declare
                   v_input varchar2(1000);
                begin
                   v_input := '4080402 4093163 64472';
                   
                   for i in (select input, result
                               from test
                              where ' ' || v_input || ' ' like '% ' || input || ' %')
                   loop
                      dbms_output.put_line(i.input || ' ' || i.result);
                   end loop;
                end;
                /
                 
                • 5. Re: Need help with nested table
                  Suman Rana
                  Hi.. your code looks good... I think u should use COMMIT after inserting the new row and then run the anonymous block.. whenever u executed the block second time inserted row was somehow committed to DB, so it did not throw any error.
                  • 6. Re: Need help with nested table
                    Veejays.User10302525-Oracle
                    if all is being done from same session should not matter, however, we are not sure how the OP did it.
                    • 7. Re: Need help with nested table
                      613913
                      I guess the following part is causing the issue.
                      = trim(substr(('4080402 4093163 64472'),instr(('4080402 4093163 64472'),' ',1),instr(('4080402 4093163 64472'),' ',-1,2)))
                      Is there any better way to segregate the 3 values.I need to use each of these in subsequent SQLs.

                      Edited by: user610910 on Dec 27, 2012 5:49 AM
                      • 8. Re: Need help with nested table
                        Solomon Yakobson
                        user610910 wrote:
                        Is there any better way to segregate the 3 values.I need to use each of these in subsequent SQLs.
                        Question is why you declare a nested table type and using it in a weird way? Why you are issuing:
                        acctid := acctid_tab('4080402 4093163 64472');
                        which always leaves you with one element nested table. Why not:
                        acctid := acctid_tab('4080402,4093163,64472');
                        Then you have nested table with 3 elements and there is no need to split.

                        SY.
                        • 9. Re: Need help with nested table
                          613913
                          Thanks.This is becuase I have to manipulate with sets of 3 data at a time.

                          I tried by creating a nested table type of record type record_tp(rec1 VARCHAR2(10),rec2 VARCHAR2(10),rec3 VARCHAR2(10))
                          but was not able initialize as acctid := acctid_tab(record_tp('4080402','4093163','64472'))