Hi Everyone,
I am a newbie and still learning the basics of Oracle PLSQL. For last 2-3 days I am trying to write a code where I have to print the error records of a file. Below is the code where values are hard-coded and working correct and I need to replicate the code to be more dynamic.
Working code: (Please ignore the declaration part for this)
CURSOR get_txn_all_sum
IS
SELECT SUM(UD0020),
SUM(UD0022),
SUM(UD0025),
UD0004,
UD0005,
UD0006,
UD0019
FROM esadaptor_fwd_txn_ip
WHERE input_file_name = p_file_name
AND di_execution_number = p_di_exec_no
GROUP BY UD0004,
UD0005,
UD0006,
UD0019
HAVING SUM(UD0020) <> 0
OR SUM(UD0022) <> 0
OR SUM(UD0025) <> 0;
FOR rec IN get_txn_all_sum
LOOP
DBMS_OUTPUT.PUT_LINE(rec.UD0004||' '||rec.UD0005||' '||rec.UD0006||' '||rec.UD0019);
END LOOP;
My Code:
set serveroutput on;
declare
type rc is ref cursor;
l_cursor rc;
TYPE get_all_sum IS RECORD (
l_delimiter VARCHAR2(20) := ' ',
l_ud_val VARCHAR2(50) ,
l_ud_data VARCHAR2(1000) ,
l_ud_data_display VARCHAR2(255),
l_ud_val_sum VARCHAR2(70) ,
l_ud_val_add VARCHAR2(70),
l_sum_ud_val_stmt VARCHAR2(70) ,
l_ud_val_having VARCHAR2(100),
l_having_stmt VARCHAR2(255),
l_uds VARCHAR2(100),
l_detail_data VARCHAR2(500) ,
l_select_stmt VARCHAR2(30000),
l_group_by VARCHAR2(70) );
get_sum get_all_sum ;
l_file_name VARCHAR2(100);
begin
l_file_name := 'abc.txt' ;
--
SELECT
listagg(ud_id,',') within group(order by ud_id) as UD_ID_SUM
INTO get_sum.l_ud_val
FROM codrm_ssi_chart_def
where value_type = 'Value'
and bal_seg_flag = 'Y'
and chart_id = 'CCLGL_JE00' ;
--
select replace(get_sum.l_ud_val,'U','SUM(U') into get_sum.l_ud_val_sum from dual ;
--
SELECT replace(get_sum.l_ud_val_sum,',','), ') into get_sum.l_ud_val_add from dual;
--
get_sum.l_sum_ud_val_stmt := ''||get_sum.l_ud_val_add||')' ;
--
DBMS_OUTPUT.PUT_LINE(get_sum.l_sum_ud_val_stmt);
--
SELECT replace(get_sum.l_ud_val_sum,',',') <> 0 OR ') into get_sum.l_ud_val_having from dual;
--
get_sum.l_having_stmt := ''||get_sum.l_ud_val_having||')' ;
--
DBMS_OUTPUT.PUT_LINE(get_sum.l_having_stmt);
--
SELECT
listagg(ud_id,get_sum.l_delimiter) within group(order by ud_id) as UD_ID_SUM
INTO get_sum.l_ud_data
FROM codrm_ssi_chart_def
where value_type = 'Data'
and bal_seg_flag = 'Y'
and chart_id = 'CCLGL_JE00' ;
--
DBMS_OUTPUT.PUT_LINE(get_sum.l_ud_data);
--
SELECT
listagg(ud_id,',') within group(order by ud_id) as UD_ID_SUM
INTO get_sum.l_group_by
FROM codrm_ssi_chart_def
where value_type = 'Data'
and bal_seg_flag = 'Y'
and chart_id = 'CCLGL_JE00' ;
--
DBMS_OUTPUT.PUT_LINE(get_sum.l_group_by);
--
get_sum.l_select_stmt := 'SELECT '||get_sum.l_sum_ud_val_stmt||','|| replace(get_sum.l_ud_data,get_sum.l_delimiter,'||') || ' as PRINT_DATA' || ',' || get_sum.l_group_by||' from conversion_ip'||CHR(10)
||' WHERE input_file_name = '||q'[']'||l_file_name||q'[']'||CHR(10)
||' AND di_execution_number = '||20011||CHR(10)
||' GROUP BY '||get_sum.l_group_by|| ',' || replace(get_sum.l_ud_data,get_sum.l_delimiter,'||') || CHR(10)
||' HAVING '||get_sum.l_having_stmt || ' <> 0';
--
DBMS_OUTPUT.PUT_LINE(get_sum.l_select_stmt);
--
open l_cursor for get_sum.l_select_stmt;
loop
fetch l_cursor into
get_sum.l_sum_ud_val_stmt,
get_sum.l_ud_data,
get_sum.l_group_by;
end loop;
end;
/
Below is the error which i am getting:
Error report:
ORA-00932: inconsistent datatypes: expected - got -
ORA-06512: at line 64
00932. 00000 - "inconsistent datatypes: expected %s got %s"
*Cause:
*Action:
Please suggest. Any help would be highly appreciated.