Solved

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

Posted on 2008-10-01
8
872 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
  • 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
 

Author Comment

by:hypermac
ID: 22616331
RISK_YTD_COUNT_V.YTD_COUNT = Number
FACT_ACTUALS_COUNT_RISK.YTD_DATA = Number (18,2)
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 500 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now