Forum Stats

  • 3,851,415 Users
  • 2,263,974 Discussions
  • 7,904,701 Comments

Discussions

Index by table - exception

862489
862489 Member Posts: 135
edited Mar 18, 2014 6:36AM in SQL & PL/SQL

Hi,

I'm trying to pass csv as varchar2 to a proc, which in turn puts it into Index by table, where in I am getting no data found exception, when there is blank fields without white spaces (,,,,)

Pls suggest how to overcome this error in the array.

Thanks

Tagged:

Answers

  • 862489
    862489 Member Posts: 135

    Pls suggest how to introduce space between the comma delimiter. ie to introduce null to overcome this exception

    Many thanks

  • SKP
    SKP Member Posts: 844 Gold Badge
    edited Mar 18, 2014 4:42AM

    Use TRIM function to remove the white space!

    Oracle/PLSQL: LTRIM Function

  • 862489
    862489 Member Posts: 135

    I need to introduce a white space between delimiter (,)

    Like this a,b,c,d  if c and d is null  - it is displaying as a,b,, I need to have blank/null for this as - a,b, ,

    Thanks

  • Sergei Krasnoslobodtsev
    Sergei Krasnoslobodtsev Member Posts: 495 Silver Badge
    edited Mar 18, 2014 4:50AM

    Hi.

    create or replace type my_tab is table of varchar2(100);
    /
    declare
        v_str varchar2(100) := '1,2,,3, 4 ,5,, 6 ,7,';
        v_tab  my_tab;
        procedure proc1(p_collect my_tab )
        as
        begin
             for z in 1 .. p_collect.count
             loop
                 dbms_output.put_line(p_collect(z));
             end loop;    
        end ;        
    
    begin
         select
           trim(regexp_substr(v_str,'[^,]+',1,level))
         bulk collect into v_tab  
         from dual
         connect by trim(regexp_substr(v_str,'[^,]+',1,level)) is not null;
         proc1(v_tab);
         
         v_tab.delete(5);
         
         if not v_tab.exists(5) then 
             dbms_output.put_line('No data found');
         end if;    
         
         
    end ;
    /
    
  • SKP
    SKP Member Posts: 844 Gold Badge

    Check the below query which will insert a white space ::

    SELECT regexp_replace('a,b,c,,,,d,,f','(\,{1})(\,{1})',' \1 \2') FROM dual;

This discussion has been closed.