Home » SQL & PL/SQL » SQL & PL/SQL » Buffer String too small(2 Merged) (Oracle 10g)
| Buffer String too small(2 Merged) [message #505922] |
Thu, 05 May 2011 13:33  |
 |
catchcv
Messages: 10
Registered: May 2011
|
Junior Member |
|
|
Hi,
Could someone help me when i try to execute the below line in a dynamic query im facing buffer string too small error.
I have declared
temp_col as long
dat as utl_file.file_type;
utl_file.put_raw(dat,utl_i18n.string_to_raw('||temp_col||',''WE8ISO8859P15''));
im building an dynamic SQL with 500 columns from a table for extraction purpose, but this doesn't give any trouble till i use 250 columns for extraction.
Thanks in advance
|
|
|
|
|
|
|
|
|
|
| Re: Buffer String too small [message #505933 is a reply to message #505930] |
Thu, 05 May 2011 14:06  |
|
catchcv
Messages: 10
Registered: May 2011
|
Junior Member |
|
|
Hi,
Below is my Dynamic SQL block
*********Declarations***************
TYPE ty_cname is record (col_names LONG);
TYPE ty_colnames iS TABLE OF ty_cname;
var_colnames ty_colnames;
--rel3.9
v_xlscolnames ty_colnames;
--rel3.9
v_col_val LONG;
v_col_val1 LONG;
v_col_val2 LONG;
v_colnam_cnt NUMBER;
proc_failure EXCEPTION;
v_colseparator scb_mlp_csv_config_param.col_separator%TYPE;
v_recordseparator scb_mlp_csv_config_param.record_separator%TYPE;
v_columnnames LONG;
v_count scb_mlp_csv_config.record_count%TYPE;
v_viewname VARCHAR2(100);
v_header_format LONG; -- release 3
v_sysdate VARCHAR2(10);
v_footerformat scb_mlp_csv_config_param.footer_format%TYPE;
v_filename VARCHAR2(100); --siva
v_column VARCHAR2(2);
v_headflg VARCHAR2(2);
v_footflg VARCHAR2(2);
v_excelflag CHAR(1);
v_dataexcel utl_file.file_type;
v_excelclose VARCHAR2(5000);
v_count_dmy NUMBER;
v_count_dmyf NUMBER;
v_datagg VARCHAR2(100);
v_qury_sum VARCHAR2(100);
V_COL1 VARCHAR2(100);
V_COL2 VARCHAR2(100);
V_COL3 VARCHAR2(100);
v_cnt1 number;
v_cube_filename VARCHAR2(100);
*********Extraction Header Part to generate column header********
IF p_fileext = 'xls' THEN
-- dbms_output.put_line('gggggggg:'||p_repgroup||'_'||p_filename||'.'||p_fileext||':'||v_header_format);
UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('<Row ss:Index="4">','WE8ISO8859P15'));
utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('</Row>','WE8ISO8859P15'));
ELSE
utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
END IF;
*******Extraction Contents Actual Data Generation***************
v_SQL:='DECLARE v_dat utl_file.file_type;
v_cnt number := 0;
v_index number :=4;
v_ws number := 0;
TYPE ty_vtmp is table of '||v_viewname||'%rowtype;
v_tmp ty_vtmp;
TYPE rc_type IS REF CURSOR;
c1 rc_type;
v_sql_cur long;
buffer_small EXCEPTION;
PRAGMA EXCEPTION_INIT(buffer_small, -6502);
begin
v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'', ''32767'' );
v_sql_cur:=''select /*+PARALLEL(' || v_viewname || ', 3)*/ * from '||v_viewname || ''';
OPEN c1 FOR v_sql_cur;
loop fetch c1 bulk collect into v_tmp limit 5000;
v_cnt := v_cnt + v_tmp.COUNT;
exit when v_tmp.count=0;
if '''||v_excelflag||''' is null THEN
for i in 1 .. v_tmp.count
loop
begin
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
exception
WHEN buffer_small THEN
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15'')) ;
END;
END LOOP;
elsif '''||v_excelflag||''' = ''T'' THEN
for i in 1 .. v_tmp.count
loop
begin
v_index := v_index +1 ;
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Row ss:Index="'||'''||v_index||'''||'">'',''WE8ISO8859P15''));
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''</Row>'',''WE8ISO8859P15''));
--utl_file.fclose(v_dat);
IF v_INDEX = 65301 THEN
v_ws := v_ws + 1;
--v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'');
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw('''||v_excelclose||''',''WE8ISO8859P15''));
--UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_1">'',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_'||'''||v_ws||'''||'">'',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Table ss:ExpandedColumnCount="65335" ss:ExpandedRowCount="65335" x:FullColumns="1" x:FullRows="1">'',''WE8ISO8859P15''));
--utl_file.fclose(v_dat);
v_index := 0;
END IF;
--utl_file.fclose(v_dat);
end;
end loop;
end if;
:1 := v_cnt;
end loop;
if utl_file.is_open(v_dat) then
utl_file.fclose(v_dat);
end if;
end;';
The blow is the execution of the package
SQL> exec scb_mlp_pack_extractionprd.extraction(7,'PRO_114_PLC','31122010');
begin scb_mlp_pack_extractionprd.extraction(7,'PRO_114_PLC','31122010'); end;
ORA-20002: process failed. Refer log table for further information: while writing the dat file :-6502:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TRGV110.SCB_MLP_PACK_EXTRACTIONPRD", line 2056
ORA-06512: at line 1
|
|
|
|
|
|
|
|
|
|
| Re: Buffer String too small [message #505941 is a reply to message #505940] |
Thu, 05 May 2011 14:46 |
|
catchcv
Messages: 10
Registered: May 2011
|
Junior Member |
|
|
Hi Swan,
FUNCTION dummyfootergeneration(
p_repgroup scb_mlp_csv_config_param.rep_group%TYPE,
p_filename IN scb_mlp_csv_config.filename%TYPE,
p_source_system in VARCHAR2,
p_filepointer in utl_file.file_type,
p_rec_seperator in scb_mlp_csv_config_param.record_separator%type,
p_cntry_code in varchar2,
p_count_dmy IN OUT NUMBER
) RETURN NUMBER IS
v_filepointer utl_file.file_type;
v_dummyfooterformat varchar2(4000);
l_stmt_date varchar2(10);
l_trans_code scb_wb_e2e_tlm.trans_code%type;
l_cnt number:=0;
l_cnt_dmy NUMBER:=0;--postrel5.0
l_cnt_dmy_f NUMBER:=0;
p_final_count NUMBER:=0;--postrel5.0
l_stg VARCHAR2(30);
l_trn_cd VARCHAR2(15);
l_tlm_sql varchar2(4000):='select count(*) from scb_wb_e2e_tlm where stage=:1 AND SOURCE_SYSTEM=:2 and message_feed_id =:3 and trans_code =:4';
l_stmt_date_sql varchar2(1000):='select (to_char(to_date(reporting_date,''ddmmyyyy''),''dd/mm/yyyy'')) from scb_mlp_reportingdate ';
cursor cc (ctry_code varchar2) is select country_code , cust_class from scb_wb_cust_class where country_code =ctry_code
and cust_class between 98 and 970;
l_msg_feed_id varchar2(100);
l_err_MSG varchar2(1000);
others exception;
l_stage scb_wb_e2e_tlm.stage%type;
BEGIN
v_filepointer :=P_filepointer ;
execute immediate l_stmt_date_sql into l_stmt_date ;
IF p_repgroup = 'E2E_TLM_BCRS_STAGING' then
l_stage := 'BCRS_STAGING';
ELSIF p_repgroup ='E2E_TLM_FDWS_STAGING' then
l_stage := 'FERMAT_STAGING';
ELSIF p_repgroup ='E2E_TLM_BCRS_RESULTS' then
l_stage := 'BCRS_RESULTS';
END IF;
--dbms_output.put_line(' File name ' || p_filename );
--dbms_output.put_line(' p_cntry_code ' || p_cntry_code );
--dbms_output.put_line(' file pointer ' || V_filepointer );
for rec in cc ( p_cntry_code )
LOOP
l_cnt:=0;
begin
l_msg_feed_id:='RMI_'||rec.country_code||'_'||rec.cust_class;
IF p_repgroup = 'E2E_TLM_BCRS_STAGING' THEN
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCSS';
if l_cnt = 0 THEN
l_cnt_dmy_f := l_cnt_dmy_f+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCSS'||'|'||0||'|'||'BCSS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
ELSIF p_repgroup = 'E2E_TLM_FDWS_STAGING' then
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'FDWS';
if l_cnt = 0 THEN
l_cnt_dmy_f := l_cnt_dmy_f+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'FDWS'||'|'||0||'|'||'FDWS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'FDWR';
if l_cnt = 0 THEN
l_cnt_dmy_f := l_cnt_dmy_f+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'FDWR'||'|'||0||'|'||'FDWR'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
ELSIF p_repgroup = ('E2E_TLM_BCRS_RESULTS') then
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCRS';
if l_cnt = 0 THEN
l_cnt_dmy_f := l_cnt_dmy_f+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCRS'||'|'||0||'|'||'BCRS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCRR';
if l_cnt = 0 THEN
l_cnt_dmy_f := l_cnt_dmy_f+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCRR'||'|'||0||'|'||'BCRR'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
END IF;
EXCEPTION
WHEN OTHERS THEN
l_err_msg := 'while writing DUMMY FOOTER FOR REP GROUP :' ||P_REPGROUP || ' File Name ' || p_filename ||SQLCODE||':'||SQLERRM;
--dbms_output.put_line('Dmmy Error 1 ' || l_err_msg);
pack_install.log_write('E','F','SCB_MLP_PACK_EXTRACTION',v_step,l_err_msg);
RETURN 1;
end;
END loop;
-- Writting dummy footer for customer class <=98 and >=970
l_msg_feed_id:='RMI_'||p_cntry_code||'_000';
IF p_repgroup = 'E2E_TLM_BCRS_STAGING' then
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCSS';
if l_cnt = 0 then
l_cnt_dmy := l_cnt_dmy+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCSS'||'|'||0||'|'||'BCSS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
ELSIF p_repgroup = 'E2E_TLM_FDWS_STAGING' then
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'FDWS';
if l_cnt = 0 THEN
l_cnt_dmy := l_cnt_dmy+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'FDWS'||'|'||0||'|'||'FDWS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'FDWR';
if l_cnt = 0 THEN
l_cnt_dmy := l_cnt_dmy+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'FDWR'||'|'||0||'|'||'FDWR'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
ELSIF p_repgroup = ('E2E_TLM_BCRS_RESULTS') then
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCRS';
if l_cnt = 0 THEN
l_cnt_dmy := l_cnt_dmy+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCRS'||'|'||0||'|'||'BCRS'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
execute immediate l_tlm_sql INTO L_CNT using L_STAGE , p_source_system ,l_msg_feed_id,'BCRR';
if l_cnt = 0 THEN
l_cnt_dmy := l_cnt_dmy+1;
v_dummyfooterformat:=l_stmt_date||'|'||l_stmt_date||'||'||l_msg_feed_id||'|'||'BCRR'||'|'||0||'|'||'BCRR'||'|||||||||||'||0||'||||'||p_source_system||'||||||||||||||||||||||||||||||||||||||'||p_rec_seperator ;
utl_file.put_raw(V_filepointer,utl_i18n.string_to_raw(v_dummyfooterformat,'WE8ISO8859P15'));
else
null;
end if;
END IF;
utl_file.fclose(v_filepointer);
--dbms_output.put_line(' Dummy footer completed successfully ' );
--RETURN 0;
--Added in POSTREL5.0
p_count_dmy := l_cnt_dmy_f + l_cnt_dmy;
RETURN p_count_dmy;
COMMIT;
EXCEPTION
WHEN OTHERs THEN
l_err_msg:=sqlerrm;
-- dbms_output.put_line('Dmmy Error 2 ' || l_err_msg);
-- pack_install.log_write('I','F','SCB_MLP_PACK_EXTRACTION',v_step,v_sysdate||'_'||v_no||' Directory created inside =>'||p_dir_path||'/'||p_repgroup);
pack_install.log_write('E','F','SCB_MLP_PACK_EXTRACTION',v_step,l_err_msg);
utl_file.fclose(v_filepointer);
RETURN 1;
--dbms_output.put_line( ' Error write dummy_footer ');
END;
FUNCTION datafilegeneration(
p_oracle_directory scb_mlp_csv_config_param.oracle_directory%TYPE,
p_header_format scb_mlp_csv_config_param.header_format%TYPE,
p_footer_format scb_mlp_csv_config_param.footer_format%TYPE,
p_repgroup scb_mlp_csv_config_param.rep_group%TYPE,
p_filename IN scb_mlp_csv_config.filename%TYPE,
p_fileext scb_mlp_csv_config_param.file_ext%TYPE,
p_worksheetname scb_mlp_csv_config.worksheet_name%TYPE,
p_workbooktype IN VARCHAR2,
p_query scb_mlp_csv_config.query%TYPE,
p_colseparator scb_mlp_csv_config_param.col_separator%TYPE,
p_recordseparator scb_mlp_csv_config_param.record_separator%TYPE,
p_count OUT NUMBER,
p_err_msg OUT VARCHAR2,
p_src_system in VARCHAR2,
p_country_code in varchar2,
p_aggate IN VARCHAR2
) RETURN NUMBER IS
TYPE ty_cname is record (col_names LONG);
TYPE ty_colnames iS TABLE OF ty_cname;
var_colnames ty_colnames;
--rel3.9
v_xlscolnames ty_colnames;
--rel3.9
v_col_val LONG;
v_col_val1 LONG;
v_col_val2 LONG;
v_colnam_cnt NUMBER;
proc_failure EXCEPTION;
v_colseparator scb_mlp_csv_config_param.col_separator%TYPE;
v_recordseparator scb_mlp_csv_config_param.record_separator%TYPE;
v_columnnames LONG;
v_count scb_mlp_csv_config.record_count%TYPE;
v_viewname VARCHAR2(100);
v_header_format LONG; -- release 3
v_sysdate VARCHAR2(10);
v_footerformat scb_mlp_csv_config_param.footer_format%TYPE;
v_filename VARCHAR2(100); --siva
v_column VARCHAR2(2);
v_headflg VARCHAR2(2);
v_footflg VARCHAR2(2);
v_excelflag CHAR(1);
v_dataexcel utl_file.file_type;
v_excelclose VARCHAR2(5000);
v_count_dmy NUMBER;
v_count_dmyf NUMBER;
v_datagg VARCHAR2(100);
v_qury_sum VARCHAR2(100);
V_COL1 VARCHAR2(100);
V_COL2 VARCHAR2(100);
V_COL3 VARCHAR2(100);
v_cnt1 number;
v_cube_filename VARCHAR2(100);
v_vc_cude varchar2(30);
--v_tlm_sql varchar2(1000):='select source_system from scb_mlp_csv_config where rep_group=:1 and filename=:2' ;
--v_src_system scb_mlp_csv_config.source_system%type;
v_dummy_ft_ret_val number:=0;
others exception;
BEGIN
--dbms_output.put_line('Hemu Inside datafile file 000');
v_sysdate := TO_CHAR(SYSDATE,'ddmmyyyy');
---- starts to change the repdate format
vsys_YYYY := to_char(to_date(v_sysdate,'DDMMYYYY'),'YYYY');
vsys_YY := to_char(to_date(v_sysdate,'DDMMYYYY'),'YY');
vsys_MON := to_char(to_date(v_sysdate,'DDMMYYYY'),'MON');
vsys_MM := to_char(to_date(v_sysdate,'DDMMYYYY'),'MM');
vsys_DD := to_char(to_date(v_sysdate,'DDMMYYYY'),'DD');
v_header_format := p_header_format;
v_footerformat := p_footer_format;
IF INSTR(v_header_format, '#SYS') > 0 THEN -- File name contains DDMMYYYY
v_header_format := REPLACE(v_header_format,'#SYSDDMMYYYY#', vsys_dd || vsys_mm || vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSDD-MM-YYYY#', vsys_dd ||'-'|| vsys_mm ||'-'|| vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSDD/MM/YYYY#', vsys_dd ||'/'|| vsys_mm ||'/'|| vsys_yyyy);--Added on july 14
v_header_format := REPLACE(v_header_format,'#SYSYYYYMMDD#', vsys_yyyy || vsys_mm || vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSYYYY-MM-DD#', vsys_yyyy ||'-'|| vsys_mm || '-'|| vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSDDMONYYYY#', vsys_dd || vsys_mon || vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSDD-MON-YYYY#', vsys_dd ||'-'|| vsys_mon ||'-'|| vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSYYYYMONDD#', vsys_yyyy|| vsys_mon || vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSYYYY-MON-DD#', vsys_yyyy||'-'|| vsys_mon || '-' || vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSMMYYYY#', vsys_mm || vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSYYYYMM#', vsys_yyyy || vsys_mm);
v_header_format := REPLACE(v_header_format,'#SYSMONYYYY#', vsys_mon || vsys_yyyy);
v_header_format := REPLACE(v_header_format,'#SYSYYYYMON#', vsys_yyyy|| vsys_mon);
v_header_format := REPLACE(v_header_format,'#SYSDDMMYY#', vsys_dd || vsys_mm || vsys_yy);
v_header_format := REPLACE(v_header_format,'#SYSYYMMDD#', vsys_yy|| vsys_mm || vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSDDMONYY#', vsys_dd || vsys_mon || vsys_yy);
v_header_format := REPLACE(v_header_format,'#SYSYYMONDD#', vsys_yy|| vsys_mon || vsys_dd );
v_header_format := REPLACE(v_header_format,'#SYSMMYY#', vsys_mm || vsys_yy);
v_header_format := REPLACE(v_header_format,'#SYSYYMM#', vsys_yy || vsys_mm);
v_header_format := REPLACE(v_header_format,'#SYSMONYY#', vsys_mon || vsys_yy);
v_header_format := REPLACE(v_header_format,'#SYSYYMON#', vsys_yy|| vsys_mon);
END IF;
--dbms_output.put_line('Hemu Inside datafile file 111');
----ends to change the repdate format
--
--dbms_output.put_line('wsheetnamedata:'||p_worksheetname||' : filename data:'||p_repgroup||'_'||p_filename);
--
v_viewname := 'V_'||p_repgroup||'_'||SUBSTR(upper(p_filename||'.'||p_fileext),1 ,INSTR(upper(p_filename||'.'||p_fileext), '.', 1, 1)-1);
v_viewname := REPLACE(v_viewname,'-','ne');
v_viewname := REPLACE(v_viewname,chr(32),'_'); -- to replace the space to underscore
v_viewname := REPLACE(v_viewname,'/',''); -- remove the slash
v_viewname := upper(substr(v_viewname,1,25));
BEGIN
v_step := 40;
v_sql:= 'SELECT '|| p_colseparator || ' FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_colseparator;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while fetching the colseparator'||':'||SQLERRM;
RETURN v_failure;
END;
--dbms_output.put_line('Hemu Inside datafile file 222');
BEGIN
v_step := 50;
v_sql:= 'SELECT '|| p_recordseparator || ' FROM dual';
EXECUTE IMMEDIATE v_sql INTO v_recordseparator;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while fetching the recordseparator'||':'||SQLERRM;
RETURN v_failure;
END;
---
v_sql := NULL;
---
--dbms_output.put_line('Hemu Inside datafile file 333');
BEGIN
v_step := 60;
v_sql:='CREATE OR REPLACE FORCE VIEW '||v_viewname ||' AS '||p_query;
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while creating temp view by using the input query '||':'||SQLERRM;
RETURN v_failure;
END;
--dbms_output.put_line('Hemu Inside datafile file 444');
BEGIN
v_step := 70;
SELECT column_name BULK COLLECT INTO var_colnames
FROM user_tab_columns
WHERE table_name = upper(v_viewname)
ORDER BY column_id ;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while collecting the column names '||':'||SQLERRM;
RETURN v_failure;
END;
v_colnam_cnt:=var_colnames.COUNT/2;
BEGIN
v_step := 80;
IF instr(v_header_format,'#COLUMN_NAME#',1) IS NOT NULL THEN
v_column := 'Y';
-- DBMS_OUTPUT.PUT_LINE('Y');
END IF;
-------------------- release 3
v_col_val := NULL;
v_col_val1 := NULL;
v_col_val2 := NULL;
v_columnnames :=NULL;
--------------------- release 3
--dbms_output.put_line('Hemu Inside datafile file 666');
FOR i IN 1 .. var_colnames.COUNT
LOOP
--dbms_output.put_line('var_colnames:'||var_colnames(i).col_names);
IF p_fileext = 'xls' THEN
v_col_val := v_col_val||'v_tmp(i).'||var_colnames(i).col_names||'||';
ELSE
v_col_val := v_col_val||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
END IF;
IF v_column IS NOT NULL THEN
IF p_fileext = 'xls' THEN
v_columnnames := v_columnnames||'<Cell ss:StyleID="head"><Data ss:Type="String">'||var_colnames(i).col_names||'</Data></Cell>' ;
ELSE
v_columnnames := v_columnnames||var_colnames(i).col_names||v_colseparator;
-- dbms_output.put_line('v_columnnames:'||v_columnnames);
END IF;
END IF;
IF var_colnames.COUNT > 1 THEN
IF i <= v_colnam_cnt THEN
IF p_fileext = 'xls' THEN
v_col_val1 := v_col_val1||'v_tmp(i).'||var_colnames(i).col_names||'||';
ELSE
v_col_val1 := v_col_val1||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
END IF;
ELSE
IF p_fileext = 'xls' THEN
v_col_val2 := v_col_val2||'v_tmp(i).'||var_colnames(i).col_names||'||';
ELSE
v_col_val2 := v_col_val2||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
END IF;
END IF;
END IF;
END LOOP;
--dbms_output.put_line('Hemu Inside datafile file 777');
IF p_fileext <> 'xls' THEN
v_columnnames:=substr(v_columnnames,1,length(v_columnnames)-1);
END IF;
v_datagg := NULL;
v_qury_sum := p_aggate;
IF v_qury_sum IS NOT NULL THEN
EXECUTE IMMEDIATE 'select '||v_qury_sum||' FROM '||v_viewname INTO v_datagg;
ELSE
v_datagg := NULL;
END IF;
-- starts to form header format
IF v_header_format IS NOT NULL THEN
v_header_format := REPLACE(v_header_format,'#COLUMN_NAME#',v_columnnames);
v_header_format := REPLACE(v_header_format,'#FILE_NAME#',p_filename);
v_header_format := REPLACE(v_header_format,'#FILE_EXT#',p_fileext);
IF instr(v_header_format,'#COUNT#',1) <> 0 THEN
v_sql:='select count(*) FROM '|| v_viewname ;
EXECUTE IMMEDIATE v_sql INTO v_count;
v_header_format := REPLACE(v_header_format,'#COUNT#',v_count);
END IF;
--POSTREL6.0
IF instr(v_header_format,'#ANOTHER#',1) <> 0 THEN
SELECT CONTROL_VALUE INTO V_COL1 FROM SCB_CB_CUBE_CONTROL_PARAM WHERE CONTROL_VAR = 'CUBE_FILE_VERSION';
v_col3 := '['||substr(p_filename,instr(p_filename,'_',1,3)+1,length(p_filename))||']';
v_header_format := REPLACE(v_header_format,'#ANOTHER#',V_COL1||v_recordseparator||v_col3);
END IF;
--POSTREL6.0
v_header_format := REPLACE(v_header_format,'#REC_SEP#',v_recordseparator);--Added on July 14
v_header_format := REPLACE(v_header_format,'#AGGREGATE#',v_datagg);--Added on July 14
v_header_format := REPLACE(v_header_format,'#COUNTRY_CODE#',SUBSTR(p_filename, INSTR(p_filename,'_', 1, 1)+1,
INSTR(p_filename,'_',1,2)-INSTR(p_filename,'_',1,1)-1));
v_header_format := v_header_format||v_recordseparator;
v_headflg :='T';
END IF;
--dbms_output.put_line('Hemu Inside datafile file 888');
IF v_footerformat IS NOT NULL THEN
v_footerformat := REPLACE(v_footerformat,'#FILE_NAME#',p_filename);--Added on July 14
v_footerformat := v_footerformat||v_recordseparator;
v_footflg := 'T';
v_footerformat := REPLACE(v_footerformat,'#AGGREGATE#',v_datagg); --rel6.0
IF instr(v_footerformat,'#ANOTHER#',1) <> 0 THEN
SELECT CONTROL_VALUE INTO V_COL2 FROM SCB_CB_CUBE_CONTROL_PARAM WHERE CONTROL_VAR = 'CUBE_FILE_TRAILER';
v_footerformat := REPLACE(v_footerformat,'#ANOTHER#',V_COL2);
END IF;
END IF;
-- ends to form the header format
IF p_fileext = 'xls' THEN
v_col_val:=substr(v_col_val,1,length(v_col_val)-2);
v_col_val1:=substr(v_col_val1,1,length(v_col_val1)-2);
v_col_val2:=substr(v_col_val2,1,length(v_col_val2)-2);
ELSE
v_col_val:=substr(v_col_val,1,length(v_col_val)-7);
v_col_val1:=substr(v_col_val1,1,length(v_col_val1)-2);
v_col_val2:=substr(v_col_val2,1,length(v_col_val2)-7);
v_col_val:=v_col_val||'||'''||v_recordseparator||'''';
END IF;
--dbms_output.put_line('Hemu Inside datafile file 999');
IF var_colnames.COUNT > 1 THEN
v_col_val1:=v_col_val1||'||'''||v_recordseparator||'''';
v_col_val2:=v_col_val2||'||'''||v_recordseparator||'''';
ELSE
v_col_val1 := 'v_tmp(1).'||var_colnames(1).col_names;
v_col_val2 := 'v_tmp(1).'||var_colnames(1).col_names;
END IF;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while fetching the column names and values '||':'||SQLERRM;
RETURN v_failure;
END;
--dbms_output.put_line('Hemu Inside datafile file 999 A');
--starts to write the header
IF v_headflg = 'T' THEN
--IF p_fileext = 'xls' THEN
/* IF p_workbooktype = 'S' THEN
v_dataexcel := utl_file.fopen(p_oracle_directory,p_repgroup||'.'||p_fileext,'ab');
ELSIF p_workbooktype = 'M' THEN
v_dataexcel := utl_file.fopen(p_oracle_directory,p_repgroup||'_'||p_filename||'.'||p_fileext,'ab');
END IF;
ELSE*/
v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'ab');
--END IF;
--dbms_output.put_line('Hemu Inside datafile file 999 B');
IF p_fileext = 'xls' THEN
-- dbms_output.put_line('gggggggg:'||p_repgroup||'_'||p_filename||'.'||p_fileext||':'||v_header_format);
UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('<Row ss:Index="4">','WE8ISO8859P15'));
utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('</Row>','WE8ISO8859P15'));
ELSE
utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
END IF;
--dbms_output.put_line('Hemu Inside datafile file 999 C');
utl_file.fclose(v_dataexcel);
--dbms_output.put_line('Hemu Inside datafile file 999 D');
END IF;
--dbms_output.put_line('Hemu Inside datafile file 10 10 10');
IF p_fileext = 'xls' THEN
v_excelclose := '</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>';
v_excelflag := 'T';
ELSE
v_excelflag := NULL;
END IF;
--ends to write the header
-------------------------------------
--starts to generate the data file by dynamic anonymous block
v_SQL:=NULL;
/*IF p_workbooktype = 'S' THEN
v_filename := p_repgroup||'.'||p_fileext;
ELSE
v_filename := p_repgroup||'_'||p_filename||'.'||p_fileext;
END IF;*/
v_filename := p_filename||'.'||p_fileext;
--dbms_output.put_line('Hemu Inside datafile file 11 11 11');
--v_SQL:='DECLARE v_dat utl_file.file_type; v_cnt number := 0;v_foot varchar2(4000) := null;TYPE ty_vtmp is table of '||v_viewname||'%rowtype;v_tmp ty_vtmp;TYPE rc_type IS REF CURSOR;c1 rc_type;v_sql_cur long;buffer_small EXCEPTION;PRAGMA EXCEPTION_INIT(buffer_small, -6502);begin v_foot := '''||v_footerformat||'''; v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||p_repgroup||'_'||v_filename||''', ''WB'' ,32767);if '''||v_headflg||''' = ''T'' then utl_file.put_raw(v_dat,utl_i18n.string_to_raw('''||v_header_format||''',''WE8ISO8859P15'')); end if; v_sql_cur:=''select /*+PARALLEL(' || v_viewname || ', 3)*/ * from '||v_viewname || ''';OPEN c1 FOR v_sql_cur;loop fetch c1 bulk collect into v_tmp limit 5000; v_cnt := v_cnt + v_tmp.COUNT; exit when v_tmp.count=0; for i in 1 .. v_tmp.count loop begin utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15'')); exception WHEN buffer_small THEN utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15''));end; end loop; :1 := v_cnt; end loop; if '''||v_footflg||''' = ''T'' then IF instr(v_foot,''#COUNT#'',1) IS NOT NULL THEN v_foot := REPLACE(v_foot,''#COUNT#'',v_cnt); end if; utl_file.put_raw(v_dat,utl_i18n.string_to_raw(v_foot,''WE8ISO8859P15''));end if; if utl_file.is_open(v_dat) then utl_file.fclose(v_dat); end if; end;';
--needs to check the limits
v_SQL:='DECLARE v_dat utl_file.file_type;
v_cnt number := 0;
v_index number :=4;
v_ws number := 0;
TYPE ty_vtmp is table of '||v_viewname||'%rowtype;
v_tmp ty_vtmp;
TYPE rc_type IS REF CURSOR;
c1 rc_type;
v_sql_cur long;
buffer_small EXCEPTION;
PRAGMA EXCEPTION_INIT(buffer_small, -6502);
begin
v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'', ''32767'' );
v_sql_cur:=''select /*+PARALLEL(' || v_viewname || ', 3)*/ * from '||v_viewname || ''';
OPEN c1 FOR v_sql_cur;
loop fetch c1 bulk collect into v_tmp limit 5000;
v_cnt := v_cnt + v_tmp.COUNT;
exit when v_tmp.count=0;
if '''||v_excelflag||''' is null THEN
for i in 1 .. v_tmp.count
loop
begin
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
exception
WHEN buffer_small THEN
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15'')) ;
END;
END LOOP;
elsif '''||v_excelflag||''' = ''T'' THEN
for i in 1 .. v_tmp.count
loop
begin
v_index := v_index +1 ;
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Row ss:Index="'||'''||v_index||'''||'">'',''WE8ISO8859P15''));
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''</Row>'',''WE8ISO8859P15''));
--utl_file.fclose(v_dat);
IF v_INDEX = 65301 THEN
v_ws := v_ws + 1;
--v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'');
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw('''||v_excelclose||''',''WE8ISO8859P15''));
--UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_1">'',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_'||'''||v_ws||'''||'">'',''WE8ISO8859P15''));
UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Table ss:ExpandedColumnCount="65335" ss:ExpandedRowCount="65335" x:FullColumns="1" x:FullRows="1">'',''WE8ISO8859P15''));
--utl_file.fclose(v_dat);
v_index := 0;
END IF;
--utl_file.fclose(v_dat);
end;
end loop;
end if;
:1 := v_cnt;
end loop;
if utl_file.is_open(v_dat) then
utl_file.fclose(v_dat);
end if;
end;';
BEGIN
v_step := 90;
/*INSERT INTO xyz VALUES('v_sql:'||v_sql);
COMMIT;*/
EXECUTE IMMEDIATE v_SQL USING OUT v_count;
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while writing the data file:'||SQLERRM||':'||SQLCODE;
RETURN v_failure;
END;
--ends to generate the data file by dynamic anonymous block
p_count := nvl(v_count,0);
--dbms_output.put_line('Hemu Inside datafile file 13 13 13');
--- starts to write the footer
if v_footflg = 'T' then
v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'a');
IF instr(v_footerformat,'#COUNT#',1) IS NOT NULL THEN
v_footerformat := REPLACE(v_footerformat,'#COUNT#',p_count);
end if;
utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_footerformat,'WE8ISO8859P15'));
utl_file.fclose(v_dataexcel);
end IF;
--- ends to write the footer
if p_repgroup in ('E2E_TLM_BCRS_STAGING','E2E_TLM_FDWS_STAGING','E2E_TLM_BCRS_RESULTS') then
begin
v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'ab');
-- dbms_output.put_line(p_repgroup || ' Before ret val ' || v_dummy_ft_ret_val );
v_dummy_ft_ret_val:=dummyfootergeneration(p_repgroup,p_filename||'.'||p_fileext,p_src_system,v_dataexcel,v_recordseparator,p_country_code,v_count_dmyf);
--postrel5.0
v_count_dmy := v_dummy_ft_ret_val+p_count;
p_count := nvl(v_count_dmy,0);
--postrel5.0
-- dbms_output.put_line(p_repgroup || ' completed ret val ' || v_dummy_ft_ret_val );
if v_dummy_ft_ret_val =0 then
null;
ELSIF v_dummy_ft_ret_val !=0 THEN
NULL;
else
raise others;
end if;
exception
when others then
p_err_msg := 'while writing dummy footer for file name :' || p_filename || ' Ret val v_dummy_ft_ret_val => '|| v_dummy_ft_ret_val||':'||SQLERRM;
RETURN v_failure;
end;
end if ;
/*BEGIN
v_step := 100;
EXECUTE IMMEDIATE 'drop view '|| v_viewname ;
\*EXCEPTION
WHEN OTHERS THEN
p_err_msg :=v_step||SQLERRM||':'||SQLCODE;
RETURN v_failure;*\
END;*/
RETURN v_success;
--dbms_output.put_line('Hemu Inside datafile file 14 14 14');
EXCEPTION
WHEN OTHERS THEN
p_err_msg := 'while writing the dat file :'||SQLCODE||':'||SQLERRM;
RETURN v_failure;
END;
****************************
In this only the below block is creating a problem
for i in 1 .. v_tmp.count
loop
begin
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
exception
WHEN buffer_small THEN
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));
utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15'')) ;
END;
END LOOP;
*********************************
Initially i thought it was because of increase in columns, but when i created a table with 500 columns with datatype varchar(2) 10. This package works fine. But my actual table has many columns varchar2(4000). This same package works for the previous realease with 256 columns.
During the put_raw datatype conversion is not proper. Anyother possible solutions for this.
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Buffer String too small [message #505959 is a reply to message #505950] |
Thu, 05 May 2011 15:33 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
catchcv wrote on Thu, 05 May 2011 16:17Hi All,
Below is the error message i get when i execute the package.
I dont know how to put the line numbers.
SQL> exec scb_mlp_pack_extractionprd.extraction(7,'PRO_114_PLC','31122010');
begin scb_mlp_pack_extractionprd.extraction(7,'PRO_114_PLC','31122010'); end;
ORA-20002: process failed. Refer log table for further information: while writing the dat file :-6502:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TRGV110.SCB_MLP_PACK_EXTRACTIONPRD", line 2056
ORA-06512: at line 1
As previously mentioned, the error is in TRGV110.SCB_MLP_PACK_EXTRACTIONPRD. You'll have to start there.
|
|
|
|
| Re: Buffer String too small [message #505962 is a reply to message #505959] |
Thu, 05 May 2011 15:39 |
|
catchcv
Messages: 10
Registered: May 2011
|
Junior Member |
|
|
1 FUNCTION datafilegeneration(
2 p_oracle_directory scb_mlp_csv_config_param.oracle_directory%TYPE,
3 p_header_format scb_mlp_csv_config_param.header_format%TYPE,
4 p_footer_format scb_mlp_csv_config_param.footer_format%TYPE,
5 p_repgroup scb_mlp_csv_config_param.rep_group%TYPE,
6 p_filename IN scb_mlp_csv_config.filename%TYPE,
7 p_fileext scb_mlp_csv_config_param.file_ext%TYPE,
8 p_worksheetname scb_mlp_csv_config.worksheet_name%TYPE,
9 p_workbooktype IN VARCHAR2,
10 p_query scb_mlp_csv_config.query%TYPE,
11 p_colseparator scb_mlp_csv_config_param.col_separator%TYPE,
12 p_recordseparator scb_mlp_csv_config_param.record_separator%TYPE,
13 p_count OUT NUMBER,
14 p_err_msg OUT VARCHAR2,
15 p_src_system in VARCHAR2,
16 p_country_code in varchar2,
17 p_aggate IN VARCHAR2
18 ) RETURN NUMBER IS
19
20 TYPE ty_cname is record (col_names LONG);
21
22 TYPE ty_colnames iS TABLE OF ty_cname;
23 var_colnames ty_colnames;
24 #NAME?
25 v_xlscolnames ty_colnames;
26 #NAME?
27 v_col_val LONG;
28 v_col_val1 LONG;
29 v_col_val2 LONG;
30
31 v_colnam_cnt NUMBER;
32 proc_failure EXCEPTION;
33 v_colseparator scb_mlp_csv_config_param.col_separator%TYPE;
34 v_recordseparator scb_mlp_csv_config_param.record_separator%TYPE;
35 v_columnnames LONG;
36 v_count scb_mlp_csv_config.record_count%TYPE;
37 v_viewname VARCHAR2(100);
38 v_header_format LONG; -- release 3
39 v_sysdate VARCHAR2(10);
40 v_footerformat scb_mlp_csv_config_param.footer_format%TYPE;
41 v_filename VARCHAR2(100); --siva
42 v_column VARCHAR2(2);
43 v_headflg VARCHAR2(2);
44 v_footflg VARCHAR2(2);
45 v_excelflag CHAR(1);
46 v_dataexcel utl_file.file_type;
47 v_excelclose VARCHAR2(5000);
48 v_count_dmy NUMBER;
49 v_count_dmyf NUMBER;
50 v_datagg VARCHAR2(100);
51 v_qury_sum VARCHAR2(100);
52 V_COL1 VARCHAR2(100);
53 V_COL2 VARCHAR2(100);
54 V_COL3 VARCHAR2(100);
55 v_cnt1 number;
56 v_cube_filename VARCHAR2(100);
57
58 v_vc_cude varchar2(30);
59
60
61
62 --v_tlm_sql varchar2(1000):='select source_system from scb_mlp_csv_config where rep_group=:1 and filename=:2' ;
63 --v_src_system scb_mlp_csv_config.source_system%type;
64 v_dummy_ft_ret_val number:=0;
65 others exception;
66 BEGIN
67 --dbms_output.put_line('Hemu Inside datafile file 000');
68 v_sysdate := TO_CHAR(SYSDATE,'ddmmyyyy');
69 ---- starts to change the repdate format
70 vsys_YYYY := to_char(to_date(v_sysdate,'DDMMYYYY'),'YYYY');
71 vsys_YY := to_char(to_date(v_sysdate,'DDMMYYYY'),'YY');
72 vsys_MON := to_char(to_date(v_sysdate,'DDMMYYYY'),'MON');
73 vsys_MM := to_char(to_date(v_sysdate,'DDMMYYYY'),'MM');
74 vsys_DD := to_char(to_date(v_sysdate,'DDMMYYYY'),'DD');
75
76 v_header_format := p_header_format;
77 v_footerformat := p_footer_format;
78
79 IF INSTR(v_header_format, '#SYS') > 0 THEN -- File name contains DDMMYYYY
80 v_header_format := REPLACE(v_header_format,'#SYSDDMMYYYY#', vsys_dd || vsys_mm || vsys_yyyy);
81 v_header_format := REPLACE(v_header_format,'#SYSDD-MM-YYYY#', vsys_dd ||'-'|| vsys_mm ||'-'|| vsys_yyyy);
82 v_header_format := REPLACE(v_header_format,'#SYSDD/MM/YYYY#', vsys_dd ||'/'|| vsys_mm ||'/'|| vsys_yyyy);--Added on july 14
83 v_header_format := REPLACE(v_header_format,'#SYSYYYYMMDD#', vsys_yyyy || vsys_mm || vsys_dd );
84 v_header_format := REPLACE(v_header_format,'#SYSYYYY-MM-DD#', vsys_yyyy ||'-'|| vsys_mm || '-'|| vsys_dd );
85 v_header_format := REPLACE(v_header_format,'#SYSDDMONYYYY#', vsys_dd || vsys_mon || vsys_yyyy);
86 v_header_format := REPLACE(v_header_format,'#SYSDD-MON-YYYY#', vsys_dd ||'-'|| vsys_mon ||'-'|| vsys_yyyy);
87 v_header_format := REPLACE(v_header_format,'#SYSYYYYMONDD#', vsys_yyyy|| vsys_mon || vsys_dd );
88 v_header_format := REPLACE(v_header_format,'#SYSYYYY-MON-DD#', vsys_yyyy||'-'|| vsys_mon || '-' || vsys_dd );
89 v_header_format := REPLACE(v_header_format,'#SYSMMYYYY#', vsys_mm || vsys_yyyy);
90 v_header_format := REPLACE(v_header_format,'#SYSYYYYMM#', vsys_yyyy || vsys_mm);
91 v_header_format := REPLACE(v_header_format,'#SYSMONYYYY#', vsys_mon || vsys_yyyy);
92 v_header_format := REPLACE(v_header_format,'#SYSYYYYMON#', vsys_yyyy|| vsys_mon);
93 v_header_format := REPLACE(v_header_format,'#SYSDDMMYY#', vsys_dd || vsys_mm || vsys_yy);
94 v_header_format := REPLACE(v_header_format,'#SYSYYMMDD#', vsys_yy|| vsys_mm || vsys_dd );
95 v_header_format := REPLACE(v_header_format,'#SYSDDMONYY#', vsys_dd || vsys_mon || vsys_yy);
96 v_header_format := REPLACE(v_header_format,'#SYSYYMONDD#', vsys_yy|| vsys_mon || vsys_dd );
97 v_header_format := REPLACE(v_header_format,'#SYSMMYY#', vsys_mm || vsys_yy);
98 v_header_format := REPLACE(v_header_format,'#SYSYYMM#', vsys_yy || vsys_mm);
99 v_header_format := REPLACE(v_header_format,'#SYSMONYY#', vsys_mon || vsys_yy);
100 v_header_format := REPLACE(v_header_format,'#SYSYYMON#', vsys_yy|| vsys_mon);
101 END IF;
102 --dbms_output.put_line('Hemu Inside datafile file 111');
103 ----ends to change the repdate format
104
105 --
106 --dbms_output.put_line('wsheetnamedata:'||p_worksheetname||' : filename data:'||p_repgroup||'_'||p_filename);
107 --
108 v_viewname := 'V_'||p_repgroup||'_'||SUBSTR(upper(p_filename||'.'||p_fileext),1 ,INSTR(upper(p_filename||'.'||p_fileext), '.', 1, 1)-1);
109 v_viewname := REPLACE(v_viewname,'-','ne');
110 v_viewname := REPLACE(v_viewname,chr(32),'_'); -- to replace the space to underscore
111 v_viewname := REPLACE(v_viewname,'/',''); -- remove the slash
112 v_viewname := upper(substr(v_viewname,1,25));
113
114 BEGIN
115 v_step := 40;
116 v_sql:= 'SELECT '|| p_colseparator || ' FROM dual';
117 EXECUTE IMMEDIATE v_sql INTO v_colseparator;
118 EXCEPTION
119 WHEN OTHERS THEN
120 p_err_msg := 'while fetching the colseparator'||':'||SQLERRM;
121 RETURN v_failure;
122 END;
123 --dbms_output.put_line('Hemu Inside datafile file 222');
124 BEGIN
125 v_step := 50;
126 v_sql:= 'SELECT '|| p_recordseparator || ' FROM dual';
127 EXECUTE IMMEDIATE v_sql INTO v_recordseparator;
128 EXCEPTION
129 WHEN OTHERS THEN
130 p_err_msg := 'while fetching the recordseparator'||':'||SQLERRM;
131 RETURN v_failure;
132 END;
133 ---
134 v_sql := NULL;
135 ---
136 --dbms_output.put_line('Hemu Inside datafile file 333');
137 BEGIN
138
139 v_step := 60;
140
141 v_sql:='CREATE OR REPLACE FORCE VIEW '||v_viewname ||' AS '||p_query;
142
143 EXECUTE IMMEDIATE v_sql;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 p_err_msg := 'while creating temp view by using the input query '||':'||SQLERRM;
148 RETURN v_failure;
149 END;
150 --dbms_output.put_line('Hemu Inside datafile file 444');
151 BEGIN
152 v_step := 70;
153
154 SELECT column_name BULK COLLECT INTO var_colnames
155 FROM user_tab_columns
156 WHERE table_name = upper(v_viewname)
157 ORDER BY column_id ;
158 EXCEPTION
159 WHEN OTHERS THEN
160 p_err_msg := 'while collecting the column names '||':'||SQLERRM;
161 RETURN v_failure;
162 END;
163
164 v_colnam_cnt:=var_colnames.COUNT/2;
165
166 BEGIN
167 v_step := 80;
168 IF instr(v_header_format,'#COLUMN_NAME#',1) IS NOT NULL THEN
169 v_column := 'Y';
170 -- DBMS_OUTPUT.PUT_LINE('Y');
171 END IF;
172
173 -------------------- release 3
174 v_col_val := NULL;
175 v_col_val1 := NULL;
176 v_col_val2 := NULL;
177 v_columnnames :=NULL;
178 --------------------- release 3
179 --dbms_output.put_line('Hemu Inside datafile file 666');
180 FOR i IN 1 .. var_colnames.COUNT
181 LOOP
182 --dbms_output.put_line('var_colnames:'||var_colnames(i).col_names);
183 IF p_fileext = 'xls' THEN
184 v_col_val := v_col_val||'v_tmp(i).'||var_colnames(i).col_names||'||';
185 ELSE
186 v_col_val := v_col_val||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
187 END IF;
188
189 IF v_column IS NOT NULL THEN
190 IF p_fileext = 'xls' THEN
191 v_columnnames := v_columnnames||'<Cell ss:StyleID="head"><Data ss:Type="String">'||var_colnames(i).col_names||'</Data></Cell>' ;
192 ELSE
193 v_columnnames := v_columnnames||var_colnames(i).col_names||v_colseparator;
194 -- dbms_output.put_line('v_columnnames:'||v_columnnames);
195 END IF;
196 END IF;
197
198 IF var_colnames.COUNT > 1 THEN
199
200 IF i <= v_colnam_cnt THEN
201
202 IF p_fileext = 'xls' THEN
203 v_col_val1 := v_col_val1||'v_tmp(i).'||var_colnames(i).col_names||'||';
204 ELSE
205 v_col_val1 := v_col_val1||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
206 END IF;
207
208 ELSE
209
210 IF p_fileext = 'xls' THEN
211 v_col_val2 := v_col_val2||'v_tmp(i).'||var_colnames(i).col_names||'||';
212 ELSE
213 v_col_val2 := v_col_val2||'v_tmp(i).'||var_colnames(i).col_names||'||'''||v_colseparator||'''||';
214 END IF;
215
216 END IF;
217
218 END IF;
219
220 END LOOP;
221 --dbms_output.put_line('Hemu Inside datafile file 777');
222 IF p_fileext <> 'xls' THEN
223 v_columnnames:=substr(v_columnnames,1,length(v_columnnames)-1);
224 END IF;
225 v_datagg := NULL;
226 v_qury_sum := p_aggate;
227 IF v_qury_sum IS NOT NULL THEN
228 EXECUTE IMMEDIATE 'select '||v_qury_sum||' FROM '||v_viewname INTO v_datagg;
229
230
231 ELSE
232 v_datagg := NULL;
233 END IF;
234
235 -- starts to form header format
236
237 IF v_header_format IS NOT NULL THEN
238 v_header_format := REPLACE(v_header_format,'#COLUMN_NAME#',v_columnnames);
239 v_header_format := REPLACE(v_header_format,'#FILE_NAME#',p_filename);
240 v_header_format := REPLACE(v_header_format,'#FILE_EXT#',p_fileext);
241 IF instr(v_header_format,'#COUNT#',1) <> 0 THEN
242 v_sql:='select count(*) FROM '|| v_viewname ;
243 EXECUTE IMMEDIATE v_sql INTO v_count;
244 v_header_format := REPLACE(v_header_format,'#COUNT#',v_count);
245 END IF;
246 #NAME?
247 IF instr(v_header_format,'#ANOTHER#',1) <> 0 THEN
248 SELECT CONTROL_VALUE INTO V_COL1 FROM SCB_CB_CUBE_CONTROL_PARAM WHERE CONTROL_VAR = 'CUBE_FILE_VERSION';
249 v_col3 := '['||substr(p_filename,instr(p_filename,'_',1,3)+1,length(p_filename))||']';
250 v_header_format := REPLACE(v_header_format,'#ANOTHER#',V_COL1||v_recordseparator||v_col3);
251 END IF;
252 #NAME?
253 v_header_format := REPLACE(v_header_format,'#REC_SEP#',v_recordseparator);--Added on July 14
254 v_header_format := REPLACE(v_header_format,'#AGGREGATE#',v_datagg);--Added on July 14
255 v_header_format := REPLACE(v_header_format,'#COUNTRY_CODE#',SUBSTR(p_filename, INSTR(p_filename,'_', 1, 1)+1,
256 INSTR(p_filename,'_',1,2)-INSTR(p_filename,'_',1,1)-1));
257 v_header_format := v_header_format||v_recordseparator;
258 v_headflg :='T';
259 END IF;
260
261 --dbms_output.put_line('Hemu Inside datafile file 888');
262
263 IF v_footerformat IS NOT NULL THEN
264 v_footerformat := REPLACE(v_footerformat,'#FILE_NAME#',p_filename);--Added on July 14
265 v_footerformat := v_footerformat||v_recordseparator;
266 v_footflg := 'T';
267 v_footerformat := REPLACE(v_footerformat,'#AGGREGATE#',v_datagg); --rel6.0
268 IF instr(v_footerformat,'#ANOTHER#',1) <> 0 THEN
269 SELECT CONTROL_VALUE INTO V_COL2 FROM SCB_CB_CUBE_CONTROL_PARAM WHERE CONTROL_VAR = 'CUBE_FILE_TRAILER';
270 v_footerformat := REPLACE(v_footerformat,'#ANOTHER#',V_COL2);
271
272 END IF;
273 END IF;
274 -- ends to form the header format
275
276 IF p_fileext = 'xls' THEN
277 v_col_val:=substr(v_col_val,1,length(v_col_val)-2);
278 v_col_val1:=substr(v_col_val1,1,length(v_col_val1)-2);
279 v_col_val2:=substr(v_col_val2,1,length(v_col_val2)-2);
280 ELSE
281 v_col_val:=substr(v_col_val,1,length(v_col_val)-7);
282 v_col_val1:=substr(v_col_val1,1,length(v_col_val1)-2);
283 v_col_val2:=substr(v_col_val2,1,length(v_col_val2)-7);
284 v_col_val:=v_col_val||'||'''||v_recordseparator||'''';
285
286 END IF;
287 --dbms_output.put_line('Hemu Inside datafile file 999');
288 IF var_colnames.COUNT > 1 THEN
289 v_col_val1:=v_col_val1||'||'''||v_recordseparator||'''';
290 v_col_val2:=v_col_val2||'||'''||v_recordseparator||'''';
291 ELSE
292 v_col_val1 := 'v_tmp(1).'||var_colnames(1).col_names;
293 v_col_val2 := 'v_tmp(1).'||var_colnames(1).col_names;
294 END IF;
295
296 EXCEPTION
297 WHEN OTHERS THEN
298 p_err_msg := 'while fetching the column names and values '||':'||SQLERRM;
299 RETURN v_failure;
300 END;
301 --dbms_output.put_line('Hemu Inside datafile file 999 A');
302
303 --starts to write the header
304 IF v_headflg = 'T' THEN
305
306 --IF p_fileext = 'xls' THEN
307 /* IF p_workbooktype = 'S' THEN
308 v_dataexcel := utl_file.fopen(p_oracle_directory,p_repgroup||'.'||p_fileext,'ab');
309
310 ELSIF p_workbooktype = 'M' THEN
311 v_dataexcel := utl_file.fopen(p_oracle_directory,p_repgroup||'_'||p_filename||'.'||p_fileext,'ab');
312 END IF;
313 ELSE*/
314 v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'ab');
315 --END IF;
316 --dbms_output.put_line('Hemu Inside datafile file 999 B');
317 IF p_fileext = 'xls' THEN
318 -- dbms_output.put_line('gggggggg:'||p_repgroup||'_'||p_filename||'.'||p_fileext||':'||v_header_format);
319
320 UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('<Row ss:Index="4">','WE8ISO8859P15'));
321 utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
322 UTL_FILE.put_raw(v_dataexcel,utl_i18n.string_to_raw('</Row>','WE8ISO8859P15'));
323 ELSE
324 utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_header_format,'WE8ISO8859P15'));
325 END IF;
326 --dbms_output.put_line('Hemu Inside datafile file 999 C');
327 utl_file.fclose(v_dataexcel);
328 --dbms_output.put_line('Hemu Inside datafile file 999 D');
329 END IF;
330 --dbms_output.put_line('Hemu Inside datafile file 10 10 10');
331 IF p_fileext = 'xls' THEN
332
333 v_excelclose := '</Table>
334 <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
335 <ProtectObjects>False</ProtectObjects>
336 <ProtectScenarios>False</ProtectScenarios>
337 </WorksheetOptions>
338 </Worksheet>';
339 v_excelflag := 'T';
340
341 ELSE
342 v_excelflag := NULL;
343 END IF;
344 --ends to write the header
345 -------------------------------------
346 --starts to generate the data file by dynamic anonymous block
347
348 v_SQL:=NULL;
349
350 /*IF p_workbooktype = 'S' THEN
351 v_filename := p_repgroup||'.'||p_fileext;
352 ELSE
353 v_filename := p_repgroup||'_'||p_filename||'.'||p_fileext;
354 END IF;*/
355 v_filename := p_filename||'.'||p_fileext;
356
357 --dbms_output.put_line('Hemu Inside datafile file 11 11 11');
358 --v_SQL:='DECLARE v_dat utl_file.file_type; v_cnt number := 0;v_foot varchar2(4000) := null;TYPE ty_vtmp is table of '||v_viewname||'%rowtype;v_tmp ty_vtmp;TYPE rc_type IS REF CURSOR;c1 rc_type;v_sql_cur long;buffer_small EXCEPTION;PRAGMA EXCEPTION_INIT(buffer_small, -6502);begin v_foot := '''||v_footerformat||'''; v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||p_repgroup||'_'||v_filename||''', ''WB'' ,32767);if '''||v_headflg||''' = ''T'' then utl_file.put_raw(v_dat,utl_i18n.string_to_raw('''||v_header_format||''',''WE8ISO8859P15'')); end if; v_sql_cur:=''select /*+PARALLEL(' || v_viewname || ', 3)*/ * from '||v_viewname || ''';OPEN c1 FOR v_sql_cur;loop fetch c1 bulk collect into v_tmp limit 5000; v_cnt := v_cnt + v_tmp.COUNT; exit when v_tmp.count=0; for i in 1 .. v_tmp.count loop begin utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15'')); exception WHEN buffer_small THEN utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15''));end; end loop; :1 := v_cnt; end loop; if '''||v_footflg||''' = ''T'' then IF instr(v_foot,''#COUNT#'',1) IS NOT NULL THEN v_foot := REPLACE(v_foot,''#COUNT#'',v_cnt); end if; utl_file.put_raw(v_dat,utl_i18n.string_to_raw(v_foot,''WE8ISO8859P15''));end if; if utl_file.is_open(v_dat) then utl_file.fclose(v_dat); end if; end;';
359 --needs to check the limits
360 v_SQL:='DECLARE v_dat utl_file.file_type;
361 v_cnt number := 0;
362 v_index number :=4;
363 v_ws number := 0;
364 TYPE ty_vtmp is table of '||v_viewname||'%rowtype;
365 v_tmp ty_vtmp;
366 TYPE rc_type IS REF CURSOR;
367 c1 rc_type;
368 v_sql_cur long;
369 buffer_small EXCEPTION;
370 PRAGMA EXCEPTION_INIT(buffer_small, -6502);
371 begin
372 v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'', ''32767'' );
373 v_sql_cur:=''select /*+PARALLEL(' || v_viewname || ', 3)*/ * from '||v_viewname || ''';
374 OPEN c1 FOR v_sql_cur;
375 loop fetch c1 bulk collect into v_tmp limit 5000;
376 v_cnt := v_cnt + v_tmp.COUNT;
377 exit when v_tmp.count=0;
378 if '''||v_excelflag||''' is null THEN
379 for i in 1 .. v_tmp.count
380 loop
381 begin
382 utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
383 exception
384 WHEN buffer_small THEN
385 utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val1||',''WE8ISO8859P15''));
386 utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val2||',''WE8ISO8859P15'')) ;
387 END;
388 END LOOP;
389 elsif '''||v_excelflag||''' = ''T'' THEN
390 for i in 1 .. v_tmp.count
391 loop
392 begin
393 v_index := v_index +1 ;
394 UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Row ss:Index="'||'''||v_index||'''||'">'',''WE8ISO8859P15''));
395 utl_file.put_raw(v_dat,utl_i18n.string_to_raw('||v_col_val||',''WE8ISO8859P15''));
396 UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''</Row>'',''WE8ISO8859P15''));
397 --utl_file.fclose(v_dat);
398 IF v_INDEX = 65301 THEN
399 v_ws := v_ws + 1;
400 --v_dat := utl_file.fopen('''||p_oracle_directory||''', '''||v_filename||''', ''a'');
401 UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw('''||v_excelclose||''',''WE8ISO8859P15''));
402 --UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_1">'',''WE8ISO8859P15''));
403 UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Worksheet ss:Name="'||p_worksheetname||'_'||'''||v_ws||'''||'">'',''WE8ISO8859P15''));
404 UTL_FILE.put_raw(v_dat,utl_i18n.string_to_raw(''<Table ss:ExpandedColumnCount="65335" ss:ExpandedRowCount="65335" x:FullColumns="1" x:FullRows="1">'',''WE8ISO8859P15''));
405 --utl_file.fclose(v_dat);
406 v_index := 0;
407 END IF;
408 --utl_file.fclose(v_dat);
409 end;
410 end loop;
411 end if;
412 :1 := v_cnt;
413 end loop;
414 if utl_file.is_open(v_dat) then
415 utl_file.fclose(v_dat);
416 end if;
417 end;';
418 BEGIN
419 v_step := 90;
420
421 /*INSERT INTO xyz VALUES('v_sql:'||v_sql);
422 COMMIT;*/
423
424 EXECUTE IMMEDIATE v_SQL USING OUT v_count;
425
426 EXCEPTION
427 WHEN OTHERS THEN
428 p_err_msg := 'while writing the data file:'||SQLERRM||':'||SQLCODE;
429 RETURN v_failure;
430 END;
431 --ends to generate the data file by dynamic anonymous block
432 p_count := nvl(v_count,0);
433 --dbms_output.put_line('Hemu Inside datafile file 13 13 13');
434 --- starts to write the footer
435
436 if v_footflg = 'T' then
437 v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'a');
438 IF instr(v_footerformat,'#COUNT#',1) IS NOT NULL THEN
439 v_footerformat := REPLACE(v_footerformat,'#COUNT#',p_count);
440 end if;
441 utl_file.put_raw(v_dataexcel,utl_i18n.string_to_raw(v_footerformat,'WE8ISO8859P15'));
442 utl_file.fclose(v_dataexcel);
443 end IF;
444 --- ends to write the footer
445
446 if p_repgroup in ('E2E_TLM_BCRS_STAGING','E2E_TLM_FDWS_STAGING','E2E_TLM_BCRS_RESULTS') then
447 begin
448 v_dataexcel := utl_file.fopen(p_oracle_directory,p_filename||'.'||p_fileext,'ab');
449 -- dbms_output.put_line(p_repgroup || ' Before ret val ' || v_dummy_ft_ret_val );
450 v_dummy_ft_ret_val:=dummyfootergeneration(p_repgroup,p_filename||'.'||p_fileext,p_src_system,v_dataexcel,v_recordseparator,p_country_code,v_count_dmyf);
451 --postrel5.0
452 v_count_dmy := v_dummy_ft_ret_val+p_count;
453 p_count := nvl(v_count_dmy,0);
454 --postrel5.0
455 -- dbms_output.put_line(p_repgroup || ' completed ret val ' || v_dummy_ft_ret_val );
456
457 if v_dummy_ft_ret_val =0 then
458 null;
459 ELSIF v_dummy_ft_ret_val !=0 THEN
460 NULL;
461 else
462 raise others;
463 end if;
464
465 exception
466 when others then
467 p_err_msg := 'while writing dummy footer for file name :' || p_filename || ' Ret val v_dummy_ft_ret_val => '|| v_dummy_ft_ret_val||':'||SQLERRM;
468 RETURN v_failure;
469 end;
470
471 end if ;
472
473
474
475
476 /*BEGIN
477 v_step := 100;
478 EXECUTE IMMEDIATE 'drop view '|| v_viewname ;
479 \*EXCEPTION
480 WHEN OTHERS THEN
481 p_err_msg :=v_step||SQLERRM||':'||SQLCODE;
482 RETURN v_failure;*\
483 END;*/
484
485 RETURN v_success;
486 --dbms_output.put_line('Hemu Inside datafile file 14 14 14');
487 EXCEPTION
488 WHEN OTHERS THEN
489 p_err_msg := 'while writing the dat file :'||SQLCODE||':'||SQLERRM;
490 RETURN v_failure;
491 END;
*********************
Hi Mike,
Numbered using XL and the line number is 382.
Thanks in advance
|
|
|
|
|
|
|
|
| Re: Buffer String too small [message #505968 is a reply to message #505966] |
Thu, 05 May 2011 15:47 |
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
|
Senior Member |
|
|
catchcv wrote on Thu, 05 May 2011 16:44
ORA-20002: process failed. Refer log table for further information: while writing the dat file :-6502:ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "TRGV110.SCB_MLP_PACK_EXTRACTIONPRD", line 2056
ORA-06512: at line 1
Ok, this is fun. I do not know why you are ignoring what Oracle is telling you, so I will tell you too for the fourth time. Eror is in line 2056 of TRGV110.SCB_MLP_PACK_EXTRACTIONPRD. Look there.
|
|
|
|
|
|
|
|
| Re: Buffer String too small(2 Merged) [message #506017 is a reply to message #505922] |
Fri, 06 May 2011 02:32 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
From a quick look, there are two major problems in given code.
1) Using LONG data type for temporary variables' type and supposing it may hold long enough data.
According to the documentation for 10gR2: http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/datatypes.htm#CJAEGDEB
Quote:The LONG datatype is like the VARCHAR2 datatype, except that the maximum size of a LONG value is 32760 bytes.
... you cannot retrieve a value longer than 32760 bytes from a LONG column into a LONG variable. So, it is even worse than using VARCHAR2(32767) data type.
2) Not binding at all. After hardcoding the values, the length of V_SQL exceeds its limit (by the way I cannot find its declaration).
You may find explanation of binding (instead of documentation) e.g. in this article: http://www.rittmanmead.com/2004/03/bind-variables-explained/ (concentrate on the part about dynamic SQL)
So, in your case, your code should look like this (paraphrasing your very first post):
v_sql := <some_content>
||'utl_file.put_raw(dat,utl_i18n.string_to_raw(:1,''WE8ISO8859P15''));'
||<another content>
||'utl_file.put_raw(dat,utl_i18n.string_to_raw(:2,''WE8ISO8859P15''));'
||<yet another content>;
execute immediate v_sql using v_col_val, v_colval1; |
|
|
|
|
|
| Re: Buffer String too small(2 Merged) [message #506103 is a reply to message #506094] |
Fri, 06 May 2011 09:25 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
catchcv wrote on Fri, 06 May 2011 15:59Do i have anyother way of converting this part 'utl_file.put_raw(dat,utl_i18n.string_to_raw(:1,''WE8ISO8859P15''));' in my dynamic sql.
In 11g, you could use CLOB as dynamic SQL statement, so it would probably execute successfully.
In 10g, only VARCHAR2s are supported in dynamic SQL statement, so the largest one may be 32767 characters long. The only option then is not to "increase columns to 400" or use shorter values in V_COL_VAL variable(s).
I suggest you to bind anyway. Not only that the SQL statement will be shorter and more transparent, you will not have to treat single quotes (') in passed string (V_COL_VAL - by the way, your dynamic SQL would fail if it contained single quote), and, if called frequently with different values, that SQL statement will not have to be hard parsed over and over again (so the performance will not suffer).
[Edit: Added the second suggestion]
[Updated on: Fri, 06 May 2011 09:51] Report message to a moderator |
|
|
|
Goto Forum:
Current Time: Mon Sep 01 11:49:55 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
