?
Solved

Oracle SP - Flat file creation fails when # records too high?????????

Posted on 2008-10-01
8
Medium Priority
?
936 Views
Last Modified: 2013-12-19
Hello Gurus!
I am executing an Oracle stored procedure [code snippet below] which has executed successfully for several months and then failed when running against the August data set.  Error message is below.  I have debugged the code.  When restricting the number of records returned [using rownum] the SP completes.  I incrementally increased the rownum value until the SP failed with 45.5K records.  There are 45,848 records in the return record set.  Is there some type of limitation that I am missing?  Your help is greatly appreciated.  This is Oracle 10G.

at: cannot stat -
usage: rm [-fiRr] file ...

BEGIN odsowner.fact_hfm_actuals_risk_sp@ods.world; END;

*
ERROR at line 1:
ORA-20013: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ODSOWNER.FACT_HFM_ACTUALS_RISK_SP", line 366
ORA-06512: at line 1


Usage: { EXIT | QUIT }         [ SUCCESS | FAILURE | WARNING | n |
       <variable> | :<bindvariable> ]  [ COMMIT | ROLLBACK ] Error return code is  1

CREATE OR REPLACE PROCEDURE FACT_HFM_ACTUALS_RISK_SP_CM
IS
tmpVar NUMBER;
/******************************************************************************
   NAME:       FACT_HFM_ACTUALS_RISK_SP
   PURPOSE:    
 
   REVISIONS:
   Ver        Date        Author           Description
   ---------  ----------  ---------------  ------------------------------------
   1.0        07/09/2008          1. Created this procedure.
 
   NOTES:
 
   Automatically available Auto Replace Keywords:
      Object Name:     FACT_HFM_ACTUALS_RISK_SP
      Sysdate:         07/09/2008
      Date and Time:   07/09/2008, 12:22:25 PM, and 07/09/2008 12:22:25 PM
      Username:         (set in TOAD Options, Procedure Editor)
      Table Name:       (set in the "New PL/SQL Object" dialog)
 
******************************************************************************/
PROGRAM_ID  VARCHAR2(30) := 'FACT_HFM_ACTUALS_RISK_SP';
f utl_file.file_type;
 
l_period			 	 varchar2(3);
l_period2			 	 varchar2(3);
l_year		       		 varchar2(4);
l_scenario				 varchar2(32);
l_account				 varchar2(80);
l_acct_desc				 varchar2(80);
l_entity				 varchar2(80);
l_ent_desc			     varchar2(80);
l_c2					 varchar2(80);
l_c2_desc				 varchar2(80);
l_product				 varchar2(80);
l_prod_desc				 varchar2(80);		
l_country_code			 varchar2(3);
l_country_desc			 varchar2(80);
l_mtd_data			     number(20,2);
l_ytd_data			     number(20,2);	
l_mtd_count				 number(20,2);
l_ytd_count				 number(20,2);  
l_seq_nbr				 number(20,2);
l_curr_date				 varchar2(24);
l_reporting_date		 varchar2(8);
l_record_count			 number(20,0);
l_timestamp				 date;
 
 
 
CURSOR c_risk_data IS
	SELECT year,
	       period,
		   period2,
		   scenario,
		   account,
		   acct_desc,
		   entity,
		   ent_desc,
		   c2,
		   c2_desc,
		   product,
		   prod_desc,
		   country_code,
		   country_desc,
		   CASE WHEN mtd_data < 0 THEN '-'
		   ELSE '0'
		   END || 
		   lpad((round(ABS(mtd_data),2)*100),16,0) as mtd_Data,
		   CASE WHEN ytd_data < 0 THEN '-'
		   ELSE '0'
		   END || 
		   lpad((round(ABS(ytd_data),2)*100),16,0) as ytd_Data,
		   LPAD(mtd_count,9,0) as mtd_count,
		   LPAD(ytd_count,9,0) as ytd_count, 
		   LPAD(rownum,9,0) as seq_nbr
	FROM risk_data_v;
	
	
 
v_row   number:= 0; -- for counter below....
 
 
BEGIN
   
   SP_ODS_PROCESS_LOG(PROGRAM_ID,0,NULL,'Load FACT_HFM_ACTUALS_RISK_SP - Program Started', SYSDATE, NULL);
 
 
 DELETE FROM RISK_YTD_DATA_TEMP;
 
 DELETE FROM RISK_YTD_COUNT_TEMP;
 
 COMMIT;
 
 
 
 INSERT INTO RISK_YTD_DATA_TEMP
 (Select * from RISK_YTD_DATA_V);
 
 INSERT INTO RISK_YTD_COUNT_TEMP
 (Select * from RISK_YTD_COUNT_V);
 
COMMIT;
  
   
   for rec in c_risk_data loop 
 
    l_period     		  := rec.period;
	l_period2     		  := rec.period2;
    l_year       		  := rec.year;
    l_scenario   		  := rec.scenario;
	l_account			  := rec.account;
	l_acct_desc			  := rec.acct_desc;
	l_entity			  := rec.entity;
	l_ent_desc			  := rec.ent_desc;
	l_c2				  := rec.c2;
	l_c2_desc			  := rec.c2_desc;
	l_product			  := rec.product;
	l_prod_desc			  := rec.prod_desc;
	l_country_code		  := rec.country_code;
	l_country_desc		  := rec.country_desc;
	l_mtd_data			  := rec.mtd_data;
	l_ytd_data			  := rec.ytd_data;
	l_mtd_count			  := rec.mtd_count;
	l_ytd_count			  := rec.ytd_count;
	l_seq_nbr			  := rec.seq_nbr;
	
	
 
    if c_risk_data%rowcount = 1 then
  
      f := utl_file.fopen( 'DIR_TEMP', l_year||l_period2||'01_RMI_HBIO_ACT.dat9', 'w');
      
    end if;
 
    v_row := v_row+1; -- increment row count
    utl_file.put_line(f, rec.year || ',' || rec.period||',HBIO,'||RPAD(rec.scenario,7)||','||RPAD(rec.account,15)||','||RPAD(nvl(rec.acct_desc,' '),30,' ')||','||'N/A ,N/A                           '||','||RPAD(rec.entity,10)||','||RPAD(nvl(rec.ent_desc,' '),30,' ')||','||RPAD(rec.c2,10)||','|| RPAD(nvl(rec.c2_desc,' '),30,' ')||','||RPAD(rec.product,10)||','||RPAD(nvl(rec.prod_desc,' '),30,' ')||','||RPAD(rec.country_code,3)||','||RPAD(rec.country_desc,20)||',' ||rec.mtd_data || ',' || rec.ytd_data|| ',' ||rec.mtd_count|| ',' ||rec.ytd_count ||','||rec.seq_nbr);
 
  end loop;
 
 
 
utl_file.fclose(f);
 
 
 
UPDATE FACT_HFM_ACTUALS_RISK
   SET CURRENT_PERIOD = 'N'
   WHERE CURRENT_PERIOD = 'Y';
   
   COMMIT;
   
   
   
   UPDATE FACT_HFM_ACTUALS_RISK
   SET ACTIVE_FLAG = 'N'
   WHERE FACT_HFM_ACTUALS_RISK.TIME_PERIOD = 
     (SELECT DISTINCT LABEL FROM RISK_PERIOD aa, RISK_FACT bb
      WHERE bb.PERIODID = aa.ID)
   AND FACT_HFM_ACTUALS_RISK.YEAR = 
       (SELECT DISTINCT LABEL FROM RISK_YEAR aa, RISK_FACT bb
      WHERE bb.YEARID = aa.ID);
 
           
   COMMIT;
   
      
 INSERT INTO FACT_HFM_ACTUALS_RISK 
   (current_period, 
   entity, 
   account, 
   custom1,
   custom2, 
   custom3, 
   custom4,
   scenario, 
   hfm_value,
   hfm_view, 
   ICP, 
   time_period, 
   year, 
   fy_period,
   account_type, 
   active_flag, 
   amount)
   (select distinct 'Y' AS CURRENT_PERIOD, 
   af.label AS Entity, 
   ah.label AS Account, 
   aj.label AS CUSTOM1,
   ak.label AS custom2, 
   al.label AS CUSTOM3, 
   am.label AS CUSTOM4, 
   ab.label AS Scenario,  
   ag.label AS Value,
   ae.label AS HFM_View, 
   ai.label AS ICP, 
   ad.label AS Period,  
   ac.label AS Year,    
   CAST('20' || SUBSTR(ac.label,3,2) || CASE WHEN ad.label = 'Jan' THEN '01'
                 WHEN ad.label = 'Feb' THEN '02'
                 WHEN ad.label = 'Mar' THEN '03'
           WHEN ad.label = 'Apr' THEN '04'
           WHEN ad.label = 'May' THEN '05'
           WHEN ad.label = 'Jun' THEN '06'
           WHEN ad.label = 'Jul' THEN '07'
           WHEN ad.label = 'Aug' THEN '08'
           WHEN ad.label = 'Sep' THEN '09'
           WHEN ad.label = 'Oct' THEN '10'
           WHEN ad.label = 'Nov' THEN '11'
           WHEN ad.label = 'Dec' THEN '12'
             ELSE NULL
           END AS NUMBER) as FY_YEAR,
   ao.accounttype AS Account_Type, 
   'Y' AS ACTIVE_FLAG, 
   aa.ddata AS AMOUNT
   FROM RISK_FACT aa, 
   RISK_scenario ab, 
   RISK_year ac, 
   RISK_period ad, 
   RISK_view ae, 
   RISK_entity af,
   RISK_value ag, 
   RISK_account ah, 
   RISK_icp ai, 
   RISK_custom1 aj, 
   RISK_custom2 ak, 
   RISK_custom3 al,
   RISK_custom4 am,  
   RISK_ACCOUNT ao
WHERE aa.scenarioid = ab.id
   AND aa.yearid = ac.id
   AND aa.periodid = ad.id
   AND aa.viewid = ae.id
   AND aa.entityid = af.id
   AND aa.valueid = ag.id
   AND aa.ACCOUNTID = ah.id
   AND aa.ICPID = ai.id
   AND aa.CUSTOM1ID = aj.id
   AND aa.CUSTOM2ID = ak.id
   AND aa.CUSTOM3ID = al.id
   AND aa.CUSTOM4ID = am.id
   AND aa.accountid = ao.id
   );
   
 
 
   COMMIT;
   
   
   
   DELETE FACT_ACTUALS_RISK
   WHERE FACT_ACTUALS_RISK.PERIOD2 = 
     (SELECT DISTINCT CASE WHEN aa.LABEL = 'Jan' THEN '01'
	   				 WHEN aa.LABEL = 'Feb' THEN '02'
					 WHEN aa.LABEL = 'Mar' THEN '03'
					 WHEN aa.LABEL = 'Apr' THEN '04'
					 WHEN aa.LABEL = 'May' THEN '05'
					 WHEN aa.LABEL = 'Jun' THEN '06'
					 WHEN aa.LABEL = 'Jul' THEN '07'
					 WHEN aa.LABEL = 'Aug' THEN '08'
					 WHEN aa.LABEL = 'Sep' THEN '09'
					 WHEN aa.LABEL = 'Oct' THEN '10'
					 WHEN aa.LABEL = 'Nov' THEN '11'
					 WHEN aa.LABEL = 'Dec' THEN '12'
					 ELSE '00'
					 END as PERIOD
   FROM RISK_PERIOD aa, RISK_FACT bb
      WHERE bb.PERIODID = aa.ID)
   AND FACT_ACTUALS_RISK.YEAR = 
       (SELECT DISTINCT LABEL FROM RISK_YEAR aa, RISK_FACT bb
      WHERE bb.YEARID = aa.ID);
	  
    COMMIT;  
	  
	DELETE FACT_ACTUALS_COUNT_RISK
    WHERE FACT_ACTUALS_COUNT_RISK.PERIOD2 = 
     (SELECT DISTINCT CASE WHEN aa.LABEL = 'Jan' THEN '01'
	   				 WHEN aa.LABEL = 'Feb' THEN '02'
					 WHEN aa.LABEL = 'Mar' THEN '03'
					 WHEN aa.LABEL = 'Apr' THEN '04'
					 WHEN aa.LABEL = 'May' THEN '05'
					 WHEN aa.LABEL = 'Jun' THEN '06'
					 WHEN aa.LABEL = 'Jul' THEN '07'
					 WHEN aa.LABEL = 'Aug' THEN '08'
					 WHEN aa.LABEL = 'Sep' THEN '09'
					 WHEN aa.LABEL = 'Oct' THEN '10'
					 WHEN aa.LABEL = 'Nov' THEN '11'
					 WHEN aa.LABEL = 'Dec' THEN '12'
					 ELSE '00'
					 END as PERIOD
FROM RISK_PERIOD aa, RISK_FACT bb
      WHERE bb.PERIODID = aa.ID)
   AND FACT_ACTUALS_COUNT_RISK.YEAR = 
       (SELECT DISTINCT LABEL FROM RISK_YEAR aa, RISK_FACT bb
      WHERE bb.YEARID = aa.ID);
 
   
   COMMIT;
	  
   
INSERT INTO FACT_ACTUALS_RISK
   (year,
   period, 
   period2, 
   scenario,
   account,
   acct_desc,
   entity,
   entity_desc,
   c2,
   c2_desc,
   product,
   prod_desc,
   country_code,
   country_desc,
   YTD_data)
   (select year,
   period,
   period2,
   scenario,
   account,
   acct_desc,
   entity,
   ent_desc,
   c2,
   c2_desc,
   product,
   prod_desc,
   country_code,
   country_desc,
   YTD_data
   from RISK_YTD_DATA_V);
   
   
   COMMIT;
   
   
   INSERT INTO FACT_ACTUALS_COUNT_RISK
   (year,
   period, 
   period2, 
   scenario,
   account,
   acct_desc,
   entity,
   entity_desc,
   c2,
   c2_desc,
   product,
   prod_desc,
   country_code,
   country_desc,
   YTD_data)
   (select year,
   period,
   period2,
   scenario,
   account,
   acct_desc,
   entity,
   ent_desc,
   c2,
   c2_desc,
   product,
   prod_desc,
   country_code,
   country_desc,
   YTD_count
   from RISK_YTD_COUNT_V);
   
   
 COMMIT;
 
 
SP_ODS_PROCESS_LOG(PROGRAM_ID,0,NULL,'FACT_HFM_ACTUALS_RISK_SP - Completed Successfully', NULL, SYSDATE); 
 
 EXCEPTION
    WHEN OTHERS THEN
       SP_ODS_PROCESS_LOG('FACT_HFM_ACTUALS_RISK_SP',SQLCODE,SQLERRM,'Raise Application Error - 20013');
        
  RAISE_APPLICATION_ERROR(-20013, SQLERRM);
 
END;
/

Open in new window

0
Comment
Question by:hypermac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 10

Expert Comment

by:dbmullen
ID: 22616070
describe these 2 objects
FACT_ACTUALS_COUNT_RISK
RISK_YTD_COUNT_V

check that RISK_YTD_COUNT_V.YTD_count and FACT_ACTUALS_COUNT_RISK.YTD_data as the same format..
0
 

Author Comment

by:hypermac
ID: 22616117
FACT_ACTUALS_COUNT_RISK is a basic FACT table.  RISK_YTD_COUNT_V is a view that aids in the collection of data.  This SP has worked successfully for periods Jan thru July.  Nothing has changed in the environment or data set with the execption of more data being returned.  I validated that the correct data set is being returned.
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22616235
so, you're saying the column types are the same for all of the columns in the 2 tables listed above..  my guess, RISK_YTD_COUNT_V is returning a "space" instead of a number for one of the columns.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hypermac
ID: 22616331
RISK_YTD_COUNT_V.YTD_COUNT = Number
FACT_ACTUALS_COUNT_RISK.YTD_DATA = Number (18,2)
0
 
LVL 10

Expert Comment

by:dbmullen
ID: 22616434
it has to be one of the columns that aren't the same datatype
or, the "data" under the view has a "blank space" in a number field.
0
 

Author Comment

by:hypermac
ID: 22616498
I searched the result set from the View and found either values or '0'.  I've compared the data types across all FACT type tables and the Views and, with the exception of the Views not having the precision and scale defined, they are all number data types.  Since this SP has worked for months, I would take the leap to think it has something to do with the August data set or the size of the resulting flat file.  I am currently working on my laptop which 'should' have the same tables as in my DEV environment [it was built using the DEV scripts].  I won't be able to verfiy the DEV environment until this evening.  I'll keep you posted.
0
 
LVL 10

Accepted Solution

by:
dbmullen earned 1500 total points
ID: 22616788
well..  to answer your question.
there is not "file-size" limit that I'm aware of..  besides OS filesize limits.  which are normally bigger than you could ever use.

the error you posted above tells me there is some "data" that was BLANK when a number is required.  line 366 has a insert/select statement..  thus, I would start there..  

the only thing that has changed is the DATA..  that tells me it's something in the data

ERROR at line 1:
ORA-20013: ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "ODSOWNER.FACT_HFM_ACTUALS_RISK_SP", line 366
ORA-06512: at line 1
0
 
LVL 4

Expert Comment

by:KICUSek
ID: 22623261
Add exception handling and log values used in statement for investigation.
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question