Topics

1

PSOUG / Logic to rebuild a string having n no of '&' and inserting into a table.

« on: March 28, 2009, 12:52:28 am »

Gurus,
Need your help ...
I need to rebuild a string value programmatically which has '&' and insert the same into another table.
Now the string may contain n no of '&' occurrances.
Hence,can you suggest/modify the existing code
so that it can be used in a general way.
Assume creating arrays permission is not granted to the schema.

Below is snippet of creation script:
Create table abc (col1 number,col2 varchar2(100));
Insert into abc values (1,‘filename=2GB_Final_Test.zip’||chr(38)||’filename2=280648119’||chr(38)||’filename3=null’);
Insert into abc values (2,’filename=c10k-k4u2p10-mz.120-30.S3.bin’||chr(38)||’filename2=268438016’||chr(38)||’filename3=null’);
Insert into abc values (3,’filename=oracle.exe’||chr(38)||’filename2=12345’||chr(38)||’ filename3=23456’);
Insert into abc values (4,’filename=c12kprp-boot-mz.120-33.S1.bin’||chr(38)||’filename2=278725201’||chr(38)||’filename3=null’);
Insert into abc values (5,’filename=oracle.exe’||chr(38)||’filename2=123455’||chr(38)||’filename3=23456’||chr(38)||’filename4=test’);

My Code:
declare
  lv_count number;
  i number:=1;
  lv_len_str number;
  lv_great number:=0;
  lv_set_first varchar(1000):=null;
  lv_set_curr varchar2(1000):=null;
  lv_prev varchar2(1000):=null;
  lv_set varchar2(1000):=null;
begin
  select length(col2) into lv_len_str from abc where col1=5;
  for a in 1..lv_len_str
  loop
    select instr(col2,'&',1,i) into lv_count from abc where col1=5;
    exit when nvl(lv_count,0)=0;
    i:=i+1;
  end loop;
  lv_great:=(i-1);
  --dbms_output.put_line ('Highest occurrance : '||lv_great);
  i:=1;
  select substr(col2,1,instr(col2,'&',1,1)-1) into lv_set from abc where col1=5;
  dbms_output.put_line ('First Set : '||lv_set);
  lv_set_first:=lv_set||chr(38);
  i:=1;
  for a in 1..lv_great
  loop
    dbms_output.put_line ('Value of i here : '||i);
    select substr(col2,instr(col2,'&',1,i)+1,(instr(col2,'&',1,i+1)-instr(col2,'&',1,i)-1)) into lv_set_curr from abc where col1=5;
    -- dbms_output.put_line (lv_set);
    i:=i+1;
    lv_prev:=lv_set_first;
    lv_set:=lv_prev||chr(38)||lv_set_curr;
    dbms_output.put_line (lv_set);
    exit when i>lv_great;
  end loop;
  --dbms_output.put_line ('Overall set : '||lv_set);
end;
/
First Set : filename=oracle.exe
Value of i here : 1
filename=oracle.exe&&filename2=123455
Value of i here : 2
filename=oracle.exe&&filename3=23456
Value of i here : 3
filename=oracle.exe&&

PL/SQL procedure successfully completed.

But desired output is :
filename=oracle.exe&filename2=123455&filename3=23456&filename4=test