Higher is not better always..

Higher is not better always..

I had very tough time with Reports 10g today...

I was having below query in After Report trigger..

SELECT a.trx_number,rct.name ,rc.customer_name , hcsu.location
     into v_trx , v_trx_type, v_trx_cust , v_b2s
                FROM APPS.RA_CUSTOMER_TRX_ALL A , APPS.HZ_CUST_SITE_USES_ALL HCSU,
                   APPS.HZ_CUST_ACCT_SITES HCAS , APPS.RA_CUSTOMERS  RC , APPS.RA_CUST_TRX_TYPES RCT
                WHERE 1=1
                AND HCSU.SITE_USE_CODE = 'BILL_TO'
                AND HCAS.CUST_ACCOUNT_ID = A.BILL_TO_CUSTOMER_ID
                AND HCSU.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
                AND HCSU.SITE_USE_ID = A.BILL_TO_SITE_USE_ID
                AND RC.CUSTOMER_ID=A.BILL_TO_CUSTOMER_ID
                AND A.CUST_TRX_TYPE_ID = RCT.CUST_TRX_TYPE_ID
                and  TRX_NUMBER = trim(l_trx)
                and rownum < 2;


The trim function was NOT recognized by reports 10g compiler and neither it gave any ERROR message...

Running report caused error

REP-0736: There exist uncompiled program unit(s).

REP-1247: Report contains uncompiled PL/SQL.

This error led me to compile report several times - incremental as well as all..but to no use

I tried dynamic sql , native sql , porting logic to procedure but nothing worked..

Finally I got this error message when I opened the report with 6i and compiled invidual unit.. husshh!!!

I reached solution not before spending 8 hours and running report for whooping 52 times..  poor I!
No wonder Edison had to try 1830 times to invent bulb.. after all  he was doing it before anyone else..

Friends reports 10g can be better but sometimes being on higher version sucks!

Comma separated string

Using Analytic functions  - 25-Oct-18

pass string as  '1,2,4,5,678'

SELECT REGEXP_SUBSTR (:i_string, '[^,]+', 1, level)
      FROM dual
      CONNECT BY LEVEL <= LENGTH(regexp_replace(:i_string,'[^,]*'))+1;

---Below is longer route


Below code can be used to separate trx numbers from comma separated sting..

Definitely re-usable ...

set serveroutput on
DECLARE
  l_list  VARCHAR2(240) := '8345825,9999003,8233347';
  l_trx VARCHAR2(50);
 
  l_start number;
  l_len number;
  l_cnt number;
 
BEGIN
l_cnt := length(l_list) - length(replace(l_list, ',',''));

dbms_output.put_line(' Cnt '||l_cnt);

l_start:=0;  -- start from 0


for i in 1..l_cnt+1
loop

--l_len:= INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) ;

select INSTR(l_list,',',1,i) - decode(l_start,0,1,l_start) into l_len from dual;

if l_len < 0 then l_len:= 100; end if; -- for last trx

dbms_output.put_line( i ||'**St '||l_start);
dbms_output.put_line( i ||'**Ln '||l_len);

 l_trx := SUBSTR(l_list,l_start,l_len);

 dbms_output.put_line( i ||'    ' ||l_trx );

 l_start := INSTR(l_list,',',1,i)+1;
end loop;

end;
/